A Random number is not necessary for all users of Microsoft Excel. Most of the users use fixed numbers to perform any operation in Excel. But when we need to work with large files, we do not have any sample data. Then we must take the help of random numbers. We will illustrate how to generate a random number using the Excel formula in this article.

---

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

---

5 Methods to Generate Random Number in Excel Formula

We will explain 5 methods to generate a random number using a formula in Excel.

---

1. Use RAND Function to Insert Random Number in Excel

The RAND function is a simple function, that generates random numbers between 0 and 1.

No arguments need to apply the RAND function.

Here, we will show the use of the RAND function to generate random numbers.

Step 1:

• Go to Cell B5.
• Write the formula below:

Step 2:

• Now, press the Enter button.

Step 3:

• Drag the Fill Handle icon towards the last cell.

We get 10 random values here.

---

A. Generate Random Number Within a Range

The RAND function generates the above random numbers between 0 to 1. But we want to generate within a range. That is also possible with this RAND function.

The format will be like this:

Step 1:

• Now, go to Cell B5.
• Put the formula below:

Step 2:

• Again, press Enter.
• Drag the Fill Handle icon to the last cell to get value for all the cells.

Here, you can see that the random values are between 1 to 5. But all are fractional values.

---

B. Generate Random Integer Numbers Within a Range

We already generated random numbers using the RAND function. Also noticed that all random numbers are fractional. Now, we will generate random integer numbers between -5 to 5.

We will insert the ROUND function with the RAND function.

The ROUND function takes a number round-up that number to a certain amount of digits.

Step 1:

• Enter Cell B5.
• Write down the following formula.

Step 2:

• Press the Enter button and pull the Fill Handle icon to the last cell.

All the random numbers are integer and between -5 to 5.

---

2. Use RANDBETWEEN Function to Insert Random Number

The RANDBETWEEN function generates random numbers between two integers. It only generates integers, no fractional values.

In the formula, we will put a bottom and top value. And, the function will generate random values between these two values.

Step 1:

• Go to Cell B5 first.
• Put the following formula on that cell.

Step 2:

• Now, press Enter.

Step 3:

• Pull the Fill handle icon to get values for the rest of the cells.

We get all the random values between -5 to 5 and all are integers.

---

3. Create Random Numbers Using RANDARRAY Function

The RANDARRAY function generates random numbers with an array. This function is only available in Excel 365 and Excel 2021.

In the formula, we input the number of rows and columns and maximum and minimum limits of values. We can input both decimal and integer values using this array function.

We will apply this function with the array below. It has 10 rows and 4 columns. Our desired random values are between 0 to 20.

Step 1:

• Go to Cell B5.
• When we put the RANDARRAY formula, we get two options in the last argument.

TRUE and FALSE  are for integer and decimal correspondingly.

Step 2:

• Now, put the complete formula for decimal values. The formula is:

Step 3:

• Then press Enter.

All the values are in decimal.

Step 4:

• Now, modify the formula for integer values. The modified formula will look like this:

Step 5:

• Again, press the Enter button.

Now, all values of the array are integers. So, using the RANDARRAY function, we have the opportunity to generate both decimal and integer random numbers.

Note:

Our selected array must be blank. This function can not overwrite any data. If any data is found on any cell of the array, the #SPILL error will show.

---

4. Add Analysis ToolPak to Generate Random Number

Analysis ToolPak is an Add-ins of Microsoft Excel. It lessens our time and steps when working with sophisticated statistical or engineering analyses. Here, we discuss in detail how to apply Analysis ToolPak and generate random numbers applying it.

Using this ToolPak we can generate random numbers in a cell or an array according to our need. Here, we will generate random numbers to an array of 6 rows and 4 columns.

Step 1:

• First, go to the File tab.

Step 2:

• Select Options from the menu.

Step 3:

• In the Excel Options window, choose Add-ins from the left side.
• Now, press Go of the Excel Add-ins.

Step 4:

• The Add-ins window will appear. Tick on Analysis ToolPak from the Add-ins available list.
• Then press OK.

Step 5:

• Now, go to the Data tab.
• Select Data Analysis from the Analysis group.

Step 6:

• Select the Random Number Generation option from the Data Analysis window.
• Then click on OK.

Step 7:

• Random Number Generation window will appear.

Step 8:

• Put values in different argument boxes.
• The Number of Variables indicates the number of columns we want to input random values.
• The Number of Random Numbers indicates the number of rows.
• We choose Uniform Distribution.
• Set the Parameters between 11 and 20.
• Set the Output Range as the starting of the array.

Step 9:

• Finally, press OK.

This is our random data array applying the Analysis ToolPak.

Step 10:

• We can also choose different Distribution methods from the list.

---

5. Excel VBA Macro to Generate Random Number

We can apply VBA Macro code to generate random numbers.

Step 1:

• Go to the Developer tab.
• Click on the Record Macro option.
• Put Generate_Random in the Macro name box.
• Then press OK.

Step 2:

• Now, select Macros.
• Choose Generate_Random macro and then Step Into it.

Step 3:

• Now, put the following code in the command module.

Sub Generate_Random()
MsgBox Rnd()
End Sub

Step 4:

• Run the code by pressing F5.

A random value is shown in the display. This is a decimal value.

But we want to generate integer random values.

Step 5:

• To generate integer values, we use the round function in the VBA So, the modified VBA code is:

Sub Generate_Random_2()
MsgBox Round((Rnd() * 5 - 1) + 1)
End Sub

Step 6:

• Again, run the code by pressing F5.

Now, we will generate the random values in the present field.

Step 7:

• Put the code below in the command module.

Sub Generate_Random_3()
Dim RDM As Integer
For RDM = 5 To 10
ActiveSheet.Cells(RDM, 2) = Round((Rnd() * 10 - 1) + 1, 0)
Next RDM
End Sub