Whether you need to generate dummy data for a software presentation, simulate dice rolls for a statistics class, or pick a random winner for a company raffle, Microsoft Excel has built-in mathematical functions to generate random numbers instantly.
There are two primary formulas you can use, depending on what kind of numbers you need.
The RAND Function (For Decimals)
The RAND function is the foundational random number generator in Excel. It requires absolutely no arguments between the parentheses.
Syntax: =RAND()
When you type this into a cell and hit Enter, Excel will generate a random decimal number between 0 and 1.
For example, it might output: 0.4839218
Because it outputs a fraction, it is highly useful in advanced statistical modeling and probability calculations. However, for everyday office tasks, returning a massive decimal string is usually not very helpful.
The RANDBETWEEN Function (For Whole Numbers)
If you want to pick a random employee ID number between 1 and 500, or simulate a dice roll between 1 and 6, you should use the RANDBETWEEN function. This function allows you to set a specific floor and ceiling, and it will only output clean, whole integers.
Syntax: =RANDBETWEEN(bottom, top)
Example 1: A Dice Roll
Type =RANDBETWEEN(1, 6). Excel will randomly output 1, 2, 3, 4, 5, or 6.
Example 2: A Raffle Winner
If you have 500 customers listed in rows 1 through 500, type =RANDBETWEEN(1, 500). Excel might output 342, telling you that the customer in row 342 is the winner.
The Volatility Problem (Important Warning)
Both RAND and RANDBETWEEN are what Excel calls “Volatile Functions.”
This means they do not calculate once and stop. Every single time you type anything into any cell in the entire workbook, or every time you save the file, the functions will instantly recalculate and generate brand new random numbers.
If you use RANDBETWEEN to pick a raffle winner (342) and then you type “Winner” in the next column, the formula will instantly recalculate and the number will change to 114, completely ruining your result.
How to freeze the numbers:
- Generate your random numbers using the formula.
- Highlight the cells containing the numbers and press Ctrl+C (or Cmd+C on Mac) to Copy them.
- Right-click on the exact same cells and look at the “Paste Options”.
- Click on the icon that says 123 (Paste Values).
This deletes the underlying RAND formula and permanently locks in the random number that was generated, preventing it from ever changing again.