Generate Random Numbers & Picks

Excel Formulas › Math

All versionsRANDBETWEENRAND

RANDBETWEEN gives a random whole number in a range — for test data, dice rolls, or random sampling. RAND gives a random decimal between 0 and 1, the engine behind random picks and shuffles.

Quick formula: for a random whole number from 1 to 100:
=RANDBETWEEN(1, 100)
Every recalculation produces a new value. RAND() gives a decimal 0–1 instead, for finer control.

Functions used (tap for the full reference guide):

RANDBETWEENRANDINDEX

The example

Random values for sample data (yours will differ — they reroll each calc).

AB
1FormulaExample result
2RANDBETWEEN(1,100)47
3RANDBETWEEN(1,6) (die)4
4RAND()0.812

The formula

A random number in a range:

=RANDBETWEEN(1, 100) // new whole number each recalc

How it works

Two random generators for different needs:

  1. RANDBETWEEN(low, high) returns a random whole number in that inclusive range — great for IDs, dice, or test data.
  2. RAND() returns a random decimal from 0 up to 1 — use it for probabilities or as a sort key.
  3. Both are volatile: they recalculate (reroll) on every edit and when you press F9.
  4. Random pick from a list: =INDEX(list, RANDBETWEEN(1, ROWS(list))) grabs a random item.

To freeze a random value so it stops changing, copy the cell and Paste Special → Values. Otherwise every recalculation gives a new number — which is great for generating data, but not if you need it to stay put.

Try it: interactive demo

Live demo

Set a range and reroll.

Random:

Variations

Random pick from a list

Grab a random item:

=INDEX(A2:A100, RANDBETWEEN(1, ROWS(A2:A100)))

Random decimal in a range

Between min and max:

=RAND() * (max - min) + min

Random array (365)

A whole grid at once:

=RANDARRAY(5, 1, 1, 100, TRUE)

Pitfalls & errors

They never stop changing. RAND/RANDBETWEEN reroll on every recalc. Paste Special → Values to lock a result in place.

RANDBETWEEN can repeat. It samples with replacement, so duplicates happen. For unique picks, see “random without repeats.”

Volatile = slower. Thousands of volatile random cells can bog down a big workbook.

Practice workbook

📊
Download the free Generate Random Numbers & Picks practice workbook
RANDBETWEEN/RAND examples (results shown; they reroll in Excel), the random-pick and RANDARRAY variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I generate a random number in Excel?
Use =RANDBETWEEN(1, 100) for a random whole number in a range, or =RAND() for a random decimal between 0 and 1. Both reroll on each recalculation.
How do I pick a random item from a list?
Use =INDEX(list, RANDBETWEEN(1, ROWS(list))), which returns a random entry from the range.
How do I stop a random number from changing?
Copy the cell and use Paste Special > Values to convert it to a fixed value, since RAND and RANDBETWEEN recalculate every time.

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

Related formulas: Random selection without repeats · Combinations & permutations · Generate numbers with SEQUENCE

Function references: RANDBETWEEN · RAND · INDEX