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.

## Download Practice Workbook

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

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

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

**Read More:** **Excel Formula to Generate Random Number (5 examples)**

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

**Read More:** **How to Generate Random Number with Excel VBA (4 Examples)**

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

**Read More:** **Excel VBA: Random Number Generator with No Duplicates (4 Examples)**

### 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)`

**Similar Readings**

**How to Generate Random Data in Excel (9 Easy Methods)****Random 5 Digit Number Generator in Excel (7 Examples)****Random 4 Digit Number Generator in Excel (8 Examples)****Random Number Generator in Excel with No Repeats (9 Methods)**

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

**Read More: Generate Random Number in Excel with Decimals (3 Methods)**

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

**Read More:** **Random Number Generator with Data Analysis Tool and Functions in Excel**

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

**Read More:** **How to Generate Random Number in a Range with Excel VBA**

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

**Read More:** **How to Generate Random Numbers Without Duplicates in Excel (7 Ways)**

## Conclusion

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

**Related Articles**

**How to Generate Random 10 Digit Number in Excel (6 Methods)****Generate Random Number between Two Numbers in Excel (4 Ways)****Random Number Generator with Normal Distribution in Excel (4 Methods)****Generate Random Number with Mean and Standard Deviation in Excel****Auto Generate Invoice Number in Excel (with 4 Quick Steps)****Generate Random Number Between 0 and 1 in Excel (2 Methods)**