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:

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

**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.

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

**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.