Most Excel trainers tell you to use RANDBETWEEN whenever you need random numbers. They’re half-right — and that half is dangerous. Since Excel 365 launched RANDARRAY in 2020, RANDBETWEEN has quietly become a legacy function with hard limits: no spill behavior, no array output, no control over distribution shape. Yet thousands still force it into modern workflows — then wonder why their Monte Carlo simulations break when copied down or why Ctrl+Z undoes *all* random values at once.
The Problem
You’re building a sales training dashboard for Alibaba’s regional partners. You need randomized mock deal values, close dates, and product categories — all tied to real names like 'Sarah Chen' or 'Javier Morales'. But your current RANDBETWEEN setup keeps failing:
| Name | Current Formula | Result | Issue |
|---|---|---|---|
| Sarah Chen | =RANDBETWEEN(10000,99999) |
$72,415 | ✅ Works — but recalculates on every edit |
| Javier Morales | =RANDBETWEEN(10000,99999) |
$72,415 | ❌ Same value as Sarah — because F9 triggered recalc on whole sheet |
| Amina Patel | =RANDBETWEEN(10000,99999) |
$72,415 | ❌ All three values identical after pressing F9 — not independent |
| Diego Wong | =RANDBETWEEN(10000,99999) |
#N/A | ❌ Broke after inserting row above — formula shifted to A1 and returned error |
| Lina Dubois | =RANDBETWEEN(B2,C2) |
#VALUE! | ❌ Refers to blank cells — RANDBETWEEN doesn’t tolerate empty arguments |
The Solution
RANDBETWEEN isn’t broken — it’s just being asked to do things it was never designed for. Here’s how to fix each failure in under 90 seconds:
- Click cell D2 (where Sarah’s value lives) and type:
=RANDBETWEEN(10000,99999). Press Enter. - Select D2, then press Ctrl+C. Click D3, hold Shift, and press ↓ until D6 is selected. Press Ctrl+V.
- Now stop copying. Instead, select D2:D6, press F2, then Ctrl+Enter. This pastes the formula into all 5 cells *without* linking them to the same random seed.
- To lock values permanently (so they won’t change on recalc), select D2:D6, press Ctrl+C, then Alt+E+S+V → Enter (Paste Values).
That last step — Alt+E+S+V — is critical. It bypasses Excel’s volatile recalc engine entirely. What makes this elegant is that you get true independence: each cell holds its own static integer, no shared dependency tree.
| Name | Deal Value | Close Date (RANDBETWEEN) | Status |
|---|---|---|---|
| Sarah Chen | $45,200 | 2024-03-15 | Won |
| Javier Morales | $81,937 | 2024-06-22 | Lost |
| Amina Patel | $29,411 | 2024-04-08 | Pending |
| Diego Wong | $63,752 | 2024-05-30 | Won |
| Lina Dubois | $55,888 | 2024-07-11 | Lost |
Note: For the Close Date column, we used =DATE(2024,RANDBETWEEN(3,7),RANDBETWEEN(1,30)) in E2:E6 — yes, nesting works, but only if both arguments are numeric. Blank cells in helper columns will still crash it.
Going Further
Three variations most people never try — but should:
- Random text from a list: Combine RANDBETWEEN with CHOOSE:
=CHOOSE(RANDBETWEEN(1,4),"Cloud","Logistics","Payments","Hardware")in F2:F6 gives you realistic Alibaba service categories. - Weighted randomness: RANDBETWEEN can’t do this alone — but pair it with INDEX/XLOOKUP and cumulative weights. Set up a table in H1:I5:
H1=1,H2=SUM(H1,I1)+1, etc., then use=INDEX(I1:I4,RANDBETWEEN(1,100))where I1:I4 holds weighted labels. - Non-repeating integers: Use
=SORTBY(SEQUENCE(5),RANDARRAY(5))instead — but if you *must* use RANDBETWEEN, generate 10 values and filter duplicates manually. Yes, it’s clunky. That’s why RANDARRAY exists.
The surprising tip? RANDBETWEEN recalculates when you open the file — even if you haven’t edited anything. To freeze it on open, paste values immediately after generation, or better yet, use Power Query to inject static randoms during data load.
When NOT to Use This
RANDBETWEEN fails silently in four specific cases:
- Dynamic arrays: If you type
=RANDBETWEEN(1,100)in A1 and press Enter, it spills nothing. You’ll get one number — not 100. Use RANDARRAY instead. - Negative ranges:
=RANDBETWEEN(-10,-1)works, but=RANDBETWEEN(-5,5)includes zero — and many users forget that. Always test edge cases. - Dates before 1900: Excel stores dates as serial numbers.
=RANDBETWEEN(0,100)returns Jan 1900–April 1900 — not useful for historical simulation. - Shared workbooks: In co-authored files, RANDBETWEEN may recalculate inconsistently across devices. Values diverge. Never use it in collaborative planning sheets.
Also: RANDBETWEEN cannot reference closed workbooks. If your low/high bounds live in [Budget.xlsx]Sheet1!$B$2:$B$3, it returns #REF! — unlike XLOOKUP or SUMIFS.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Recalculate all volatile formulas | F9 | Triggers RANDBETWEEN everywhere — use sparingly |
| Paste Values only | Alt+E+S+V | Fastest way to lock RANDBETWEEN output |
| Fill formula down without dragging | Ctrl+D | But avoid this with RANDBETWEEN — use Ctrl+Enter instead |
| Edit active cell | F2 | Required before Ctrl+Enter to fill multiple cells |
