MO-211 Expert Prep
Module 2 · Lesson 2.3

Generate Numeric Data Using RANDARRAY()

Edit on GitHub

NumPy has np.random.randint(). Python has random.sample(). Excel has RANDARRAY() — a dynamic array function that generates an entire grid of random numbers in a single formula. It is the modern replacement for the older RAND() and RANDBETWEEN() functions when you need structured, multi-cell random output for mock datasets, simulations, or randomized sampling.

Part 1: Syntax

=RANDARRAY([rows], [columns], [min], [max], [integer])
Parameter Description Default
rows Number of rows to fill 1
columns Number of columns to fill 1
min Minimum value in the output 0
max Maximum value in the output 1
integer TRUE = whole numbers only; FALSE = decimals FALSE

All five parameters are optional. With no arguments, =RANDARRAY() returns a single random decimal between 0 and 1 — identical to =RAND().

Part 2: Examples

Example 1 — 5 rows of random decimals (0 to 1)

=RANDARRAY(5)

Returns a single column, 5 rows, decimals between 0 and 1.

Example 2 — 4×3 grid of random integers (1 to 100)

=RANDARRAY(4, 3, 1, 100, TRUE)

Returns a 4-row by 3-column grid of whole numbers between 1 and 100.

Example 3 — 10 random scores (50 to 100, integers)

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

Useful for generating mock exam scores, sales figures, or ratings.

Example 4 — Random decimal prices (9.99 to 99.99)

=RANDARRAY(20, 1, 9.99, 99.99, FALSE)

Part 3: Volatile Behavior — The Critical Caveat

IMPORTANT
`RANDARRAY()` is a **volatile function**. It **recalculates every time any change is made to the workbook** — including typing in an unrelated cell, pressing F9, or saving. This means your "random" data is never stable as long as the formula exists.

To freeze the output permanently: 1. Select the spill range produced by RANDARRAY(). 2. Press Ctrl+C to copy. 3. Right-click the same cell > Paste Special > Values (Ctrl+Alt+V, then V). 4. The formula is replaced with static numbers that no longer change.

Part 4: Comparison with Older Random Functions

Function Output Shape Range Control Integer Mode Volatile
RAND() Single cell 0 to 1 only No Yes
RANDBETWEEN(min, max) Single cell Yes Yes (always) Yes
RANDARRAY([r],[c],[min],[max],[int]) Multi-cell array Yes Optional Yes
TIP
To generate a **random sort order** for a list, use `=RANDARRAY(COUNTA(A:A))` alongside `SORTBY()`: `=SORTBY(A2:A20, RANDARRAY(19))`. This produces a randomly shuffled version of your list — useful for randomizing surveys or quiz questions.

Part 5: Use Cases

  • Mock datasets — generate a full table of test data without manual entry
  • Random sampling — pair with SORTBY() or INDEX() to pick random rows
  • Monte Carlo simulations — feed into financial or probability models
  • Randomized seating / assignment — shuffle lists fairly

Official Resources