How To Generate Random Numbers In Excel

To generate random numbers in Excel is a simple, straightforward process done in a minute. All are is required to learn how is three formulas, a few clicks, and a few minutes of your time. 

Generating a set of random numbers can be useful in many scenarios, such as when we want to generate a table of random values to conduct a simple random sample – for instance. 

This tutorial will teach you how to generate a table of random numbers and unique random numbers in Microsoft® Excel

The good part is that once you know how to generate random numbers in Excel, you can use the same workflow and formulae to generate random sets of values on other platforms such as Google® Spreadsheets.

Without further ado, let’s get started.

Excel Functions for Generating Random Numbers

There are two functions dedicated to generating random samples in Excel: RANDBETWEEN and RAND. Additionally, we can use the RANK function to obtain integers from decimal values. You can see why and how in a moment.

The RANDBETWEEN function will generate random numbers between a user-specified range, but some are likely to be repeated.  

The RAND function will likely generate numbers without repetition. However, the RAND function can only generate random numbers between 0 and 1. To deal with this limitation, we can use RAND in combination with the RANK function to generate unique random numbers in Excel.    

Generate Random Numbers With RANDBETWEEN function

The RANDBETWEEN function in Excel generates a set of integer random numbers between two specified numbers.

The RANDBETWEEN function supports two arguments [values] to define the range of the random numbers to be generated. The bottom and top values are included in parentheses and separated by a comma and space. 

Let’s assume we want to generate a set of random numbers between 10000 and 99999

To do so, simply select a cell in Excel, and in the active cell type, the following formula then presses the ENTER key. 

Alternatively, you can type the formula in the function FX field in Excel.

=RANDBETWEEN(10000, 100000)

NOTE: You can use any bottom and upper values for RANDBETWEEN, e.g., (1, 10); (100, 500), etc.

The RANDBETWEEN function will generate a number like this:

How To Generate Random Numbers In Excel - RANDBETWEEN generates one number. Source: researchcup.com

Let’s assume we need to generate a table of 50 random numbers with RANDBETWEEN. This means we need a table of 5 columns and 10 rows in Excel. 

Select the active cell with the randomly generated number, click and hold on the small square on the bottom-right of the selection, then drag the selection horizontally for another four cells. 

How To Generate Random Numbers In Excel - RANDBETWEEN generates multiple numbers. Source: researchcup.com

NOTE:  the RANDBETWEEN is a volatile function and will generate new random numbers every time you change your random number selection. 

Here is how my result looks like:

How To Generate Random Numbers In Excel - RANDBETWEEN generates multiple numbers result. Source: researchcup.com

Now extend your selection to cover ten rows. 

How To Generate Random Numbers In Excel - RANDBETWEEN generates 50 numbers table. Source: researchcup.com

There you go. You just generated a table of random numbers between 10000 and 100000 in Excel.  

Generate Unique Random Numbers with RAND and RANK Functions

Let’s assume you want to generate 10 unique random numbers in Excel. Simply select a cell and type the following formula in the active cell or FX field. 

=RAND()

How To Generate Random Numbers In Excel - RAND generates a random value. Source: researchcup.com

Next, select the cell with the generated random number and expand the selection to cover 10 rows. 

How To Generate Random Numbers In Excel - RAND generates random values. Source: researchcup.com

As you see, we generated a set of random numbers but the values are decimal numbers. 

To convert the decimal values to integers, we need to use the RANK function in Excel.

With the random decimal number cells selected, type the following formula in the FX field and push the ENTER key:

=RANK.EQ(A1,$A$1:$A$10)

NOTE: make sure there are no spaces before the “=” sign when you paste the formula. If you are using different columns and rows, make sure you adjust the (A1,$A$1:$A$10) parameters accordingly. 

How To Generate Random Numbers In Excel - RANK converts RAND value to an integer. Source: researchcup.com

The RANK formula generated a new number, as seen in the figure below.

Next, select the cell of the newly generated number and drag it until row 10.

How To Generate Random Numbers In Excel - RANK converts RAND values to integers. Source: researchcup.com

Here you go: a new set of unique randomly generated numbers has been generated in column B, as seen below:

How To Generate Random Numbers In Excel - RANK converts all RAND values to integers. Source: researchcup.com

I performed many tests with larger sets of numbers to see if any number repetitions occur. So far, all the random numbers generated with RAND and RANK were unique. 

However, if you decide to use this method, I advise you to double-check for repetitions before using the number set in your project. Here is a great tutorial to check if your number set contains any repetitions – just in case.

Excel Functions for Generating Random Numbers

There are two functions dedicated to generating random samples in Excel: RANDBETWEEN and RAND. Additionally, we can use the RANK function to obtain integers from decimal values. You can see why and how in a moment.

The RANDBETWEEN function will generate random numbers between a user-specified range, but some are likely to be repeated.  

The RAND function will likely generate numbers without repetition. However, the RAND function can only generate random numbers between 0 and 1. To deal with this limitation, we can use RAND in combination with the RANK function to generate unique random numbers in Excel.    

Generate Random Numbers With RANDBETWEEN function

The RANDBETWEEN function in Excel generates a set of integer random numbers between two specified numbers.

The RANDBETWEEN function supports two arguments [values] to define the range of the random numbers to be generated. The bottom and top values are included in parentheses and separated by a comma and space. 

Let’s assume we want to generate a set of random numbers between 10000 and 99999

To do so, simply select a cell in Excel, and in the active cell type, the following formula then presses the ENTER key. 

Alternatively, you can type the formula in the function FX field in Excel.

=RANDBETWEEN(10000, 100000)

NOTE: You can use any bottom and upper values for RANDBETWEEN, e.g., (1, 10); (100, 500), etc.

The RANDBETWEEN function will generate a number like this:

How To Generate Random Numbers In Excel - RANDBETWEEN generates one number. Source: researchcup.com

Let’s assume we need to generate a table of 50 random numbers with RANDBETWEEN. This means we need a table of 5 columns and 10 rows in Excel. 

Select the active cell with the randomly generated number, click and hold on the small square on the bottom-right of the selection, then drag the selection horizontally for another four cells. 

How To Generate Random Numbers In Excel - RANDBETWEEN generates multiple numbers. Source: researchcup.com

NOTE:  the RANDBETWEEN is a volatile function and will generate new random numbers every time you change your random number selection. 

Here is how my result looks like:

How To Generate Random Numbers In Excel - RANDBETWEEN generates multiple numbers result. Source: researchcup.com

Now extend your selection to cover ten rows. 

How To Generate Random Numbers In Excel - RANDBETWEEN generates 50 numbers table. Source: researchcup.com

There you go. You just generated a table of random numbers between 10000 and 100000 in Excel.  

Generate Unique Random Numbers with RAND and RANK Functions

Let’s assume you want to generate 10 unique random numbers in Excel. Simply select a cell and type the following formula in the active cell or FX field. 

=RAND()

How To Generate Random Numbers In Excel - RAND generates a random value. Source: researchcup.com

Next, select the cell with the generated random number and expand the selection to cover 10 rows. 

How To Generate Random Numbers In Excel - RAND generates random values. Source: researchcup.com

As you see, we generated a set of random numbers but the values are decimal numbers. 

To convert the decimal values to integers, we need to use the RANK function in Excel.

With the random decimal number cells selected, type the following formula in the FX field and push the ENTER key:

=RANK.EQ(A1,$A$1:$A$10)

NOTE: make sure there are no spaces before the “=” sign when you paste the formula. If you are using different columns and rows, make sure you adjust the (A1,$A$1:$A$10) parameters accordingly. 

How To Generate Random Numbers In Excel - RANK converts RAND value to an integer. Source: researchcup.com

The RANK formula generated a new number, as seen in the figure below.

Next, select the cell of the newly generated number and drag it until row 10.

How To Generate Random Numbers In Excel - RANK converts RAND values to integers. Source: researchcup.com

Here you go: a new set of unique randomly generated numbers has been generated in column B, as seen below:

How To Generate Random Numbers In Excel - RANK converts all RAND values to integers. Source: researchcup.com

I performed many tests with larger sets of numbers to see if any number repetitions occur. So far, all the random numbers generated with RAND and RANK were unique. 

However, if you decide to use this method, I advise you to double-check for repetitions before using the number set in your project. Here is a great tutorial to check if your number set contains any repetitions – just in case.

Conclusion

In this tutorial, we learned how to generate a set of random numbers using RANDBETWEEN, RAND, and RANK functions in Excel. 

I hope you found this article useful. If so, take a few seconds to share it with your colleagues and friends.

Cite this article in your research paper:

Uedufy (November 29, 2022) How To Generate Random Numbers In Excel. Retrieved from https://uedufy.com/how-to-generate-random-numbers-in-excel/.
"How To Generate Random Numbers In Excel." Uedufy - November 29, 2022, https://uedufy.com/how-to-generate-random-numbers-in-excel/
Uedufy December 14, 2021 How To Generate Random Numbers In Excel., viewed November 29, 2022,<https://uedufy.com/how-to-generate-random-numbers-in-excel/>
Uedufy - How To Generate Random Numbers In Excel. [Internet]. [Accessed November 29, 2022]. Available from: https://uedufy.com/how-to-generate-random-numbers-in-excel/
"How To Generate Random Numbers In Excel." Uedufy - Accessed November 29, 2022. https://uedufy.com/how-to-generate-random-numbers-in-excel/
"How To Generate Random Numbers In Excel." Uedufy [Online]. Available: https://uedufy.com/how-to-generate-random-numbers-in-excel/. [Accessed: November 29, 2022]
Leonard Cucos
Leonard Cucos

Leonard is a Ph.D. student in Data Science and holds an MBA and B.Sc. He has an impressive public speaking profile on education, engineering, and research. He loves to help students achieve their academic objectives and believes education is the key to building a better future for mankind.

Articles: 26