Excel RANDARRAY — One Formula for a Whole Grid of Random Numbers

TL;DR — RANDARRAY([rows], [cols], [min], [max], [whole_number]) is Excel 365's do-everything random generator: one formula spills a whole block of random numbers instead of you dragging RAND down a thousand rows. All five arguments are optional. Bare =RANDARRAY() behaves like RAND(). The switch that decides decimals vs integers is the last argument, whole_number, and it defaults to FALSE — so you get decimals like 3.71 unless you explicitly pass TRUE. It's still volatile (re-rolls on every edit) and it spills, so a blocked range gives #SPILL!.

=RANDARRAY()                    ' -> 0.6218...   one decimal in [0,1), like RAND()
=RANDARRAY(5)                   ' -> spills 5 random decimals down a column
=RANDARRAY(5, 3)                ' -> spills a 5×3 grid of decimals
=RANDARRAY(5, 3, 1, 100, TRUE)  ' -> a 5×3 grid of random integers 1–100
=SORTBY(A2:A20, RANDARRAY(ROWS(A2:A20)))   ' -> shuffle a list in one formula

If you already know RAND and RANDBETWEEN, RANDARRAY is the natural next step: it does what both of them do, for a whole region at once, and it resizes itself. The catches are the argument order, one sneaky default, and the fact that — being a dynamic array — it can collide with whatever sits below it.

What you'll learn

The mental model: RANDARRAY is RAND + RANDBETWEEN, spilled
Why the whole_number flag defaults to FALSE — the #1 "why do I get decimals?"
The full argument order, and how min/max behave for decimals vs integers
Why it's still volatile, and when it throws #SPILL!
The killer combo: SORTBY(list, RANDARRAY(...)) to shuffle
When RANDARRAY isn't available (and what to use instead)

The mental model: RAND and RANDBETWEEN, spilled

RAND gives one decimal. RANDBETWEEN gives one integer. To fill a table with either, the old way was to write the formula once and drag it across hundreds of cells. RANDARRAY collapses all of that into a single formula that spills — you tell it the shape (rows, columns) and the flavor (range, integer or not), and it paints the whole block at once.

That's the entire idea: one function that is both of the classic dice and the fill-down, rolled together. Read =RANDARRAY(5, 3, 1, 100, TRUE) as "give me a 5-by-3 grid of RANDBETWEEN(1,100)," and =RANDARRAY(5, 3) as "give me a 5-by-3 grid of RAND()." Once you see it as "spilled RAND / spilled RANDBETWEEN," the arguments stop feeling arbitrary.

The arguments — and the FALSE default that bites everyone

All five arguments are optional, but their order matters and one default surprises almost everyone:

=RANDARRAY([rows], [cols], [min], [max], [whole_number])

rows, cols — the shape of the spilled block (default 1 × 1).
min, max — the range (default 0 to 1). Both ends are inclusive when you're generating integers.
whole_number — TRUE for integers, FALSE for decimals. Default: FALSE.

That last default is the number-one RANDARRAY question: "I asked for numbers between 1 and 100 and got 47.83 — why?" Because you stopped at max and never passed whole_number:

=RANDARRAY(5, 1, 1, 100)         ' -> decimals like 47.83  (whole_number omitted = FALSE)
=RANDARRAY(5, 1, 1, 100, TRUE)   ' -> integers like 47      (whole_number = TRUE)

Rule: if you want integers, you must end the formula with TRUE. Supplying min and max is not enough — it happily returns decimals inside that range. This is the opposite of RANDBETWEEN, which is integer-only, so people migrating from RANDBETWEEN get caught every time. (One more integer detail worth a glance: with whole_number TRUE, min and max must themselves be whole numbers, or Excel returns #VALUE! — and if min is greater than max, #VALUE! again.)

It's still volatile, and it still spills

RANDARRAY inherits both behaviors that define dynamic-array randomness:

Volatile — like RAND and RANDBETWEEN, it re-rolls on every recalculation: any edit, F9, sort, or file-open. If you need to keep a result, freeze it the same way: select the spill, Copy, Paste Special → Values. (Freeze the whole spilled range in one go, not just the top-left cell.)
Spilling — it writes into the cells below and to the right of where you typed it. If anything is already sitting in that block, you get a #SPILL! error instead of numbers. Clear the obstruction and the array appears.

=RANDARRAY(1000, 1, 1, 100, TRUE)   ' fills 1000 cells from ONE formula...
                                     ' ...but re-rolls all 1000 on every recalc

That volatility is worth respecting at scale: a thousand-cell RANDARRAY recomputes every one of its cells on each keystroke elsewhere in the book. For a live what-if model that's fine; for a dataset you've finalized, freeze it so the recalcs stop.

The killer combo: shuffle a list with SORTBY + RANDARRAY

The single most useful thing RANDARRAY does isn't generating numbers you keep — it's generating numbers you sort by and then throw away. Pair it with SORTBY and you shuffle a list in one formula, no helper column:

=SORTBY(A2:A20, RANDARRAY(ROWS(A2:A20)))       ' -> the list in random order
=TAKE(SORTBY(A2:A20, RANDARRAY(ROWS(A2:A20))), 5)  ' -> a random sample of 5, no repeats

RANDARRAY(ROWS(...)) makes one random key per row; SORTBY orders the rows by those keys; the randomness is gone the instant it's done its job. Because each row gets a distinct key and is simply reordered, nothing repeats — which is exactly the without-replacement behavior RANDBETWEEN can't give you. This pattern is the backbone of random selection and sampling, and RANDARRAY is what makes it a one-liner on 365.

The judgment call

On Excel 365, RANDARRAY makes "drag RAND down 1,000 rows" obsolete — one formula, no dragging, and it resizes itself when your data grows. Reach for it whenever you need a block of randomness or a random sort key. The two things to keep in your head are the whole_number = FALSE default (end with TRUE when you want integers) and volatility (freeze to values the moment a result is final). The one time not to use it is compatibility: RANDARRAY is a dynamic-array function, so it exists only in Excel 365 and Excel 2021 — if your workbook has to run in Excel 2019 or earlier, fall back to RAND and RANDBETWEEN filled down the old way.

How ExcelMaster helps

The FALSE-default and spill mechanics are easy to forget, so let a formula assistant carry them. Ask ExcelMaster for "a 10×5 grid of random integers from 1 to 100" and it writes =RANDARRAY(10, 5, 1, 100, TRUE) — with the TRUE you'd have dropped — and points the spill at an empty range so you never see #SPILL!. Ask it to "shuffle this list" or "pull a random sample of 25 rows" and it builds the SORTBY(..., RANDARRAY(...)) combo, then offers to freeze the output to values so your sample stops moving. And if it detects your file targets an older Excel, it quietly falls back to a RAND-based approach that will actually run there.

Frequently asked questions

What does the RANDARRAY function do in Excel?

=RANDARRAY([rows], [cols], [min], [max], [whole_number]) returns a spilled array of random numbers. With no arguments it returns a single decimal in [0, 1) like RAND(); with rows and cols it fills a whole block; with min, max, and whole_number set to TRUE it fills that block with random integers in your range. It's Excel 365's all-in-one random generator.

Why is RANDARRAY giving me decimals instead of whole numbers?

Because the whole_number argument defaults to FALSE. Passing min and max only sets the range — it does not force integers. To get whole numbers you must add TRUE as the fifth argument: =RANDARRAY(5, 1, 1, 100, TRUE). Without it, RANDARRAY returns decimals inside [min, max].

Is RANDARRAY volatile? How do I stop it recalculating?

Yes — like RAND and RANDBETWEEN it re-rolls on every recalculation. To keep a result, select the entire spilled range, Copy, then Paste Special → Values. That converts the whole array to static numbers that no longer change.

Why does RANDARRAY show a #SPILL! error?

RANDARRAY is a dynamic array that spills into the cells below and right of the formula. If any of those cells already contain data, Excel can't spill and shows #SPILL!. Clear or move whatever is blocking the spill range and the array appears.

How do I shuffle a list or take a random sample with RANDARRAY?

Sort by a random key: =SORTBY(range, RANDARRAY(ROWS(range))) returns the list in random order, and =TAKE(SORTBY(range, RANDARRAY(ROWS(range))), n) takes a random sample of n with no repeats. See How to Randomly Select, Sample & Shuffle in Excel.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-11.