Picking random names without picking the same one twice — a raffle, a shuffled order, random team assignment — needs sampling without replacement. In Excel 365, SORTBY + RANDARRAY shuffles a list in one formula.
The example
A name list shuffled into a random order (no name repeats).
| A | C | ||
|---|---|---|---|
| 1 | Names | Shuffled | |
| 2 | Ana | Cy | |
| 3 | Ben | Ana | |
| 4 | Cy | Dot | |
| 5 | Dot | Ben |
The formula
The shuffled list:
How it works
Sort by a random key to shuffle:
RANDARRAY(ROWS(A2:A5))generates one random number per name — a random sort key for each row.SORTBY(A2:A5, …)reorders the names by those random keys, producing a shuffle.- Because it’s a reorder of the original list, no name repeats — that’s sampling without replacement.
- For a random sample of N, wrap in
TAKE(…, N)or just read the top N rows of the shuffle.
Pre-365? Add a helper column of =RAND(), then sort the list by that column — or use =INDEX(list, RANK(RAND(), randColumn)). The SORTBY approach just does it in one cell.
Try it: interactive demo
Shuffle the list (no repeats) — click to reshuffle.
Variations
Random sample of N
Take the top N of the shuffle:
Legacy helper-column shuffle
Add =RAND() beside the list and sort by it.
Random unique numbers
Shuffle a sequence:
Pitfalls & errors
It reshuffles on every recalc. Like all random formulas, the order changes constantly. Paste Special → Values to freeze a draw.
Excel 365 for the one-formula version. SORTBY/RANDARRAY need 365/2021. Older Excel uses the RAND helper-column method.
Ties are astronomically unlikely with RANDARRAY, but on tiny lists a repeated key could occur — harmless, it just keeps original order for the tie.
Practice workbook
Frequently asked questions
How do I randomly shuffle a list in Excel?
How do I pick N random items without duplicates?
How do I shuffle without Excel 365?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class