# How to Use Random Number Generator in Excel (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

### 3. Generate Array of Random Numbers with 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.

### 4. Use Data Analysis Feature as 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.

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

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

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.

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

## Related Articles

#### Hafiz Islam

Hi there. I am Hafiz, graduated from BUET. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. Now you can see my articles in the ExcelDemy blog.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF