How to Create a Random Number Generator in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

You might be surprised at how often you need to generate random numbers in Excel.

Maybe it’s for an experiment or some type of hypothetical calculation. Or for whipping up a spreadsheet full of mock data.

No matter the reason, Excel has you covered with two different functions that’ll give you random numbers.

Both RAND and RANDBETWEEN have their unique uses, and we’ll go over both of them here.

Let’s get started!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

An important note

Before we get into how to generate random numbers in Excel, there’s an important fact about these functions to take note of:

A new random number is returned every time the worksheet is recalculated

This means that whenever you type in new data, your random numbers will change. Here’s what that looks like:

automatic-recalculation

You can disable automatic recalculation, but I wouldn’t recommend it, because that will wreak havoc with your other formulas. If you want to do it, though, head to File > Options > Formulas and change Workbook Calculation to Manual.

automatic-recalculation

There might be some times when you want to do this. But there’s a better way that we’ll go over momentarily.

Kasper Langmann, Co-founder of Spreadsheeto

Remember this automatic recalculation when you see your numbers changing—it can be a bit surprising at first!

Get your FREE exercise file

It’s easy to play around with random number generation in your own Excel spreadsheet.

But if you want to follow along on the exact sheets that we’re working with, you can grab the free exercise file below.

Download the FREE Exercise File

Download exercise file
Download free exercise file

Getting random numbers with RAND

The RAND function is very simple. It doesn’t even have any arguments!

Here’s the syntax:

The syntax of the RAND function

=RAND()

Note: there are no arguments, but you still need to type the parentheses!

This function returns a random number (technically a pseudo-random number) that’s greater than or equal to 0 and less than 1. (So you might get 0 from this function, but you’ll never get 1.)

It doesn’t get much simpler than that.

Want to try it out?

Open up the example workbook, click into cell A2, and type the formula =RAND().

Hit Enter, and you’ll get a random number. My function returned 0.079671543, but yours will return something different:

excel-random-number

Drag the cell down using the fill handle and you’ll get a longer series of random numbers between 0 and 1.

more-random-numbers

Once you’ve gotten your string of random numbers, try typing something in a different cell. You’ll see all the numbers recalculate.

That can be annoying, especially if you’re trying to work with the same data over time. Let’s take a look at how to fix this problem.

Pro tip: generate a lot of random numbers quickly

Using the fill handle is a pretty fast way of getting random numbers into a lot of cells. But there’s a faster way.

Just highlight all the cells you want to contain random numbers, type =RAND(), and press Ctrl + Enter.

(This also works for the RANDBETWEEN function.)

Stopping random numbers from recalculating

There’s a simple solution to keep your random numbers from recalculating all the time. And it doesn’t involve manually updating all the formulas in your workbook manually.

Instead of pressing Enter to calculate your random number, press F9 instead. That will calculate the formula and store the result as a value.

Here are two random numbers, one created by pressing Enter, and another by pressing F9:

random-number-f9

This method only works with a single cell. To keep a range of random numbers from recalculating, your best bet is to copy them and paste the values (not the formulas) into a new set of cells.

Kasper Langmann, Co-founder of Spreadsheeto

This method also works with RANDBETWEEN, which we’ll take a look at now.

Selecting range with RANDBETWEEN

RAND is great, but what if you don’t want a random number between 0 and 1?

You could multiply the random numbers by a set value, but there’s an easier way: RANDBETWEEN.

The syntax of the RANDBETWEEN function

=RANDBETWEEN(bottom, top)

The bottom argument is the smallest integer that the function will return.

The top argument is the largest integer that the function will return.

Running this function will give you a random integer (no decimals) between these two numbers. Keep in mind that both of the numbers listed could show up.

Let’s get a number between 1 and 100 with this function:

=RANDBETWEEN(1, 100)

randbetween-function

After hitting Enter, I get 67. You’ll get something different.

randbetween-number

Like RAND, RANDBETWEEN recalculates whenever you enter a new value into the spreadsheet.

You can get around this by pressing F9 instead of Enter, or by copying and pasting a series of RANDBETWEEN values.

Pro tip: generate random decimals between two numbers

Want to generate random non-whole numbers with RANDBETWEEN? Use RAND in one column and RANDBETWEEN in the next.

Then use the SUM function to add them together.

Wrapping things up…

Generating random numbers in Excel is very easy with the RAND function, and RANDBETWEEN gives you even more options. Using F9 to get non-updating numbers and Ctrl + Enter to generate many numbers at once just top it off.

So next time you need a lot of random numbers, instead of heading to a website or another tool, fire up Excel!