How to Randomly Select, Sample & Shuffle Rows in Excel

TL;DR — Every random pick in Excel is the same trick: give each row a random number, then sort by that number. Whatever you take off the top is a random sample; the whole thing reordered is a shuffle. On Excel 365 it's one formula — =TAKE(SORTBY(data, RANDARRAY(ROWS(data))), n) picks n random rows with no repeats. The one distinction that decides whether your sample is valid: without replacement (sort-by-random — every pick distinct) vs with replacement (INDEX + RANDBETWEEN — picks can repeat). And if you sort by a live RAND(), freeze it to values first, or it re-rolls mid-sort.

' Modern (Excel 365) — pick 10 random rows, no repeats:
=TAKE(SORTBY(A2:C500, RANDARRAY(ROWS(A2:C500))), 10)

' Shuffle a whole list into random order:
=SORTBY(A2:A100, RANDARRAY(ROWS(A2:A100)))

' Classic (any version) — helper column, then sort by it:
' B2:  =RAND()      then Copy → Paste Values → sort by column B → take top N

"Pick some rows at random" sounds trivial, and the formula almost is. What separates a correct random sample from a plausible-looking wrong one is a single choice most people never notice they're making — whether the same row can be picked twice. Get that right and the mechanics are easy.

What you'll learn

The universal pattern: random key → sort → take (everything is a variation of it)
The modern one-liner with SORTBY + RANDARRAY, and the classic RAND helper
The distinction that ruins samples: without replacement vs with replacement
The freeze-before-you-sort trap that makes live RAND() samples irreproducible
How to split a list into fair random groups (random assignment)

The one pattern behind all of it: random key → sort → take

There is really only one idea here, and every method below is a costume for it:

Attach a random number to each row.
Sort the rows by that random number.
Take however many you need off the top (or take them all, for a shuffle).

Because the sort order is random, the top of the list is a random subset, and the full reordered list is a shuffle. That's it. Sampling, shuffling, drawing winners, splitting into teams — all of them are "sort by randomness, then slice." Once you see that, you're not memorizing five recipes; you're applying one.

The modern one-liner (Excel 365)

On 365, RANDARRAY makes the random key, SORTBY does the sorting, and TAKE slices the top — one formula, no helper column, and it spills:

=SORTBY(A2:A100, RANDARRAY(ROWS(A2:A100)))            ' shuffle the whole list
=TAKE(SORTBY(A2:C500, RANDARRAY(ROWS(A2:C500))), 10)  ' 10 random rows, all columns

RANDARRAY(ROWS(range)) generates exactly one random key per row; SORTBY reorders the rows by those keys; TAKE(..., 10) keeps the first ten. Because every row gets its own distinct key and is merely reordered, no row can appear twice — this is sampling without replacement, which is what "a random sample of 10 customers" almost always means.

The classic method (any Excel version)

Before dynamic arrays, the same pattern used a helper column. It still works everywhere and is the right tool when you're on Excel 2019 or earlier:

In a spare column next to your data, enter =RAND() and fill it down every row.
Copy that column, then Paste Special → Values (this is the critical step — see below).
Sort your data by that column.
The top N rows are your random sample; the whole thing is now shuffled.

Same three moves — random key, sort, take — just done by hand. For picking a single random row you can also skip the sort entirely:

=INDEX(A2:A100, RANDBETWEEN(1, ROWS(A2:A100)))   ' one random row — CAN repeat if reused

Note that INDEX + RANDBETWEEN reused down a column samples with replacement — the same row can come back — which is the whole point of the next section.

The distinction that ruins samples: with vs without replacement

This is the insight the whole article is built around, because getting it wrong produces a wrong answer that looks completely fine.

Without replacement — each row can be chosen at most once. This is the sort-by-random method. It's what you want for a sample of distinct customers, a shuffle, a draw of unique winners, or splitting people into teams.
With replacement — each pick is independent and the same row can be chosen again. This is INDEX(range, RANDBETWEEN(1, n)) repeated, or RANDBETWEEN used to generate row numbers. It's correct for simulations (bootstrapping, dice) where repeats are legitimate.

The failure mode is silent and expensive: someone builds a "random sample of 50 customers" by copying =INDEX(list, RANDBETWEEN(1, 500)) down 50 rows, and the sample quietly contains the same customer two or three times. No error, no warning — just a biased sample that overweights whoever got drawn twice. Rule: if the word "sample" or "unique" is anywhere in the task, you need without replacement — sort by a random key, never RANDBETWEEN row numbers.

The freeze-before-you-sort trap

Here's the subtle one that catches people using the classic method. RAND() is volatile: it re-rolls on every recalculation — and sorting is a recalculation. So if you sort your data by a live =RAND() column, Excel generates brand-new random numbers during the sort, and the order you get no longer matches the numbers you're looking at. Worse, the "sample" changes every time anyone touches the sheet, so it can never be reproduced or audited.

The fix is step 2 above, and it is not optional: Copy the RAND() column and Paste Special → Values to freeze it before you sort. Once the keys are static numbers, the sort is stable and the sample is reproducible. For anything auditable — a compliance sample, a randomized trial assignment — record the frozen keys (or the date you drew them); a random result nobody can reproduce is worthless as evidence.

Bonus: split a list into fair random groups

Random assignment — dividing people into N teams, A/B buckets, or shifts — is the same pattern with a twist at the end. Attach a random key, rank it, then map the rank into buckets with MOD:

' After freezing a RAND() key in column B, rank each row and assign to 3 groups:
=MOD(RANK(B2, $B$2:$B$100) - 1, 3) + 1     ' -> 1, 2, or 3, evenly and randomly

RANK turns the random keys into positions 1..N; MOD(..., 3) deals those positions into three groups like dealing cards, so the groups come out the same size (±1) but randomly composed. Change the 3 to however many buckets you need.

The judgment call

Stop collecting recipes and hold onto the one pattern: random key → sort → take. Sampling, shuffling, drawing winners, and assigning groups are all that pattern with a different last step. The two decisions that actually determine whether your result is correct are, first, replacement — if the task says "sample" or "unique," you need without-replacement (sort by a random key), not repeatable RANDBETWEEN picks — and second, freezing: convert a live RAND()/RANDARRAY() key to values before you sort, or your sample re-rolls out from under you and can never be reproduced. Everything else is just plumbing.

How ExcelMaster helps

The replacement distinction is exactly the kind of thing that's easy to get wrong and hard to notice. Tell ExcelMaster "pick 30 random customers from this list, no repeats" and it builds =TAKE(SORTBY(range, RANDARRAY(ROWS(range))), 30) — the without-replacement method — rather than a RANDBETWEEN approach that would silently double-count. Ask it to "shuffle these names" or "randomly split this roster into 4 teams" and it writes the sort-by-random or MOD-into-buckets formula, and reminds you to freeze the random keys to values so the result stays put. You describe the draw; it picks the statistically correct mechanism and keeps it reproducible.

Frequently asked questions

How do I select random rows in Excel?

Attach a random number to each row and sort by it. On Excel 365, one formula does it: =TAKE(SORTBY(data, RANDARRAY(ROWS(data))), n) returns n random rows with no repeats. In any version, add a helper column of =RAND(), Copy → Paste Special → Values to freeze it, sort by that column, and take the top n rows.

How do I randomize (shuffle) a list in Excel?

Sort the list by a random key. Modern: =SORTBY(range, RANDARRAY(ROWS(range))) returns the whole list in random order. Classic: put =RAND() in a helper column, freeze it to values, and sort your data by it. Both reorder every item exactly once.

What's the difference between sampling with and without replacement?

Without replacement means each row can be picked at most once — the sort-by-random method (SORTBY + RANDARRAY, or a frozen RAND column). With replacement means the same row can be picked more than once — INDEX(range, RANDBETWEEN(1, n)) repeated. Use without replacement for real samples of distinct items; use with replacement only for simulations where repeats are intended.

Why does my random sample change every time?

Because RAND() and RANDARRAY() are volatile and re-roll on every recalculation. If your sample is driven by live random formulas, it will differ each time the sheet recalculates. Freeze the random values first — select them, Copy, then Paste Special → Values — before you sort or record the sample.

How do I randomly assign items to groups in Excel?

Attach and freeze a =RAND() key, then bucket the ranks: =MOD(RANK(key, keyrange) - 1, N) + 1 assigns each row to one of N groups, evenly and randomly. Change N to the number of groups you want (3 for three teams, 2 for an A/B split, and so on).

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-11.