The Excel RANDARRAY function returns a spilled array of random numbers with the rows, columns, range, and whole-vs-decimal behavior you choose — a single formula that fills a whole block of cells.
Syntax
| Argument | Description | |
|---|---|---|
rows | Optional | Number of rows to return. Defaults to 1. |
columns | Optional | Number of columns to return. Defaults to 1. |
min | Optional | The smallest value to return. Defaults to 0. |
max | Optional | The largest value to return. Defaults to 1. |
whole_number | Optional | TRUE returns whole numbers; FALSE (default) returns decimals. |
How to use it
RANDARRAY is the Excel 365 dynamic-array version of RAND. A single formula spills a block of random numbers across as many rows and columns as you ask for.
Add min, max, and whole_number to control the values. Setting the fifth argument to TRUE returns integers within your range:
Like RAND, RANDARRAY is volatile — the whole spill range refreshes on every recalculation. It needs empty cells below and to the right to spill into, otherwise it returns a spill error.
Tip: Pair RANDARRAY with SORTBY to shuffle a list, or with SEQUENCE to build randomized sample tables in one formula.
Try it: interactive demo
Pick a RANDARRAY example to see the formula and its result.
Practice workbook
Frequently asked questions
What does RANDARRAY return by default?
=RANDARRAY() returns a single random decimal between 0 and 1, just like RAND. Add rows and columns to spill a larger block.How do I get whole numbers from RANDARRAY?
whole_number, to TRUE and supply a min and max. =RANDARRAY(3,1,1,6,TRUE) returns a 3-cell column of whole numbers from 1 to 6.Why do I get a #SPILL! error?
Is RANDARRAY available in older Excel?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class