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:
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.
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:
Now extend your selection to cover ten rows.
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()
Next, select the cell with the generated random number and expand the selection to cover 10 rows.
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.
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.
Here you go: a new set of unique randomly generated numbers has been generated in column B, as seen below:
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:
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.
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:
Now extend your selection to cover ten rows.
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()
Next, select the cell with the generated random number and expand the selection to cover 10 rows.
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.
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.
Here you go: a new set of unique randomly generated numbers has been generated in column B, as seen below:
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:
[citationic]