The first thing most people do when they type =RAND() is copy it down a column and call it done. That’s dangerous. Every time you hit Enter, press F9, or even scroll past the cell, RAND() spits out a new number. Your ‘random sample’ isn’t stable — it’s a live grenade.
The Setup
You’re building a quarterly sales mockup for internal training. Finance needs 10 fictional leads with realistic names, company sizes, and estimated deal values — but they must stay fixed once created. No drifting. No surprise changes during review.
| A: Lead ID | B: Name | C: Company | D: Est. Value ($) | E: Status |
|---|---|---|---|---|
| L-001 | Maya Rodriguez | Nexus Labs | $72,500 | Prospect |
| L-002 | James Lin | Vanta Systems | $114,800 | Qualified |
| L-003 | Tasha Bell | Oryx Dynamics | $39,200 | Prospect |
| L-004 | Diego Morales | Stellara Inc | $87,600 | Proposal Sent |
| L-005 | Anya Petrova | Kairo Group | $132,100 | Qualified |
| L-006 | Rajiv Mehta | Helix Data | $55,400 | Prospect |
| L-007 | Chloe Tan | Veridia Corp | $98,300 | Proposal Sent |
| L-008 | Marcus Wu | Tiberius AI | $210,700 | Qualified |
| L-009 | Priya Desai | Flare Solutions | $63,900 | Prospect |
| L-010 | Elena Kim | Quill & Co | $44,100 | Qualified |
The Challenge
You need random deal values between $35,000 and $220,000 — but they must lock in after creation. Not shift when someone opens the file on another PC. Not change if the user hits F9. Not flip if you insert a row above row 5.
RAND() alone can’t do that. It’s volatile. So is RANDBETWEEN(). Both recalculate on *any* workbook change — including editing unrelated cells, saving, or opening the file.
The real trap? People think “I’ll just copy → paste values” later. But they forget — or do it too late. By then, the numbers have already changed three times during review.
Walking Through It
Step 1: In cell D2, enter =RANDBETWEEN(35000,220000). Copy down to D11. You now have 10 volatile numbers.
Step 2: Select D2:D11. Press Ctrl+C.
Step 3: Right-click → Paste Special → Values (or use Alt+E, S, V, Enter). This replaces formulas with static numbers.
Step 4 (surprising tip): Before pasting values, press F9 *while the formula cells are selected*. Why? Because F9 forces recalculation *only on selected cells*. You get one final, intentional roll — not an accidental one later.
| Before (volatile) | After (static) |
|---|---|
=RANDBETWEEN(35000,220000) |
$147,283 |
=RANDBETWEEN(35000,220000) |
$81,650 |
=RANDBETWEEN(35000,220000) |
$192,314 |
=RANDBETWEEN(35000,220000) |
$55,892 |
The Result
Here’s your final lead list — now fully stable. No more recalculation surprises. Values won’t budge when you sort, filter, or email the file.
| A: Lead ID | B: Name | C: Company | D: Est. Value ($) | E: Status |
|---|---|---|---|---|
| L-001 | Maya Rodriguez | Nexus Labs | $147,283 | Prospect |
| L-002 | James Lin | Vanta Systems | $81,650 | Qualified |
| L-003 | Tasha Bell | Oryx Dynamics | $192,314 | Prospect |
| L-004 | Diego Morales | Stellara Inc | $55,892 | Proposal Sent |
| L-005 | Anya Petrova | Kairo Group | $167,405 | Qualified |
| L-006 | Rajiv Mehta | Helix Data | $73,112 | Prospect |
| L-007 | Chloe Tan | Veridia Corp | $118,769 | Proposal Sent |
| L-008 | Marcus Wu | Tiberius AI | $204,833 | Qualified |
| L-009 | Priya Desai | Flare Solutions | $39,527 | Prospect |
| L-010 | Elena Kim | Quill & Co | $129,901 | Qualified |
What Could Go Wrong
| Symptom | Cause | Fix |
|---|---|---|
| Deal values change mid-presentation | User pressed F9 or clicked into a cell containing RAND() | Paste values *before* sharing. Confirm no formulas remain in D2:D11 using Ctrl+` (tilde) to toggle formula view. |
| Numbers look suspiciously round (e.g., all end in 00) | Used =ROUND(RAND()*100000,0) instead of RANDBETWEEN() | Use RANDBETWEEN(35000,220000) directly — avoids rounding bias and edge-case zero results. |
| Entire column shifts when inserting a new row | Pasted values into a Table (structured reference), triggering auto-fill | Convert range to regular range first (Ctrl+A, Ctrl+T, uncheck 'My table has headers'), then paste values. |
