How to Use Random Number Generator in Excel (5 Quick Ways)

You can use the random number generator Excel to generate random numbers. A random number generator has a huge use when working with statistical analysis or a financial model, or to select random winners from a list, etc. Whatever your need for random numbers, Excel has different approaches to creating them. In this article, you will learn 5 quick ways to use random number generators in Excel.


Download Practice Workbook

Download the following Excel file for your practice.


5 Quick Ways to Use Random Number Generator in Excel

We have a blank column that contains 10 cells with headings in an Excel worksheet. Our goal is to generate random numbers using several functions and tools.

1. Use the Excel RAND Function as Random Number Generator

The RAND function does not require any arguments. The function generates a random decimal number greater than or equal to 0 and less than 1 and the numbers change on recalculation.

To do this, just follow the steps below.

Steps:

  • First of all, select cell B5 > type =RAND() > press ENTER.
=RAND()

Use Random Number Generator in Excel with RAND Function

  • To generate a list of random numbers, drag the Fill Handle.

Finally, here is the result.

Use Random Number Generator in Excel with RAND Function (Result)

Remarks:

  • Using the RAND function to generate random numbers between any two numbers ( In this example, we need random numbers between 10 and 100).

Type the following formula in cell B5.

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

Use Random Number Generator in Excel with RAND Function (Between two numbers)

Drag the Fill Handle to the required cells.

Here is the result,

  • Using the RAND function to generate random integer numbers between any two numbers ( In this example, we need random integer numbers between 10 and 100).

Type the following formula in cell B5.

=ROUND(RAND()*(100-10)+10,0)

This is the same formula as the RAND function to generate numbers between two numbers but uses the ROUND function to round to zero decimal places.

Use Random Number Generator in Excel with RAND Function (Integer)

Drag the Fill Handle to the required cells.


2. Use the RANDBETWEEN Function as Random Number Generator

The RANDBETWEEN function generates a random integer number between two numbers that you specify. In this example, we need random integer numbers between 50 and 100. Just follow the steps below to learn this function.

Steps:

  • Firstly select cell B5 > type =RANDBETWEEN(50,100) > press ENTER.
=RANDBETWEEN(50,100)

Use Random Number Generator in Excel with RANDBETWEEN Function

  • Drag the Fill Handle to the required cells.

The following image shows the output.

Use Random Number Generator in Excel with RANDBETWEEN Function (Result)


3. Use the RANDARRAY Function

If you have the most updated version of Excel 365 or Excel 2021, you can use the RANDARRAY function. In this example, we need random numbers consisting of 6 rows and 2 columns. To do this, just follow the steps below.

Steps:

  • Select cell B5 > type =RANDARRAY(6,2) > press ENTER.
=RANDARRAY(6,2)

See the image below.

Use Random Number Generator in Excel with RANDARRAY Function (Result)

Now, if you want to generate an array of integers, 6 rows by 2 columns, between 10 to 50, just type the following formula in cell B5.

=RANDARRAY(6,2,10,50,TRUE)

Use Random Number Generator in Excel with RANDARRAY Function (integer)

Finally, here is the result.

Use Random Number Generator in Excel with RANDARRAY Function (Result)

Note:

The Boolean TRUE asks the RANDARRAY function to give an array of integer numbers. Put FALSE if you want to get a decimal numbers array.


Similar Readings


4. Generate Random Numbers in Excel Using the Analysis ToolPak

The Analysis ToolPak in Excel provides a Random Number Generation tool. The tool is more flexible than the functions we discussed above. Before using this tool, you need to install it. Just follow the steps below.

Steps:

  • First, go to the File tab.

Generate Random Numbers in Excel Using the Analysis Tools Add-In

  • Click Options. Or press Alt+F+T to open the Options. A window will pop up.

  • In the left-hand pane of this window click on Add-Ins. In the manage box, select Excel Add-ins. And click on the Go button.

Generate Random Numbers in Excel Using the Analysis Tools Add-In

  • A window containing all available add-ins will pop up. Select the Analysis ToolPak and click OK.

Generate Random Numbers in Excel Using the Analysis Tools Add-In

  • Under the Data tab on the Excel ribbon, you can see a new group called Analysis with one option called Data Analysis.

Generate Random Numbers in Excel Using the Analysis Tools Add-In

  • Now, click Data Analysis. A Data Analysis window will pop up. Select the Random Number Generation option and click OK.

  • A new window will pop up where you can enter your parameters to create the random numbers.

Generate Random Numbers in Excel Using the Analysis Tools Add-In (Dialog Box)

There are a few settings that can be customized. The settings are discussed below.

  • The Number of Variables indicates the number of columns of random numbers that you want in your worksheet.
  • The Number of Random Numbers refers to the number of rows that you want in your worksheet.
  • Distribution means which kinds of distribution of random numbers you want. In this example, we choose Uniform.
  • Parameters indicate the boundaries of your distribution. In this example, 10 to 50.
  • Output Range means the range of the table of random numbers. You can simply enter the upper-left cell (In this example, B5).

Finally, here is the result,

Generate Random Numbers in Excel Using the Analysis Tools Add-In (Result)

Read More: Random Number Generator with Data Analysis Tool and Functions in Excel


5. Generate Random Numbers with a VBA Code

VBA is the programming language that can be used to generate random numbers. But, it is more complicated than using a formula in a cell in Excel. Follow the steps below to use VBA in generating random numbers.

Steps:

  • Press Alt+F11 or Go to the Developer tab > Visual Basic. A window will pop up.  Under this window, click on the Insert tab > select the Module option from the drop-down. A module window will pop up.

Generate Random Numbers with VBA

  • Type the following code on the module window.
Sub RandomNumb()
MsgBox Rnd()
End Sub

Generate Random Numbers with VBA (Code)

  • Press F5 to run this code. A message will pop up with a random number displayed. Press OK. It will return to the previous code window. Run the code again and a new random will be shown. The random numbers are between 0 and 1.

Remark:

you can also use the Rnd function with a parameter that sets the starting point of the algorithm to generate random numbers. Just type the following code in the module window.

Sub RandomNumbRounded()
MsgBox Round((Rnd() * 30) + 20,0)
End Sub

Generate Random Numbers with VBA (Integer)

In this example, the code will generate random integers between 20 and 50. The code multiplies the random number by 30 (=50-20) and then adds 20 to it and finally, rounds to zero decimal places.

Read More: Excel VBA: Random Number Generator with No Duplicates (4 Examples)


Conclusion

In this tutorial, I have discussed 5 quick ways to use the random number generator Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo