You can use the random number generator in Excel to generate random numbers. Random numbers are data series that have no definite patterns. They are widely used when working with statistical analysis, financial models, or selecting random winners from a list, etc. With Excel, we can build the random number generator in various ways. That involves creating formulas or using different features. In this article, you will learn how to use random number generator in Excel with 6 different examples.

The above overview image shows how to use random number generator in Excel with the **RAND**, **RANDBETWEEN**, and **RANDARRAY **functions. This article will cover other functions as well as features.

**Table of Contents**Expand

## How to Use Random Number Generator in Excel: 6 Examples

We’ll generate 10 random numbers in most examples that will be demonstrated in this article. So, follow along.

### 1. Use RAND Function as Random Number Generator in Excel

**The RAND function** does not require any arguments. The function usually generates a random decimal number greater than or equal to 0 and less than 1 and the numbers change on recalculation.

#### 1.1 Generate Random Number Between 0 and 1

In cell **B5**, type the formula:

`=RAND()`

Press **Enter **and it’ll return a random number. Use **AutoFill** to populate the column. As you can see all the numbers are between 0 and 1.

#### 1.2 Specify Upper Bound of Range

In cell **B5**, insert the formula:

`=RAND()*50`

Here the upper bound value is 50. Press **Enter **and apply **AutoFill**. Hence you’ll see all the random numbers are within 50.

#### 1.3 Generate Random Number Between Two Specified Numbers

With the **RAND **function, you can also generate random numbers within two specified values. For this purpose, we’ll fix the lower bound and the upper bound.

Type the formula in cell **B5**:

`=RAND()*(25-5)+5`

This will return random numbers between 5 and 25.

**Generate Random Whole Number**

This is the same formula as the **RAND **function to generate numbers between two numbers but uses **the ROUND function** to round to zero decimal places.

In cell **B5**, type the formula:

`=ROUND(RAND()*(25-5)+5,0)`

Press **Enter **and apply **AutoFill**.

**Read More:** Generate Random Number between Two Numbers in Excel

### 2. Insert RANDBETWEEN Function to Generate Random Number Within a Specified Range

**The** **RANDBETWEEN function** generates a random integer number between two numbers that we specify.

#### 2.1 Generate Random Integers

Insert the formula in cell **B5**:

`=RANDBETWEEN(5,25)`

Press **Enter **and use **AutoFill**. Here the random numbers are between **5 **and **25**.

#### 2.2 Create Random Numbers with Specified Decimal Places

To use the **RANDBETWEEN **function as a random number generator with 1 decimal place, type the formula:

`=RANDBETWEEN(5*10, 50*10)/10`

To get up to 2 decimal places, replace 10 in the formula with 100. For 3 decimal places, replace with 1000.

**Read More:** Random Number Generator between Range in Excel

### 3. Generate an Array of Random Numbers with the Excel RANDARRAY Function

In this example, we will show how to generate random numbers consisting of 6 rows and 2 columns with **the RANDARRAY function**.

In cell **B5**, apply the formula:

`=RANDARRAY(6,2,10,50,TRUE)`

This formula generates random numbers between 10 and 50. Here, **TRUE **is for generating integers. You can replace it with **FALSE **to get decimals.

**Read More: **How to Generate Random Number from List in Excel

### 4. Use the Data Analysis Feature as a Random Number Generator Without Formula

First, we have to enable the **Data Analysis** add-in. So go to the **File **tab. Click **Options**.

In the **Excel Options** dialog box, go to the **Add-ins** >> choose **Excel Add-ins** in the **Manage **section >> click **Go**.

In the pop-out **Add-ins** dialog box, check the box for **Analysis Toolpak**. Press **OK**.

Now, go to the **Data **tab >> select** Data Analysis** in the **Analyze **group.

Choose **Random Number Generation** in the **Data Analysis** dialog box. Click **OK**.

In the **Random Number Generation** dialog box, insert

- Number of Variables >> 1
- Number of Random Numbers >> 10
- Distribution >> Uniform
- Parameters Between 5 and 25
- Output Range >> B5

Press **OK**.

In this way, you’ll get the random numbers without using any formula.

**NOTE:**

Refreshing worksheets won’t recalculate the random numbers.

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

### 5. Apply Excel VBA as Random Number Generator

Go to the **Developer **tab >> select **Visual Basic**.

In the **VBA **window, click **Insert **> select **Module**. Copy the following code and paste it into the **Module **box. Then press **Run**.

```
Sub RandomNum()
Dim M As Integer
For M = 5 To 14
ActiveSheet.Cells(M, 2) = Round((Rnd() * 30) + 20, 0)
Next M
End Sub
```

Thus the random numbers will appear in the active worksheet.

**NOTE:**

Refreshing worksheets won’t recalculate the random numbers.

### 6. Generate Random Number Without Duplicates in Excel

It’s possible to get duplicate values when generating random numbers with formulas.

#### 6.1 Combine RANK.EQ and COUNTIF Functions to Generate Random Number Without Duplicates

In the above image, we can see 24, 11, and 8 twice. Here we used the **RANDBETWEEN **function:

`=RANDBETWEEN(5, 25)`

In the **C5 **cell, insert the formula:

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

This will rank the numbers starting from 1. In this way, we can avoid getting duplicates.

**Formula Breakdown**

**The COUNTIF function**counts the number of appearances.**The RANK.EQ function**ranks the cell values in descending order.**EQ(B5, $B$5:$B$14) + COUNTIF($B$5:B5, B5) – 1:**Finally this total formula assigns different ranks without any repetition.

#### 6.2 Generate Random Number Without Duplicates with VBA

First press the **Alt **+ **F11** keys together to get the **VBA **window. Next, click **Insert **> select **Module**. Copy the following code and paste it into the **Module **box. Then press **Run**.

```
Sub RandomNumberNoDuplicates()
Dim p As Integer, Temp As String, RandN As Integer
For p = 5 To 14
Repeat:
RandN = Round((Rnd(10) * 9) + 1, 0)
If InStr(Temp, RandN) Then GoTo Repeat
ActiveSheet.Cells(p, 2) = RandN
Temp = Temp & RandN & "|"
Next p
End Sub
```

As a result, random numbers between 1 and 10 appear.

#### 6.3 Combine SEQUENCE, SORTBY, RANDARRAY, and INDEX Functions with Dynamic Arrays

We can also generate a specified amount of unique numbers by combining multiple functions.

`=INDEX(SORTBY(SEQUENCE(15), RANDARRAY(15)), SEQUENCE(10))`

**Formula Breakdown**

**The SEQUENCE function**generates a sequence of numbers mentioned in the argument.- The
**RANDARRAY**function returns an array of random numbers. **The SORTBY function**sorts the outputs of**SEQUENCE**in random order based on the outputs of**RANDARRAY**.**The INDEX function**returns 10 random unique numbers from the sorted sequence.

## How to Generate Random Dates in Excel

To generate random dates, insert the below formula in cell **B5**:

`=RANDBETWEEN(DATEVALUE("1-Mar-2023"), DATEVALUE("30- Mar-2023"))`

Here, the specified dates in **the DATEVALUE function** act as the arguments of the **RANDBETWEEN **function.

Now, you have to apply the **Date **format (**Home** tab >> **Number **group) to the output cells to see dates.

## How to Insert Random Time in Excel

For generating random time, use the formula:

`=RAND()`

Apply the **Time **format (**Home** tab >> **Number **group) to the cells and it’ll show random time.

**Read More: **How to Generate Random Date and Time in Excel

## How to Generate Random Letters in Excel

To get random letters, apply the formula:

`=CHAR(RANDBETWEEN(CODE("A"),CODE("Z")))`

**Formula Breakdown:**

**The CODE function**provides numeric ANSI codes for the mentioned letters.- The
**RANDBETWEEN**function returns random numbers between those numeric ANSI codes. - Finally,
**the CHAR function**transforms the codes into letters.

## Things to Remember

In the case of using functions, Excel generates a new batch of random numbers every time the sheet gets refreshed.

To stop auto-generating new numbers,

- Click the cell >> go to the formula bar >> press
**F9**>> press**Enter**. This converts the formula to its value. - Alternatively, you can copy the cells and paste them as values.

**Download Practice Workbook**

Download the following Excel file for your practice.

## Conclusion

In this tutorial, I have discussed 6 suitable examples of how to use a random number generator in Excel. You can choose either formulas or features whichever suits your requirements. If you don’t want the recalculation of the random numbers, go with the Data Analysis tool or VBA instead. Finally, I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Frequently Asked Questions

**1. How do I generate random numbers that meet certain criteria in Excel?**

Use **the IF function** along with **RAND **or **RANDBETWEEN **to generate random numbers that meet certain criteria in Excel.

**2. Can I generate random numbers in Excel that don’t change every time I open the workbook?**

Yes, you can. Apply the **Data Analysis** tool or **VBA **demonstrated in examples 4 and 5 to get random numbers that won’t change every time you open the workbook. Alternatively, you can copy the random numbers you get from applying formulas and paste them as values.

**Random Number in Excel: Knowledge Hub**

**<< Go Back to Randomize in Excel | Learn Excel **