Why does your ‘random’ sample always include Sarah Chen and never Rajiv Patel? Why does refreshing give you the same 5 names every time? Why does your manager ask, ‘Is this *really* random — or just shuffled?’
Quick Answer
Use =RAND() in a helper column, then sort the whole table by that column — but only after disabling automatic recalculation or copying values first. Otherwise, every sort or edit regenerates the numbers, breaking reproducibility. That’s what most people miss.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| RAND + Sort | Add =RAND() in new column → select full range → Data > Sort → choose RAND column | Small lists (<500 rows), one-time sampling | Fails on refresh; not reproducible; breaks if sheet recalculates mid-sort |
| RANDBETWEEN + INDEX/MATCH | Generate unique IDs with RANDBETWEEN, use INDEX to pull matching rows | Fixed-size samples without duplicates | Slow above 10k rows; requires array formula handling in older Excel |
| Analysis ToolPak – Sampling | Data > Data Analysis > Sampling → input range, sample size, periodic vs. random | Auditors, compliance reports, legacy workflows | ToolPak must be enabled; no dynamic updates; outputs static values only |
| SORTBY + SEQUENCE (Excel 365) | =TAKE(SORTBY(A2:C100,RANDARRAY(ROWS(A2:C100))),5) | Modern Excel users needing live, scalable, duplicate-free samples | Not available in Excel 2019 or earlier; volatile — recalcs on every sheet change |
| Power Query Random Index | Add Index → Add Custom Column = Number.Random(), Sort by it → Remove Index | Large datasets (>100k rows), repeatable workflows, ETL pipelines | No native undo; requires PQ refresh to regenerate; extra step to load back to sheet |
Method 1 Deep Dive
We’ll use the classic RAND + Sort method — but do it right this time.
Start with real data in A1:C12:
| Name | Company | Revenue |
|---|---|---|
| Sarah Chen | Nexus Labs | $124,500 |
| Rajiv Patel | Vanta Systems | $89,200 |
| Maya Johnson | Acme Corp | $211,750 |
| Diego Mora | Strata Dynamics | $64,900 |
| Aisha Kim | Orion Group | $153,300 |
| Kenji Tanaka | Lumeo Inc | $97,100 |
| Fatima Hassan | Clio Solutions | $182,400 |
| Eliot Reed | TerraLink | $73,600 |
| Zara Wu | Helix Partners | $144,200 |
| Omar Diallo | Kairo Analytics | $112,800 |
| Lena Vogt | Nordic Flow | $86,500 |
| Tariq Singh | Quill & Byte | $137,900 |
Step 1: In D1, type Random. In D2, enter =RAND(). Drag down to D12.
Step 2: Select A1:D12. Press Alt → A → S → S (opens Sort dialog). Choose “Expand the selection”. Sort by Column D, Smallest to Largest.
Step 3: Now — here’s the counterintuitive part — immediately copy D2:D12 and paste as Values (right-click → Paste Values, or Ctrl+Alt+V, then V). If you skip this, every time you click another cell, RAND regenerates, and your sort order collapses.
Step 4: Keep only the top 5 rows (A1:C5). Done.
Method 2 Deep Dive
For Excel 365 users who need something dynamic and clean, use SORTBY + RANDARRAY.
In a blank cell like F1, paste this exact formula:
=TAKE(SORTBY(A2:C12,RANDARRAY(ROWS(A2:C12))),5)
This pulls 5 rows from A2:C12, sorted by a fresh set of random numbers each time the sheet recalculates.
It works because RANDARRAY(ROWS(A2:C12)) returns 11 random decimals — one per row — and SORTBY uses them as sort keys. TAKE grabs just the first 5.
Surprising tip: If you want *reproducible* randomness (e.g., for audit trails), replace RANDARRAY() with RANDARRAY(,1,1,1000000,TRUE) and seed it using a fixed number — but don’t. Instead, use Power Query. More on that below.
One caveat: This formula spills. Don’t put anything in F2:F6 — Excel will throw #SPILL!.
Cheat Sheet
| Task | Action | Shortcut | Notes |
|---|---|---|---|
| Insert RAND column | Type =RAND() in first empty column, drag down | — | Always start at row 2 if header is in row 1 |
| Open Sort dialog | Select full data range including RAND column | Alt+A+S+S | Critical: Check “Expand selection” |
| Paste RAND as values | Copy RAND column → right-click → Paste Values | Ctrl+Alt+V, then V | Do this *before* sorting, or right after |
| Get 5-row sample (365) | =TAKE(SORTBY(A2:C12,RANDARRAY(11)),5) | — | Replace 11 with ROWS(A2:C12) for flexibility |
| Enable Analysis ToolPak | File > Options > Add-ins > Manage Excel Add-ins > Check “Analysis ToolPak” | Alt+F+T → G → Alt+X | Only needed once per installation |
| Freeze current random state | Press F9 to recalc → immediately copy RAND column → Paste Values | F9, then Ctrl+Alt+V+V | This locks your sample before sorting |
