Quick Navigation

RANDARRAY Function

Excel 365, Excel 2019+

Summary

The RANDARRAY function generates an array of random numbers dynamically, perfect for simulations, testing, and data analysis. It creates arrays of specified dimensions filled with random values between defined limits.

Syntax

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

Parameters

Parameter	Type	Required	Description
rows	`Number`	No	Number of rows (1-1000, defaults to 1)
columns	`Number`	No	Number of columns (1-1000, defaults to 1)
min	`Number`	No	Minimum value (defaults to 0)
max	`Number`	No	Maximum value (defaults to 1)
whole_number	`Boolean`	No	TRUE=integers, FALSE=decimals (defaults to FALSE)

Using the RANDARRAY Function

RANDARRAY revolutionizes random data generation in Excel by creating dynamic arrays that automatically spill into adjacent cells. Use it for Monte Carlo simulations, random sampling, test data creation, and statistical analysis without needing to copy formulas across ranges.

Common RANDARRAY Examples

Basic 5x5 Random Array

=RANDARRAY(5,5)

Creates 5 rows × 5 columns of random decimals between 0 and 1.

10 Random Integers 1-100

=RANDARRAY(10,1,1,100,TRUE)

Generates 10 random whole numbers between 1 and 100.

Random Test Scores

=RANDARRAY(20,1,50,100,TRUE)

Creates 20 random integer test scores from 50 to 100.

Frequently Asked Questions

Yes, like all RAND functions, it recalculates on every worksheet recalculation (F9).

Up to 1,048,576 rows × 16,384 columns, limited by available memory.

Copy (Ctrl+C) → Paste Special → Values to convert to static numbers.

Common Errors and Solutions

#VALUE! error

Cause: Non-numeric arguments for rows, columns, min, or max

Solution: Use valid numbers for all numeric parameters

#NUM! error

Cause: Rows or columns exceed Excel limits or min > max

Solution: Ensure rows ≤ 1,048,576, columns ≤ 16,384, and min ≤ max

Notes

All parameters are optional
Arrays automatically spill into adjacent cells
Press F9 to regenerate new random values
Use Ctrl+Shift+Enter not required (dynamic arrays)
Perfect companion to SORT, FILTER, UNIQUE functions

Compatibility

Available in: Excel 365, Excel 2021, Excel 2019 (with dynamic array update)

Not available in: Excel 2016 and earlier, Excel 2013, Excel 2010, Excel 2007, Excel 2003

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2019+