Guide
How to Generate Random Numbers in Excel
Use RANDBETWEEN, RAND and RANDARRAY to create random whole numbers, decimals and unique lists in Excel and Google Sheets, and how to stop them recalculating.
On this page
Excel and Google Sheets include built-in random functions. They are quick for everyday work, with a few behaviours worth knowing.
Random whole numbers
=RANDBETWEEN(1,100)
Returns a whole number from 1 to 100. Both ends are included. Fill the formula down a column to get a list.
Random decimals
=RAND() → decimal from 0 up to (not including) 1
=RAND()*(10-5)+5 → decimal between 5 and 10
=ROUND(RAND()*(10-5)+5,2) → rounded to 2 decimal places
A list in one formula
Excel for Microsoft 365 and Excel 2021 have RANDARRAY, which fills several cells at once:
=RANDARRAY(10,1,1,100,TRUE)
This gives 10 rows, 1 column, minimum 1, maximum 100, whole numbers. Values can repeat.
Random numbers without repeats
Shuffle a sequence and take the first values. In Microsoft 365:
=INDEX(SORTBY(SEQUENCE(100),RANDARRAY(100)),SEQUENCE(10))
This returns 10 different numbers from 1 to 100. In older versions, put =RAND() beside a list of numbers and sort by that column.
Stop the numbers changing
RAND, RANDBETWEEN and RANDARRAY are volatile: they recalculate whenever the sheet changes, and when you press F9. To keep a set of values, copy the cells and use Paste Special → Values.
Limits to remember
- Spreadsheet random functions are fine for sampling and games but are not meant for passwords or security; see pseudorandom and cryptographically secure randomness.
- You cannot set a seed, so a result cannot be reproduced after it recalculates. What is a random seed? explains why that matters. Working in code instead? See random numbers in Python.
Prefer not to build formulas? Use the random number generator or the random number list generator, then copy the numbers or download them as CSV and paste them into your sheet. For unique numbers use the no-repeats generator, and for values like 0.4271 the random decimal generator.