Alok Paul
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:
=RAND()
Step 2:
- Now, press the Enter button.
Step 3:
- Drag the Fill Handle icon towards the last cell.
We get 10 random values here.
1.1 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:
=RAND()*(5-1)+1
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.
1.2 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.
=ROUND(RAND()*(5-(-5))+(-5),0)
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.
Read More: Random Number Generator between Range in Excel (8 Examples)
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.
=RANDBETWEEN(-5,5)
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.
Read More: Random Number Generator with Data Analysis Tool and Functions in Excel
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:
=RANDARRAY(10,4,0,20,FALSE)
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:
=RANDARRAY(10,4,0,20,TRUE)
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.
Read More: How to Generate Random Numbers Without Duplicates in Excel (7 Ways)
Similar Readings
- Auto Generate Invoice Number in Excel (with 4 Quick Steps)
- Generate Random Number with Mean and Standard Deviation in Excel
- Random Number Generator with Normal Distribution in Excel (4 Methods)
- How to Generate Random 10 Digit Number in Excel (6 Methods)
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.
Read More: How to Generate Random Data in Excel (9 Easy Methods)
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
Step 8:
- Again, run the code by pressing F5.
We get random values from Cell B5 to B10.
Read More: Excel VBA: Random Number Generator with No Duplicates (4 Examples)
A Big Issue While Generating Random Number with Formula
There is a big issue with the random number generating functions like RAND, RANDBETWEEN, RANDARRAY. After inserting the random values a big issue we notice. The random values are changing every time execute the formula. As a result, when we use those values as a reference to other functions, the results of those functions are also changed. For that, we need to stable the random values. Follow the steps below to apply perform this.
Step 1:
- Select all the cells first.
- Place the cursor on the edge as pointed.
Step 2:
- Keep pressing the mouse button. Move the cursor to the adjacent column and back the cursor to our data column.
- Leave the mouse button.
- From the list choose Copy Here as Values Only option.
Step 3:
- Enter any of the cells of the data column. Now, see the formula bar.
No formula is shown on the bar. Also, values will not change.
💬 Things to Remember
✎ If we need only integer random values use the RANDBETWEEN function.
✎ The RANDARRAY function can generate both decimal and integer random values.
✎ The ROUND function helps get random values with specified decimal digits.
Conclusion
In this article, we described how to generate the random number in Excel by applying the formula. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
