Excel 2021 or laterExamples need Excel 2024 or laterDynamic arrays

RANDARRAY

ASELECT.MATRIX in Dutch Excel

Fills a block of any size with random numbers, decimals or whole numbers between bounds you choose, and draws them again on every recalculation.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1RelationCustomerBalance 31 Dec 2025Draw
2D1001Brinkman Installatie BV€12,450.00€ 12.450,000.73160,7316=RANDARRAY(5)=RANDARRAY(5)
3D1002Bakkerij De Gouden Korst BV€3,820.50€ 3.820,500.20890,2089
4D1003Hoekstra Transport BV€27,915.75€ 27.915,750.55470,5547
5D1004Van der Meer Groenteteelt€1,640.00€ 1.640,000.09120,0912
6D1005Kuiper Metaalbewerking BV€8,095.20€ 8.095,200.88030,8803
=RANDARRAY(5)
=RANDARRAY(5)

What RANDARRAY does and when to use it

What it does: it fills a whole block of random numbers from one cell, with the rows, columns, minimum and maximum you choose, as decimals or whole numbers, for example a random key per invoice.

When to use it: to fill a test sheet, shuffle a list with SORTBY, draw a sample or simulate many outcomes at once. For one random whole number RANDBETWEEN is shorter and works in every Excel version.

Three main gains:

  • One formula, whole block: the result spills over the rows and columns you set, with no formula to copy down.
  • Range built in: min and max replace the scaling sum you need with RAND.
  • Whole numbers on request: set whole_number to TRUE instead of rounding the result.

How Excel evaluates it

Excel works as follows:

  • it reads the five arguments, filling in 1, 1, 0, 1 and FALSE for any you leave out, and checks that min is not above max
  • it draws every cell of the rows by columns block independently: a decimal between min and max, or with whole_number TRUE a whole number from min to max with both ends possible
  • it spills the block from the formula cell

Watch out: Every recalculation, from any edit in the workbook, opening the file or pressing F9, draws a completely new block, and there is no seed to get the same numbers back. Freeze a draw that matters with Copy and Paste Values.

Mental model

A tray of dice, one per cell, thrown together every time anyone touches the workbook; each die lands on its own, so two can show the same face.

Terms used on this page: workbook, spill, argument, range, cell

Syntax

RANDARRAY([rows], [columns], [min], [max], [whole_number])
RANDARRAY([rows]; [columns]; [min]; [max]; [whole_number])
ArgumentTypeMeaningDefault
rowsOptionalHow many rows of random numbers; default 1.1
columnsOptionalHow many columns of random numbers; default 1.1
minOptionalThe lowest value allowed; default 0.0
maxOptionalThe highest value allowed; default 1.1
whole_numberOptionalTRUE for whole numbers; FALSE or omitted for decimals.FALSE

Return value:A spilled block of rows by columns random numbers

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 16 KB)

Beginner

Draw a follow-up order for debtor confirmations

Sanne de Vries is auditing Veldhuis Installatie BV and has sent five debtor confirmations as of 31 December 2025. She wants a visible, unbiased way to decide which customer she chases first for a reply.

Each customer gets a random number; the lowest number is followed up first. She draws once, then freezes the result for the file.

ABCDE
1RelationCustomerBalance 31 Dec 2025Draw
2D1001Brinkman Installatie BV€12,450.00€ 12.450,000.73160,7316=RANDARRAY(5)=RANDARRAY(5)
3D1002Bakkerij De Gouden Korst BV€3,820.50€ 3.820,500.20890,2089
4D1003Hoekstra Transport BV€27,915.75€ 27.915,750.55470,5547
5D1004Van der Meer Groenteteelt€1,640.00€ 1.640,000.09120,0912
6D1005Kuiper Metaalbewerking BV€8,095.20€ 8.095,200.88030,8803
7
Formula cell (D2)InputSpilled result

Formula in D2

=RANDARRAY(5)
=RANDARRAY(5)

Result: 0.7316 / 0.2089 / 0.5547 / 0.0912 / 0.88030,7316 / 0,2089 / 0,5547 / 0,0912 / 0,8803Random: your result will differ

Step by step
  1. Excel reads rows as 5. The other four arguments are left out, so columns is 1, min is 0, max is 1 and whole_number is FALSE.
  2. It draws 5 independent decimals between 0 and 1 and spills them from D2 to D6. Say it draws 0.7316, 0.2089, 0.5547, 0.0912 and 0.8803 (shown to 4 decimals); your numbers will be different, and that is expected.
  3. The lowest number is 0.0912 in D5, next to D1004 (Van der Meer Groenteteelt, balance 1,640.001.640,00), so that customer is chased first. Then D1002 (0.2089), D1003 (0.5547), D1001 (0.7316) and D1005 (0.8803).
  4. Type a note anywhere in the sheet and press Enter: all five numbers change, because every edit recalculates RANDARRAY. Once the order is final, copy D2:D6 and paste it as values onto itself, so the order in the audit file can be traced.

Watch out: If a sixth customer is added in row 7, RANDARRAY(5) still draws only five numbers and the new relation gets none. Use RANDARRAY(ROWS(A2:A7)) so the count follows the list.

More on this example: why it works, 1 variation

Why it works

One formula gives every customer its own independent number, and with decimals between 0 and 1 a tie is so unlikely that the order is clear. The draw is unbiased because no number depends on another, and freezing it turns a live draw into evidence in the file.

Variations

=RAND()
=RAND()

One random decimal in one cell, the older way: copy it down five rows to get the same effect without spilling.

Intermediate

A grid of whole test amounts for a round-amount test

Before the real journal lines of Veldhuis Installatie BV arrive, Sanne de Vries is building her journal entry testing sheet and needs test data: four entries with three lines each, whole amounts from 100 to 5,0005.000.

Both bounds must be possible, so the formulas that flag exact round amounts such as 100 and 5,0005.000 get tested too, and the amounts must be whole euros, as in the real export.

ABCDE
1Entry no.Line 1Line 2Line 3
225-041212501250=RANDARRAY(4, 3, 100, 5000, TRUE)=RANDARRAY(4; 3; 100; 5000; TRUE)47304730310310
325-04135000500028752875100100
425-04147807803420342034203420
525-04154105410550005000260260
6
Formula cell (B2)InputSpilled result

Formula in B2

=RANDARRAY(4, 3, 100, 5000, TRUE)
=RANDARRAY(4; 3; 100; 5000; TRUE)

Result: 1250 | 4730 | 310 / 5000 | 2875 | 100 / 780 | 3420 | 3420 / 4105 | 5000 | 2601250 | 4730 | 310 / 5000 | 2875 | 100 / 780 | 3420 | 3420 / 4105 | 5000 | 260Random: your result will differ

Step by step
  1. Excel reads rows 4 and columns 3, so the block is 4 by 3: one row per entry, one column per line, B2 to D5.
  2. min is 100, max is 5000, and whole_number is TRUE, so each of the 12 cells gets a whole number from 100 to 5000, both included: 5000 - 100 + 1 = 4901 possible values per cell.
  3. Say it draws 1250, 4730, 310 for entry 25-0412; 5000, 2875, 100 for 25-0413; 780, 3420, 3420 for 25-0414; 4105, 5000, 260 for 25-0415. Both bounds appear (100 once, 5000 twice), and 25-0414 has 3420 twice, because every cell is drawn on its own.
  4. Leave out the fifth argument and the same block fills with decimals such as 2718.4392 and 100.0427, because whole_number defaults to FALSE. An exact 5,0005.000 then becomes practically impossible, so the round-amount test would never be tested.
  5. Press F9 and all 12 amounts change. When the testing sheet works, select B2:D5 and paste as values before you replace them with real journal lines.

Watch out: Typing a real amount into C3 while the formula is still live gives #SPILL! in B2, because the typed value blocks the spill range. Freeze the block first.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The first two arguments shape the block to match the sheet, and the last three decide what goes in each cell. whole_number TRUE makes the bounds inclusive whole values, which is what euro amounts in an export are; without it the same bounds describe a continuous range of decimals.

More to watch out for

  • Swapping min and max, RANDARRAY(4, 3, 5000, 100, TRUE), gives #VALUE!; min must not be larger than max.

Variations

=ROUND(RANDARRAY(4, 3, 100, 5000), 2)
=ROUND(RANDARRAY(4; 3; 100; 5000); 2)

Amounts with cents between 100 and 5,0005.000, closer to a real export: ROUND to 2 decimals keeps them at cent level.

Advanced

Shuffle invoices into a random testing order

Sanne de Vries has six sales invoices of Veldhuis Installatie BV that she will vouch to supporting documents, and she wants a random testing order that nobody can call selective. The list may still grow. She wants the shuffled invoice numbers in one column next to the list, and she will freeze the order once it is recorded in the audit file.

ABCDE
1Invoice no.AmountTesting order
225-1101€8,450.00€ 8.450,0025-1105=SORTBY(A2:A7, RANDARRAY(ROWS(A2:A7)))=SORTBY(A2:A7; RANDARRAY(ROWS(A2:A7)))
325-1102€1,275.50€ 1.275,5025-1102
425-1103€23,900.00€ 23.900,0025-1104
525-1104€640.75€ 640,7525-1106
625-1105€5,180.00€ 5.180,0025-1101
725-1106€11,320.40€ 11.320,4025-1103
8
Formula cell (D2)InputSpilled result

Formula in D2

=SORTBY(A2:A7, RANDARRAY(ROWS(A2:A7)))
=SORTBY(A2:A7; RANDARRAY(ROWS(A2:A7)))

Result: 25-1105 / 25-1102 / 25-1104 / 25-1106 / 25-1101 / 25-110325-1105 / 25-1102 / 25-1104 / 25-1106 / 25-1101 / 25-1103Random: your result will differ

Step by step
  1. The innermost function first: ROWS(A2:A7) counts the rows of the list: 6.
  2. RANDARRAY(6) draws 6 decimals between 0 and 1, one per invoice. Say 0.6213 for 25-1101, 0.1534 for 25-1102, 0.8846 for 25-1103, 0.3419 for 25-1104, 0.0702 for 25-1105 and 0.5127 for 25-1106; yours will differ.
  3. SORTBY uses those 6 numbers as by_array1 and sorts ascending: 0.0702, 0.1534, 0.3419, 0.5127, 0.6213, 0.8846.
  4. Each invoice follows its number: 25-1105, 25-1102, 25-1104, 25-1106, 25-1101, 25-1103. They spill from D2 to D7; the random keys themselves never appear on the sheet.
  5. Whatever the draw, the output always contains the same six invoice numbers exactly once, because sorting only reorders. Record the order, then copy D2:D7 and paste as values, or the next edit reshuffles it.

Watch out: Using whole numbers as keys, RANDARRAY(6, 1, 1, 6, TRUE), looks equivalent but produces ties in about 98% of draws. Tied invoices keep their list order, so those near the top of the list are silently favored.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

Giving every item an independent random key and sorting by it makes every ordering equally likely, which is what a fair shuffle means. ROWS ties the number of keys to the list, so the key array always has the size SORTBY demands.

More to watch out for

  • Hard-coding RANDARRAY(6) works until a seventh invoice is added and the ranges are extended: then the key array is one short and the result is #VALUE!.

Variations

=TAKE(SORTBY(A2:A7, RANDARRAY(ROWS(A2:A7))), 3)
=TAKE(SORTBY(A2:A7; RANDARRAY(ROWS(A2:A7))); 3)

Only the first three of the shuffle: a random sample of 3 invoices to vouch first, never the same invoice twice.

=SORTBY(A2:B7, RANDARRAY(ROWS(A2:B7)))
=SORTBY(A2:B7; RANDARRAY(ROWS(A2:B7)))

Shuffle invoice numbers and amounts together, so each invoice keeps its amount.

MasterExcel 2024 or later

Draw a sample of manual entries without repeats

In December 2025 Veldhuis Installatie BV posted 45 manual memorial entries (journal MEM), numbered 1 to 45 in the extract. Sanne de Vries tests six of them, one per test focus, and no entry may be selected twice, so the six numbers must all differ. The number of draws must follow the list of sample items, and the formula must say so plainly if someone adds more items than there are entries.

ABCDEFG
1ItemTest focusEntry to testFirst entry11
211Round amount2727=LET( pool, SEQUENCE(F2 - F1 + 1, 1, F1), n, ROWS(B2:B7), shuffled, SORTBY(pool, RANDARRAY(ROWS(pool))), IF(n > ROWS(pool), "More items than entries", TAKE(shuffled, n)) )=LET( pool; SEQUENCE(F2 - F1 + 1; 1; F1); n; ROWS(B2:B7); shuffled; SORTBY(pool; RANDARRAY(ROWS(pool))); IF(n > ROWS(pool); "More items than entries"; TAKE(shuffled; n)) )Last entry4545
322Weekend posting33
433Posted after period close4444
544Unusual account pair1111
655Year-end cut-off3838
766No description1919
8
Formula cell (C2)InputSpilled result

Formula in C2

=LET(
  pool, SEQUENCE(F2 - F1 + 1, 1, F1),
  n, ROWS(B2:B7),
  shuffled, SORTBY(pool, RANDARRAY(ROWS(pool))),
  IF(n > ROWS(pool), "More items than entries", TAKE(shuffled, n))
)
=LET(
  pool; SEQUENCE(F2 - F1 + 1; 1; F1);
  n; ROWS(B2:B7);
  shuffled; SORTBY(pool; RANDARRAY(ROWS(pool)));
  IF(n > ROWS(pool); "More items than entries"; TAKE(shuffled; n))
)

Result: 27 / 3 / 44 / 11 / 38 / 1927 / 3 / 44 / 11 / 38 / 19Random: your result will differ

Step by step
  1. pool is SEQUENCE(45 - 1 + 1, 1, 1): a column of the 45 entry numbers 1, 2, 3 and so on up to 45. Each number exists once.
  2. n is ROWS(B2:B7): 6 sample items, so 6 draws. Add an item in row 8, extend the range, and n becomes 7 without touching the rest.
  3. shuffled sorts pool by RANDARRAY(45), 45 random decimals: one random key per entry. The result is all 45 numbers in a random order, still each exactly once.
  4. IF checks 6 > 45, which is FALSE, so TAKE(shuffled, 6) keeps the first six of the shuffle. Say they are 27, 3, 44, 11, 38 and 19; your draw will differ, but it will always be 6 different whole numbers from 1 to 45.
  5. The six numbers spill from C2 to C7, one next to each test focus in draw order: entry 27 is tested for round amounts, entry 19 for a missing description.
  6. Set F2 to 5 and the pool holds only 5 entries; n is 6, so IF returns More items than entries instead of a draw that would silently leave the last test focus without an entry. After the draw, copy C2:C7 and paste as values: there is no seed, so this draw can never be recreated.

Watch out: The mirror-image mistake is RANDARRAY(6, 1, 1, 45, TRUE). It looks right, six whole numbers from 1 to 45, but each cell is drawn on its own, and about 29% of draws contain a repeated number: one entry is tested twice for two foci, and another is never tested.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

RANDARRAY never supplies the selected entry numbers directly; it only supplies the order. The numbers come from SEQUENCE, which lists every entry once, so taking the first n of a shuffle is a draw without replacement, and repeats are impossible by construction.

Do not use this construction when the selection must be reproducible for a reviewer, because RANDARRAY has no seed. The alternative is a helper column of random keys created once and frozen as values next to the entry list, then sorted by that column, which costs an extra column but leaves evidence of exactly how the sample was chosen.

More to watch out for

  • A live draw recalculates when anyone types, so a later edit changes the selection after you wrote it in the working paper. Switch calculation to manual before the draw, press F9 once, and freeze.
  • For a population of hundreds of thousands of journal lines, SEQUENCE and RANDARRAY build and sort that many values on every recalculation. Shuffle once into a frozen column instead, or draw a few numbers and check for repeats.

Variations

=SORT(TAKE(SORTBY(SEQUENCE(45), RANDARRAY(45)), 6))
=SORT(TAKE(SORTBY(SEQUENCE(45); RANDARRAY(45)); 6))

The bare draw, without LET or the safety check, sorted from low to high so you can work through the extract in order. Shorter, but the 45 and 6 are typed in and do not follow the sheet.

Common mistakes

SymptomCauseFix
The same number appears twiceRANDARRAY(6, 1, 1, 45, TRUE) is used to draw 6 different lottery numbers, and sometimes a number appears twice.Each cell is an independent draw. For different numbers, shuffle the full range with SORTBY(SEQUENCE(45), RANDARRAY(45)) and take the first 6, as in the Master example.
Yesterday's numbers are different todayThe random numbers you analyzed yesterday are different today, and your notes no longer match the sheet.Opening the file recalculated them. Once a draw is final, copy the spill range and paste it as values.
Decimals like 6.2817 instead of whole numbersRANDARRAY(4, 3, 1, 10) is meant to give marks from 1 to 10 and shows values like 6.2817.whole_number defaults to FALSE, so you get decimals. Add TRUE as the fifth argument.
Workbook slow on every editA model with RANDARRAY(100000, 10) has become slow on every edit.A million volatile cells are redrawn, with everything that depends on them, on every change. Freeze the block as values once it has served its purpose, or set calculation to manual while you work.

Used in combinations