5 Ways to Generate Random Numbers in Excel

Step-by-step

Random numbers are handy for building practice data sets, test data or simulations. Here are five ways to make them, from a single decimal to a whole normally distributed sample.

1. RAND: a random decimal

=RAND()

Returns a random decimal between 0 and 1. If the cell is in a table, Excel fills the whole column. The numbers are “live”: they change every time the sheet recalculates (for example, when you press Enter anywhere).

Freeze the numbers

When you’re happy with a set, select it, copy (Ctrl+C), then right-click > Paste Special > Values on the same cells. They become fixed numbers. Use this step with every method below.

2. RANDBETWEEN: random whole numbers

For invoice numbers between 1,000 and 3,500:

=RANDBETWEEN(1000,3500)

For sale amounts between 0 and 1,500, use =RANDBETWEEN(0,1500) and format the column as currency.

3. RAND plus RANDBETWEEN: amounts with cents

Add the two together to get a whole-number part plus a decimal part, then round to two decimal places so it looks like real money:

=ROUND(RANDBETWEEN(100,5000)+RAND(),2)

4. Normally distributed numbers with the Analysis ToolPak

To make realistic data such as exam scores that cluster around an average, turn on the ToolPak: File > Options > Add-ins > Excel Add-ins > Go, tick Analysis ToolPak. Then go to Data > Data Analysis > Random Number Generation. Choose 1 variable, 100 numbers, distribution Normal, mean 75 and standard deviation 15, and pick an output cell such as B4.

5. RANDARRAY: a whole block at once

RANDARRAY (Excel 365) fills rows and columns in one go. Its arguments are rows, columns, minimum, maximum and whole numbers (TRUE) or decimals (FALSE). For 50 rows by 7 columns of whole numbers from 25 to 100:

=RANDARRAY(50,7,25,100,TRUE)

For 100 rows of decimals between 0 and 1,000, use =RANDARRAY(100,7,0,1000,FALSE).

Go further

My Excel Statistics & Data Analysis: Zero to Analyst course goes much further into distributions, sampling and analysis.