Guide

How to Generate Random Numbers in Excel

Updated · 2 min read

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

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.

Try the tools

More guides