RANDARRAY Function

Returns a grid of random numbers, decimals or whole numbers.

Category

Math & Trig

Available in

Microsoft 365, Excel 2021+

Returns

An array that spills

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Dynamic Arrays module

Overview

RANDARRAY fills as many rows and columns as you ask with random numbers between a minimum and maximum.

Set the last argument to TRUE for whole numbers — a whole block of test data in one formula.

Syntax

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

Arguments

ArgumentRequired?What it does
rowsOptionalHow many rows. Default 1.
columnsOptionalHow many columns. Default 1.
minOptionalSmallest value. Default 0.
maxOptionalLargest value. Default 1.
whole_numberOptionalTRUE for integers, FALSE (default) for decimals.

Examples

Example 1 — A block of test sales

10 rows × 4 quarters of whole-number sales between 100 and 900.

=RANDARRAY(10, 4, 100, 900, TRUE) Explain

Example 2 — Random order for a list

Shuffle 20 names.

=SORTBY(A2:A21, RANDARRAY(20)) Explain

Common mistakes

  • #SPILL! Clear space for the whole grid.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.