Excel Formula to Generate Random Number (5 examples)

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()

Use RAND Function to Insert Random Number in Excel

Step 2:

  • Now, press the Enter¬†button.

Step 3:

  • Drag the Fill Handle icon towards the last cell.

Use RAND Function to Insert Random Number in Excel

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:

=RAND()*(B-A)+A

Step 1:

  • Now, go to Cell B5.
  • Put the formula below:
=RAND()*(5-1)+1

Use RAND Function to Insert Random Number in Excel

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.

=ROUND(RAND()*(B-A)+A,0)

Step 1:

  • Enter Cell B5.
  • Write down the following formula.
=ROUND(RAND()*(5-(-5))+(-5),0)

Use RAND Function to Insert Random Number in Excel

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)

RANDBETWEEN Function to Insert Random Number

Step 2:

  • Now, press Enter.

Step 3:

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

RANDBETWEEN Function to Insert Random Number

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.

Create Random Numbers Using RANDARRAY Formula

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)

Create Random Numbers Using RANDARRAY Formula

Step 3:

  • Then press Enter.

Create Random Numbers Using RANDARRAY Formula

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)

Create Random Numbers Using RANDARRAY Formula

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


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.

Add Analysis ToolPak to Generate Random Number

Step 4:

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

Add Analysis ToolPak to Generate Random Number

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.

Add Analysis ToolPak to Generate Random Number

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.

Add Analysis ToolPak to Generate Random Number

Step 9:

  • Finally, press OK.

Add Analysis ToolPak to Generate Random Number

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.

Excel VBA Macro to Generate Random Number

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

Excel VBA Macro to Generate Random Number

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

Excel VBA Macro to Generate Random Number

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

Excel VBA Macro to Generate Random Number

Step 8:

  • Again, run the code by pressing F5.

Excel VBA Macro to Generate Random Number

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.

Issue With the Random Value Generating Formula

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.

Issue With the Random Value Generating Formula

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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo