Financial analysts, software testers, educators, and data scientists frequently need to populate spreadsheets with mock datasets, simulate probability distributions, create randomized testing matrices, or conduct Monte Carlo forecasting. In traditional spreadsheet workflows, generating random values required writing single RAND() or RANDBETWEEN() formulas and manually dragging them across hundreds of rows and columns, creating bulky worksheets that slow calculation engines.
Google Sheets provides the native dynamic array function RANDARRAY. With a single compact formula, RANDARRAY generates an entire two-dimensional grid of random numbers, automatically spilling the calculated array across the specified dimensions.
Understanding the RANDARRAY Syntax
The RANDARRAY function accepts two optional dimension arguments:
=RANDARRAY([rows], [columns])- rows: (Optional) The number of rows to populate in the spilled array. Defaults to
1. - columns: (Optional) The number of columns to populate in the spilled array. Defaults to
1.
When evaluated without additional arithmetic, RANDARRAY returns floating-point decimal numbers greater than or equal to 0 and strictly less than 1 (identical to RAND()).
Generating a Basic 2D Grid of Random Decimals
To create a matrix of random decimal values spanning 10 rows and 5 columns, enter this formula into cell A1:
=RANDARRAY(10, 5)Google Sheets instantly populates cells A1:E10 with random decimal values. If any existing text or formula obstructs the target expansion cells, Sheets returns a #REF! error indicating that the array could not spill.
Generating Random Integers within a Defined Range
While RANDARRAY produces decimals by default, you can generate whole numbers (integers) within custom lower and upper boundaries by combining it with INT or ROUND:
=ARRAYFORMULA(INT(RANDARRAY(20, 1) * (MAX - MIN + 1)) + MIN)For example, to generate 20 random test scores ranging between 50 and 100:
=ARRAYFORMULA(INT(RANDARRAY(20, 1) * 51) + 50)This formula generates a vertical vector of 20 random integer scores instantly.
Generating Random Dates Between Two Calendar Limits
Because Google Sheets stores dates internally as serial integers (where day 1 is December 30, 1899), you can generate a list of random dates within a date interval by converting calendar dates to serial numbers:
=ARRAYFORMULA(INT(RANDARRAY(15, 1) * (DATE(2026, 12, 31) - DATE(2026, 1, 1) + 1)) + DATE(2026, 1, 1))Format the output cells as Format > Number > Date to view properly formatted calendar dates distributed across the year 2026.
Creating Randomised Non-Repeating Shuffles
You can combine RANDARRAY with SORT to randomly shuffle an existing list of employees, competitors, or lottery tickets without altering the source range:
=SORT(A2:A100, RANDARRAY(ROWS(A2:A100), 1), TRUE)In this construction, RANDARRAY generates a column of random decimals matching the exact row count of the source list, and SORT rearranges the original entries based on those randomized values.
Freezing Volatile Values into Static Data
Like RAND(), RANDARRAY is a volatile formula that recalculates whenever any cell on the worksheet is edited or refreshed. To prevent random values from changing during subsequent reporting:
- Highlight the spilled array range.
- Press Ctrl + C (or Cmd + C on Mac) to copy.
- Right-click the top-left cell and select Paste special > Values only (or press Ctrl + Shift + V).
Converting the spilled formula to static numbers preserves your generated sample dataset permanently for analysis and presentation.