In this article, I will discuss the random number generator between a range in Excel. Often, while doing statistical and financial analysis, you might have to use a random number generator. Whatever the purpose is, Excel has several ways to generate random numbers. Letâ€™s have a look at those ways.

**Table of Contents**Expand

## Random Number Generator between Range in Excel: 8 Suitable Examples

### 1. Use Excel RAND Function to Generate Number between a Range

You can use **the RAND function **as a random number generator. Usually, this function creates random numbers between **0** to** 1**.

**Steps:**

- First. write the below formula in
**Cell B5**. Hit**Enter**. As expected, you will get a number between**0**to**1**.

`=RAND()`

- Now, drag the
**Fill Handle**(**+)**tool to get a list of numbers between the**RAND**functionâ€™s range.

- Finally, here is the list of numbers.

- Besides, you can set the range of the random numbers using the
**RAND**For example, I want to get numbers between**0**and**6**. Then type the below formula in**Cell B5**and press**Enter**.

`=RAND()*5+1`

- As before, drag down the
**Fill Handle**(**+**) and get the below result.

**ðŸ“Œ Convert Formula Results to Values:**

Now, there is a problem with the above formula. The **RAND** function is a Volatile Function. The numbers we get from the function will continuously change on recalculation. So, to avoid that change we have to convert the result of the above formula to values. To do that, follow the below steps.

**Steps: **

- First, select the resulting list we have got, and press
**Ctrl + C**.

- Next, from
**Excel Ribbon**, go to**Home**>**Paste**. Now click on the**Paste Values**icon (See the screenshot).

- As a result, we got the numbers as values below. Now, these values wonâ€™t change on recalculation.

### 2. Apply RANDBETWEEN Function as Random Number Generator in a Range

Letâ€™s use **the RANDBETWEEN function** to get a list of random numbers. Using this function, you can specify the top and bottom numbers of your range. For example, we want to have random numbers between **10** and** 50**.

**Steps:**

- Type the below formula in
**Cell B5**. Consequently, we will get the following result after hitting**Enter**.

`=RANDBETWEEN(10,50)`

- Upon using the
**Fill Handle**tool, the following is our list of random numbers.

Similar to the **RAND** function, if necessary, make sure you convert the result of the **RANDBETWEEN** formula to values. This is because the **RANDBETWEEN** function is also a Volatile Function in Excel.

### 3. Use RANK.EQ and RAND Functions as Unique Number Generator between a Range

Usually, the **RAND** function returns unique numbers between a range. Still, to check the repetition of resulting random numbers, we can use the** RANK.EQ **function.

**Steps:**

- First, get a random number list using the
**RANDÂ**function.

- Then convert the list to values using the
**Paste Values**option (Described in**Method 1**). - Now, type the below formula in
**Cell C5**.

`=RANK.EQ(B5,$B$5:$B$13)`

- Press
**Enter**.

- Now if you put any duplicate values in
**Column B**,**Column C**will indicate it by showing duplicate integers to the corresponding**RANDÂ**values.

### 4. Insert RANDARRAY Function as Random Number Generator in Excel

In **Excel 365**, we can use the** RANDARRAY **function as a random number generator. Syntax of the **RANDARRAY** function is mentioned below for your better understanding.

**RANDARRAY([rows],[columns],[min],[max],[whole_number])**

Suppose, you want to create a random number array between the range of** 10 **and **20**, containing** 5** rows and** 2 **columns, and I want to have whole numbers, then follow the below procedure.

**Steps:**

- Type the below formula in
**Cell B5**. Press**Enter**and you will get an array (outlined as blue color) containing expected random numbers.

`=RANDARRAY(5,2,10,20,TRUE)`

### 5. Excel ROUND and RAND Functions Combination as Random Number Generator in a Range

Now I will use **the ROUND function** along with the** RAND **function to get a random number list between **0** and** 20**.

**Steps:**

- First, write the following formula in
**Cell B5**and hit**Enter**. As a consequence, you will get the list of random numbers within the specified range.

`=ROUND(RAND()*19+1,0)`

Here, the result of the **RAND **formula is multiplied by **19** and then **1** adds to it. Later, the **ROUND** function will round the decimal number to** 0** decimal places.

### 6. Use Analysis Toolpak Add in to Generate Random Numbers between a Range

We will use Excel **add-ins**Â to generate a random number list. Follow the below steps to do the task.

**Steps:**

- First, go to the
**File**tab from**Excel Ribbon**.

- Next, go to the
**Options**.

- Then
**Excel Options**window will show up. Go to the**Add-ins**menu, now make sure**Excel Add-ins**are selected in the field:**Manage**. Click on the**Go**button.

- The
**Add-ins**window will appear. After that, put a tick on the**Analysis Toolpak**and click**OK**.

- As a result, the
**Data Analysis**option is added under the**Data**tab of**Excel Ribbon**. Now, click on the**Data AnalysisÂ**option.

- The
**Data Analysis**dialog will pop up. Choose the**Random Number Generation**option and click**OK**.

- Put values on the below fields (see the screenshot) and click
**OK**. For Example, I want to generate a random number list within the range of**10**to**50**.

- Finally, we got the below result.

### 7. Apply VBA as Random Number Generator between Range in Excel

You can use **VBA** as a random number generator in Excel. Letâ€™s see how to create a random number using **VBA** and show it both on the message box and worksheet.

#### 7.1. Generate Random Number Using VBA and Return the Result in the Message Box

Letâ€™s assume I want to get a random number between** 0** and** 13**. Here are the steps involved in the process.

**Steps:**

- First, go to the corresponding worksheet and right-click on the sheet name, and then select the
**View CodeÂ**option.

- As a result, the
**VBA**window will appear. Write the below code on the**Module**.

```
Sub RandomNumber()
Â Â Â MsgBox Round((Rnd() * 10) + 3)
Â End Sub
```

**Run**the code by pressing the**F5**key or clicking on the run icon (see the screenshot).

- Upon running the code, you will get the below result in a message box.

#### 7.2. Create Random Number Using VBA and Display in Excel Worksheet

For instance, if you want to get a random number (whole number) list between** 3 **and **10** then follow the below steps.

**Steps:**

- Go to the corresponding Excel sheet, right-click on the sheet name, and click on the
**View Code**option to bring up the**VBAÂ**window. - Type the below code in the
**Module**.

```
Sub RandomNumberEx1()
Dim N As Integer
For N = 1 To 5
Â Â Â ActiveSheet.Cells(N, 1) = Round((Rnd(10) * 7) + 3, 0)
Next N
End Sub
```

- After that,
**Run**the code. - The below list will appear in the Excel sheet.

### 8. Random Number Generator without Duplicates (RANDBETWEEN, RANK.EQ & COUNTIF Functions)

Most of the time the **RANDBETWEEN **function returns the random number list containing duplicates. So, we will combine the **RANK.EQ** and **COUNTIF** function to get the unique random numbers.

**Steps:**

- First, I have created a random number list between
**1**and**10**inserting the below formula in**Cell B5**.

`=RANDBETWEEN(1,10)`

- Press
**Enter**.

- Then type the below formula in
**Cell C5**and hit**Enter**to get a random number list containing unique numbers between**1**to**10**.

`=RANK.EQ(B5,$B$5:$B$13)+COUNTIF($B$5:B5,B5)-1`

** **

**ðŸ”Ž How Does the Formula Work?**

**âž¤ RANK.EQ(B5,$B$5:$B$13)**

This part of the formula returns {**5**}. Here, the **RANK.EQ **function returns the rank of a number in a list of numbers.

**âž¤ COUNTIF($B$5:B5,B5)**

Now, this part of the formula returns {**1**}. Here the **COUNTIF** function counts the number of cells within **$B$5:B5**, that meet the specified condition.

**âž¤ RANK.EQ(B5,$B$5:$B$13)+COUNTIF($B$5:B5,B5)-1**

Finally, the formula returns {**5**}.

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

## Conclusion

In the above article, I have tried to discuss several methods for random number generators between a range in Excel. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

**<< Go Back to Random Number in Excel |** **Randomize in Excel**Â **|Â Learn Excel**