Excel RAND & RANDBETWEEN — Generate Random Numbers (and Stop Them Changing)

TL;DR — Excel's two built-in random-number generators are RAND(), which returns a random decimal from 0 up to (but never reaching) 1, and RANDBETWEEN(bottom, top), which returns a random integer between bottom and top with both ends included. The one fact that explains every surprise: both are volatile — they generate a new number on every edit, every F9, and every time the file opens. "My random numbers keep changing" isn't a bug; it's the design. To lock them, Copy → Paste Special → Values. And RANDBETWEEN repeats — it rolls dice, it doesn't deal cards.

=RAND()                       ' -> 0.4173...   decimal in [0, 1)
=RANDBETWEEN(1, 6)            ' -> 4           integer 1–6, both inclusive
=RANDBETWEEN(-10, 10)        ' -> -3          negative bounds are fine
=50 + (100 - 50) * RAND()     ' -> 73.9        random decimal in [50, 100)
=ROUND(50 + 50*RAND(), 2)     ' -> 82.14       random price to 2 decimals

Generating a random number in Excel is one line. The part that actually trips people up is controlling it — stopping the number from re-rolling the instant you touch anything else, and knowing that "random integers" and "a random sample" are two different jobs. Get the volatility model and the freeze trick, and this whole corner becomes predictable.

What you'll learn

The mental model: RAND and RANDBETWEEN are live dice, not stored values
Why volatility is the number-one source of "my numbers keep changing"
How to freeze random numbers so they stop moving (Paste Special → Values)
Why RANDBETWEEN repeats — inclusive, with replacement
The =a+(b-a)*RAND() pattern for random decimals in any range
When volatile randomness quietly slows a large model — and what to do

The mental model: live dice, not stored numbers

Most Excel functions look something up or calculate a fixed answer — ask twice, get the same thing. Random functions are different: each is a die that Excel re-rolls every time the sheet recalculates. The cell doesn't hold a number; it holds an instruction to roll one now. That single mental shift explains everything below.

RAND() takes no arguments and returns a uniform decimal in the half-open interval [0, 1) — zero is possible, one is not. RANDBETWEEN(bottom, top) returns a whole number from bottom to top, and — this is the part people misread — both bounds are included, so RANDBETWEEN(1, 6) really can return 1 or 6. The number of possible outcomes is top − bottom + 1 (six for a die, not five).

Everything you'll ever build with randomness is one of these two dice, reshaped: a decimal die you stretch into any range, or an integer die you roll for IDs, sample data, or dice-game logic.

Volatility is the #1 gotcha: they re-roll on every edit

Type =RANDBETWEEN(1, 100) in A1, then type your name in A2 and press Enter. Watch A1 — it changed. You didn't touch it, but you triggered a recalculation, and a volatile function re-rolls on every recalc: any edit anywhere, pressing F9, sorting, inserting a row, or just reopening the workbook.

=RAND()          ' a NEW number every recalc — every edit, every F9, every file-open
=RANDBETWEEN(1,100)  ' same: never sits still while it's a live formula

This is the root of nearly every RAND/RANDBETWEEN complaint — "my random assignments shuffle themselves," "the numbers changed before I could copy them," "my sample is different every time I open the file." None of that is a malfunction. It's a live die doing exactly what a die does. The only question is whether you want it live (a what-if model that should re-roll on demand) or frozen (a sample you need to keep). That decision is the next section.

How to freeze random numbers so they stop moving

When you want to keep a set of random numbers — the winners are drawn, the sample is picked — you convert the live formulas into static values. Excel has no random seed, so freezing is the only way to make results reproducible:

Select the cells with =RAND() / =RANDBETWEEN(...).
Copy (Ctrl+C).
Paste Special → Values (Ctrl+Alt+V, then V) onto the same selection.

The formulas become plain numbers that will never re-roll again. For a single cell you can also select the formula in the formula bar and press F9 to bake it to a literal.

Rule: draw with a live formula, then freeze to values the moment the draw is final. A random result that silently changes on the next keystroke is worse than useless for anything you'll email, audit, or act on — and "I'll copy it later" fails the first time someone else opens the file and Excel re-rolls on load.

RANDBETWEEN repeats: it rolls dice, it doesn't deal cards

Here is the misconception that causes real damage. People reach for RANDBETWEEN(1, 500) to assign 500 unique IDs, or roll it down 50 rows to "pick 50 random people," and are baffled when values repeat.

=RANDBETWEEN(1, 500)   ' filled down 500 rows -> guaranteed duplicates

RANDBETWEEN samples with replacement: every roll is independent and the full range is available every time, exactly like a die that can land on 4 twice in a row. By the birthday-problem math, duplicates show up far sooner than intuition expects — roll a 1–100 die just 12 times and a collision is more likely than not.

So the judgment line is sharp:

Need repeats allowed (dice, simulated ratings, sample test data) → RANDBETWEEN is correct.
Need every result distinct (unique winners, a sample of rows without replacement, shuffling a list) → RANDBETWEEN is the wrong tool entirely. That's a selection problem, solved by attaching a random key and sorting — see How to Randomly Select, Sample & Shuffle in Excel.

Piling IF(COUNTIF(...)) guards onto RANDBETWEEN to force uniqueness is a fragile patch for using the wrong function. Reach for the sort-by-random pattern instead.

Random decimals in any range: the =a+(b-a)*RAND() pattern

RANDBETWEEN only does integers, so the instinct to get a random price with RANDBETWEEN(50, 100)/1 (integers only) or RANDBETWEEN(5000, 10000)/100 (snaps to a 1-cent grid, subtly biased) is a workaround for the wrong function. For continuous random values, stretch RAND():

=a + (b - a) * RAND()              ' uniform decimal in [a, b)
=50 + (100 - 50) * RAND()          ' -> a decimal anywhere in [50, 100)
=ROUND(50 + 50*RAND(), 2)          ' -> the same, rounded to a real price
=TODAY() + RANDBETWEEN(0, 30)      ' -> a random date in the next 30 days

Read the pattern through the mental model: RAND() gives a fraction of the way from 0 to 1; multiplying by the width (b − a) scales that fraction to your range; adding a slides it to the right starting point. Wrap it in ROUND when you need clean decimals or currency. This one formula covers every "random number between X and Y" that isn't a plain integer.

The judgment call

RAND versus RANDBETWEEN is the easy part — RAND for decimals, RANDBETWEEN for integers, and the a+(b-a)*RAND() stretch for a decimal in a custom range. The decisions that actually matter are about state, not syntax. First: is this die supposed to stay live, or should it be frozen? If the numbers back a decision, freeze them to values immediately — an unfrozen sample won't survive the next recalc, let alone an audit. Second: do you need repeats or not? RANDBETWEEN happily repeats, so the moment your task says "unique" or "sample without replacement," stop stacking it and switch to the sort-by-random approach. Nail those two calls and randomness in Excel stops fighting you.

How ExcelMaster helps

Volatility and the repeats trap are exactly the kind of thing a formula assistant should handle for you. Tell ExcelMaster "give me 50 random test scores between 60 and 100" and it writes the right generator, then offers to freeze the result to values so it stops re-rolling. Ask for "a random price between $50 and $99.99" and it reaches for =ROUND(50+49.99*RAND(),2) instead of a biased integer hack. And when you ask for something that actually needs distinct results — "pick 20 random customers, no repeats" — it recognizes that's a sampling job and builds the sort-by-random formula rather than a RANDBETWEEN you'll have to de-duplicate by hand.

Frequently asked questions

What is the RAND function in Excel?

=RAND() returns a uniformly distributed random decimal that is greater than or equal to 0 and less than 1. It takes no arguments. It's volatile, so it generates a new value every time the worksheet recalculates — on any edit, when you press F9, or when the file reopens. To get a decimal in a custom range [a, b), use =a+(b-a)*RAND().

How does RANDBETWEEN work, and are the bounds inclusive?

=RANDBETWEEN(bottom, top) returns a random integer between bottom and top with both ends included — =RANDBETWEEN(1, 6) can return any of 1, 2, 3, 4, 5, or 6. The bounds can be negative (=RANDBETWEEN(-10, 10)). Like RAND, it's volatile and re-rolls on every recalculation.

Why do my random numbers keep changing in Excel?

Because RAND and RANDBETWEEN are volatile functions — they produce a new random value on every recalculation, which is triggered by nearly any action (editing a cell, pressing F9, sorting, or reopening the workbook). To stop them changing, convert them to static values: select the cells, Copy, then Paste Special → Values.

How do I stop random numbers from recalculating?

Freeze them to values. Select the cells containing the random formulas, press Ctrl+C, then paste over the same range with Paste Special → Values (Ctrl+Alt+V, then V). The cells become fixed numbers that no longer re-roll. For a single cell, click into the formula bar, select the formula, and press F9 to convert it to a literal value.

How do I generate random numbers without duplicates?

Don't use RANDBETWEEN — it samples with replacement and will repeat. To get distinct random values or a sample without repeats, attach a random key to each item and sort by it, e.g. =SORTBY(list, RANDARRAY(ROWS(list))) to shuffle, or take the top N of that. See How to Randomly Select, Sample & Shuffle in Excel.

Tested in

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