Excel Random Number Generator: 3 Different Methods

Random numbers are used in Excel for all kinds of tasks like simulations and hypothesis testing. In this article, I’ll walk you through the different ways to generate them. We’ll start with the basics like RAND() and RANDBETWEEN(), then move on to more advanced options like RANDARRAY(). By the end, you’ll know exactly how to use each of these functions and how to choose between them.

Overview of Excel’s Random Number Functions

Excel can generate random numbers as single decimals, whole numbers within a range, or even entire tables of values. Let’s look at three different ways to do so:

RAND() function

The RAND() function gives a random decimal between 0 and 1. It’s an important function, which is why we included in our list of 15 Basic Excel Formulas Everyone Needs to Know.

To generate random decimal numbers between 0 and 1, simply enter =RAND() into one of the cells. If you want more random numbers, drag to apply this formula to additional rows.

Generate random numbers using the RAND() function. Image by Author.

You can also use the RAND() function to generate different types of numbers, such as:

• Random numbers within a specific range: Use this formula: RAND() * N formula. Here, N can be any value. 

• Random number between any two numbers: Use this formula: RAND() * (b - a) + a. Here b is the highest value, and a is the lowest. The result will always be less than the b value.

• Random numbers that are integers instead of decimal numbers: Use the above formulas and then wrap them inside the INT() function.

=RAND()*100

=RAND()*(20-10)+10

=INT(RAND()*(20-10)+10)

Generate random integers with RAND() function. Image by Author.

As a note, you might notice that these numbers update every time I enter new numbers in the sheet. Make sure to use Paste Special > Values if you want to lock the numbers in place (more on this one later).

RANDBETWEEN() function

RANDBETWEEN() generates random integers between the specified range. Its syntax is:

=RANDBETWEEN(bottom, top)

The bottom is the lowest value, and the top is the highest.

For example, imagine I want to generate integers between 10 and 20. I can use the following formula:

=RANDBETWEEN(10,20)

Generate random numbers using RANDBETWEEN(). Image by Author.

Notice how you could get the same result with =INT(RAND()*(20-10)+10). But I prefer RANDBETWEEN() because it is an easier and upgraded version of the RAND() function designed for this kind of job. With RAND(), I have to write a long formula to get a random integer between two values. But with RANDBETWEEN(), I can do it in one simple step.

RANDARRAY() function

RANDARRAY() was introduced in Excel 365. It returns an array of random numbers and allows us to specify the number of values, rows, and columns to fill - all without dragging the formula - and we can choose whether to return decimals or whole numbers.

=RANDARRAY([rows],[columns],[min],[max],[whole_number])

Here:

• rows refers to the number of rows we want to populate.

• columns refers to the number of columns we want to populate.

• min specifies the lowest number.

• max specifies the highest number.

• whole_number takes TRUE for whole numbers and FALSE (or leave blank) for decimals.

Let’s see how it works in real life. Suppose I want to generate random decimal numbers on 4 columns and 6 rows between 10 and 20. In this case, my formula would be:

=RANDARRAY(6,4,10,20)

For the integer numbers, the formula would be:

=RANDARRAY(6,4,10,20,TRUE)

This way, every time I refresh the spreadsheet, new numbers are generated.

Generate random numbers using the RANDARRAY() function. Image by Author.

A quick comparison of all functions 

Here is a quick comparison of all three functions:

Specific Uses for the Excel Random Number Generator 

Let’s look at a few real-life examples of how and where we can use random number generators.

Generate unique random numbers

Sometimes, we want to generate random numbers that don’t repeat. While Excel doesn’t have a direct function for this, we can make a combination of two functions:

1. RAND() with RANK.EQ()

2. RANDARRAY() with UNIQUE() 

Let’s say I need 10 unique random numbers. First, I apply RAND() in cell A2 to generate random numbers and drag them to A10. Then, in cell B2, since I want these numbers as unique values, I enter the following formula:

=RANK.EQ(A2,$A$2:$A$10)

Generate unique random numbers using RANK.EQ(). Image by Author.

Another way is to wrap the RANDARRAY() function inside the UNIQUE() function. Here, I generate 10 unique random numbers between 1 and 20 using this formula:

=UNIQUE(RANDARRAY(10,1,1,20,TRUE))

Generate random numbers with UNIQUE() and RANDARRAY(). Image by Author.

Note: Both UNIQUE() and RANDARRAY() only work in Excel 365 and 2021. Take our Excel Fundamentals track to stay current.

Generate numbers with specific distributions

Excel also provides a way to generate random numbers following a specific distribution, such as a normal distribution, which might be useful in something like a monte carlo simulation. For this, you can use the NORM.INV().

=NORM.INV(probability, mean, standard deviation)

Here: 

• probability is a random decimal between 0 and 1 (RAND()).

• mean is the average value the numbers should center around.

• standard deviation refers to how much the numbers fluctuate up and down from the mean.

For example, I want to create random test scores for a class of students and set the average score to 75. Most students score 10 points (meaning their scores range from 65 to 85). So, I applied the following formula to all rows:

=NORM.INV(RAND(), 75, 10)

Generate normal distribution using NORM.INV() function. Image by Author.

Anonymize and test data

We can even use random numbers to replace sensitive data (like account numbers or phone numbers) for testing purposes or data privacy.

Here, I have a list of account numbers, and I want to anonymize the first four digits of account numbers, so I replace them with general random numbers using this formula:

=REPLACE(A2,1,4,RANDBETWEEN(1,4))

Hide the sensitive data with random numbers. Image by Author.

Make better decisions

It may look surprising, but we can even use random numbers to make quick decisions. For example, if I want to pick a winner from a long list randomly, I can extract a random name from the given range.

Below, I have a list of names in cells A2 to A10 for a giveaway, and I want to select a winner. Now, to do so, I enter the following formula in cell C2, and it randomly chooses a winner:

=INDEX(A2:A10, RANDBETWEEN(1, 11))

Pick a random winner. Image by Author.

How to Prevent Recalculation of Random Numbers

One issue with random numbers is that they change every time the sheet updates. If you want to keep them fixed, convert them to static values. There are two ways to do this:

• Select the range of random numbers, press Ctrl + C to copy them, then right-click on it and choose Paste Special > Values.
• To stop recalculating numbers in one cell, click on the cell and, in the formula bar, press F9.

Understanding Excel’s Random Number Algorithms

In case you are curious about what is happening under the hood of the Excel random number functions, I'll talk a bit about it.

Since Excel 2010, Microsoft Excel has been using the Mersenne Twister algorithm to generate random numbers. It produces high-quality random numbers that pass many statistical tests. One of its most commonly used versions is MT19937, based on a special type of prime number called a Mersenne prime.

Before the Mersenne Twister, the middle-square method was used. It squares a number and treats the middle digit as a random number. While it was a clever idea at that time, developed by the genius John von Neumann, it was discovered to produce patterns and could even get stuck repeating the same numbers. That’s why the Mersenne Twister replaced it.

Final Thoughts 

We’ve gone through different ways to generate random numbers in Excel, from simple decimals with RAND() to whole numbers with RANDBETWEEN() and even full tables of random values with RANDARRAY().

If you want to learn more about handling data in Excel, check out our Data Preparation in Excel and Data Visualization in Excel courses. Also, click below for our Excel Fundamentals track - no prior experience required.