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:theRANDBETWEENis 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 theparameters accordingly.(A1,$A$1:$A$10)

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:theRANDBETWEENis 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 theparameters accordingly.(A1,$A$1:$A$10)

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]