Creating Random Data With RANDARRAY

Need a block of random numbers for testing, sampling, or a quick simulation? The RANDARRAY function generates an entire array of random values from one formula.

Older spreadsheets had RAND (one random decimal) and RANDBETWEEN (one random integer). RANDARRAY rolls both ideas into a single spill function that fills as many cells as you ask for.

RANDARRAY accepts up to five arguments, all optional:

• rows how many rows to fill (default 1)
• columns how many columns (default 1)
• min the smallest value (default 0)
• max the largest value (default 1)
• whole_number TRUE for integers, FALSE for decimals (default FALSE)

With no arguments at all, it returns a single random decimal between 0 and 1.

The simplest useful call asks for several rows of decimals between 0 and 1. Each value is a fresh random number with many decimal places.

This formula spills 10 random decimals down a column, handy for weighting, jitter, or probability tests.

The third and fourth arguments set the range of values. To get decimals between 50 and 100, supply those as min and max.

Remember to include rows and columns first. This gives 8 random decimal values, each somewhere between 50 and 100.

Set the fifth argument whole_number to TRUE to get integers instead of decimals. This is the modern replacement for RANDBETWEEN.

This formula produces 6 random whole numbers from 1 to 6, like rolling a handful of dice.

Provide both rows and columns to fill a rectangular block. Combine that with min, max, and TRUE for a grid of integers.

Here we make a 4-row, 3-column grid of whole numbers from 1 to 100, perfect for sample datasets or test fixtures.

RANDARRAY is volatile: its values change every time the sheet recalculates, such as when you edit any cell or press F9. That is great for live what-if testing but bad if you need stable numbers.

If you want to freeze a set of random values, copy the spilled range and paste it back as values only.

A neat trick: shuffle a list into random order by sorting it with RANDARRAY as the key. Pair it with SORTBY.

For names in A2:A20, this orders them by a fresh random number, effectively shuffling the list. Take the top few rows for a random sample.

Google Sheets supports RANDARRAY too, though its argument set is simpler: just rows and columns, always returning decimals between 0 and 1.

To get a custom range or integers in Sheets, wrap the result, for example multiply and add, then use INT. Excel's min, max, and whole_number arguments are not available there.

Like every dynamic array, RANDARRAY needs empty cells to expand into. A large request such as RANDARRAY(1000) will throw #SPILL! if anything blocks the range.

Place it in open space and clear obstructions before generating big blocks of random data.

Combine ideas to mock up data fast. Generate a column of random sales figures to test a dashboard before the real numbers arrive.

This spills 50 random whole-dollar amounts between 100 and 5000. Paste as values to lock them, then build your formulas on top.

You want a single column of 6 random whole numbers between 1 and 6, like dice rolls. Which formula is correct?

RANDARRAY fills a block with random values from one formula:

• =RANDARRAY(rows, columns, min, max, whole_number)
• All arguments are optional; bare RANDARRAY() gives one decimal 0 to 1
• Set whole_number to TRUE for integers (replaces RANDBETWEEN)
• It is volatile, so paste as values to freeze the numbers

Pair it with SORTBY to shuffle and sample, and leave room for the spill.