Math & Trig

RANDARRAY Function in Excel

Returns an array of random numbers.

Syntax

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

Arguments

  • rows (optional): Number of rows (default 1)
  • columns (optional): Number of columns (default 1)
  • min (optional): Minimum value (default 0)
  • max (optional): Maximum value (default 1)
  • whole_number (optional): TRUE for integers, FALSE for decimals

Examples

  • =RANDARRAY(5, 1, 1, 100, TRUE) - 5 random integers 1-100 - Result: Random array

Excel RANDARRAY function documentation

  • RANDARRAY returns an array of random numbers — spill in Microsoft 365.
  • Syntax: =RANDARRAY([rows], [columns], [min], [max], [integer]).
  • Replaces filling many RAND() cells — one formula for Monte Carlo grids.
  • Related: [RAND](/functions/rand/), [RANDBETWEEN](/functions/randbetween/), [SEQUENCE](/functions/sequence/).

RANDARRAY syntax

  • rows, columns default 1.
  • min max default 0 and 1 for decimal; set integer TRUE for whole numbers.
  • Volatile — recalculates on workbook calc.
  • Clear spill area to avoid #SPILL!.

Step-by-step: 5x10 simulation grid

  • Step 1 — =RANDARRAY(5, 10) spills 5 rows 10 cols of 0-1.
  • Step 2 — Scale: =RANDARRAY(5,10,100,200) uniform 100-200.
  • Step 3 — Integer dice grid: =RANDARRAY(100,1,1,6,TRUE).
  • Step 4 — Paste values snapshot before presentation.
  • Step 5 — Monte Carlo — combine with NORM.INV for normal draws advanced.

RANDARRAY vs RAND vs Data Table

  • RANDARRAY — block spill random.
  • RAND — single cell.
  • What-If Data Table — sensitivity not random generation.
  • Dynamic array hub: [FILTER spill fix](/problems/filter-spill-error/).

Worked examples to copy

  • 10 random decimals: =RANDARRAY(10,1).
  • Lottery ints: =RANDARRAY(6,1,1,49,TRUE).
  • Matrix: =RANDARRAY(3,3,0,1).
  • Shuffle helper col: =RANDARRAY(ROWS(A:A),1).

People also ask

  • RANDARRAY Excel 2019? — No; 365.
  • RANDARRAY seed? — No built-in seed; VBA for reproducible.
  • RANDARRAY slow? — Large grids volatile — paste values when done.
  • RANDARRAY duplicates? — Possible; not unique without check.

Common errors

  • Recalculates on every change

Use cases

  • Random sampling
  • Test data
  • Simulations

Frequently asked questions

  • How do I generate random test data? Use =RANDARRAY(100, 5, 1, 1000, TRUE) for 100 rows of 5 random integers. Combine with other functions: =RANDARRAY(10, 1, DATE(2024,1,1), DATE(2024,12,31), TRUE) for random dates in 2024.
  • How do I randomly select N items from a list without repeats? Use =SORTBY(list, RANDARRAY(ROWS(list))) to shuffle, then =TAKE(shuffled, N) to get top N. Or combine: =TAKE(SORTBY(A1:A100, RANDARRAY(100)), 10) randomly selects 10 unique items.
  • How do I freeze random values so they don't change? RANDARRAY recalculates on every change. To freeze: select the cells, Copy (Ctrl+C), then Paste Special > Values (Ctrl+Shift+V). This replaces formulas with static random numbers.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use RANDARRAY

  • Random sampling — common Math & Trig scenario for RANDARRAY.
  • Test data — common Math & Trig scenario for RANDARRAY.
  • Simulations — common Math & Trig scenario for RANDARRAY.

RANDARRAY in the Math & Trig category

  • Browse all Math & Trig functions at /categories/math-trig/ for related formulas.
  • RANDARRAY syntax: =RANDARRAY([rows], [columns], [min], [max], [whole_number])
  • rows (optional): Number of rows (default 1)
  • columns (optional): Number of columns (default 1)
  • min (optional): Minimum value (default 0)
  • max (optional): Maximum value (default 1)
  • Confirm RANDARRAY arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm RANDARRAY arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • RAND (/functions/rand/): Returns a random decimal number between 0 and 1.
  • RANDBETWEEN (/functions/randbetween/): Returns a random integer between two specified numbers.
  • SEQUENCE (/functions/sequence/): Generates a list of sequential numbers in an array.

Errors to watch for

  • Recalculates on every change — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter RANDARRAY on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

RANDARRAY worked examples to copy

  • =RANDARRAY(5, 1, 1, 100, TRUE) — 5 random integers 1-100. Expected result: Random array.

RANDARRAY reference summary for crawlers and offline review

  • RANDARRAY belongs to the Math & Trig category in Excel. Returns an array of random numbers.
  • Full syntax: =RANDARRAY([rows], [columns], [min], [max], [whole_number]). Open /functions/randarray/ for parameters, FAQs, and related pages.
  • Common mistakes: Recalculates on every change
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.