Random Number Generator Within Range in Excel (8 Examples)

In this article, we will demonstrate how to generate a random number within a range in Excel in 8 different ways.

Example 1 – Using Excel RAND Function to Generate Number within a Range

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

Steps:

• Enter the formula below in Cell B5:

• Press Enter.

A number between 0 to 1 is returned.

• Drag the Fill Handle (+) tool down to get a list of numbers within the RAND function’s range.

Here is the list of numbers.

We can extend the range of the random numbers generated the RAND. For example, suppose we want numbers between 0 and 6.

• Enter the below formula in Cell B5 and press Enter:

• Drag down the Fill Handle (+) to return the below result.

Convert Formula Results to Values

There is an issue with the above formula. The RAND function is a Volatile Function, which means the numbers returned by the function will continuously change on recalculation. If we want to preserve the random numbers generated by the function, we’ll have to to convert the result of the above formula to values.

Steps:

• Select the resultant list from the process above, and press Ctrl + C.

• From the Excel Ribbon, go to Home.
• Click on Paste.
• Click on the Paste Values icon.

The numbers are pasted as values. Now, these values won’t change on recalculation.

Example 2 – Using RANDBETWEEN Function

We can similarly use the RANDBETWEEN function to generate a list of random numbers. The function allows setting the upper and lower limits of your range. Suppose we want to generate random numbers between 10 and 50.

Steps:

• Enter the below formula in Cell B5 and press Enter:

A number between 10 and 50 is generated.

• Use the Fill Handle tool to generate a list of random numbers.

Because the RANDBETWEEN function is also a Volatile Function, if you need to preserve the generated values, convert the results to values.

Example 3 – Using RANK.EQ and RAND Functions

To control the repetition of random numbers generated by the RAND function, we can use the RANK.EQ function.

Steps:

• Generate a random number list using the RAND function.

• Convert the list to values using the Paste Values option (described in Method 1).
• Enter the below formula in Cell C5:

• Press Enter.

Now if any duplicate values appear in Column B, Column C will indicate it by giving those values an equal rank.

Example 4 – Using RANDARRAY Function

In Excel 365, we can use the RANDARRAY function as a random number generator.

The syntax of the RANDARRAY function is as follows:

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

Suppose we want to create a random number array of whole numbers between the range of 10 and 20, containing 5 rows and 2 columns.

Steps:

• Type the below formula in Cell B5 and press Enter:

An array of the specified dimensions containing random whole numbers is returned.

Example 5 – Combining ROUND and RAND Functions

Now let’s use the ROUND function along with the RAND function to generate a list of random numbers between 0 and 20.

Steps:

• Enter the following formula in Cell B5 and press Enter:

A list of random numbers within the specified range is generated.

Here, the result of the RAND formula is multiplied by 19 and 1 is added to the product. Then the ROUND function rounds the generated decimal number to 0 decimal places.

Example 6 – Using Analysis Toolpak Add in

We can generate a random number list without using a formula by means of an Excel add-in.

Steps:

• Go to the File tab on the Excel Ribbon.

• Click on Options.

The Excel Options window will open.

• Go to the Add-ins menu,.
• Select Excel Add-ins in the field Manage.
• Click the Go button.

The Add-ins window will appear.

• Tick the Analysis Toolpak and click OK.

The Data Analysis option is added under the Data tab of Excel Ribbon.

• Click on Data Analysis.

The Data Analysis dialog will pop up.

• Choose the Random Number Generation option and click OK.

• Put values in the fields like in the image below and click OK.

A result similar to the image below will be returned.

Example 7 – Using VBA Code

We can create a random number using VBA and display it either in a message box or in the worksheet.

7.1 – Return the Result in the Message Box

Suppose we want a random number between 0 and 13.

Steps:

• Right-click on the sheet name, and select View Code.

The VBA window will appear.

• Enter the code below in 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.

A random number between 0 and 13 is displayed in a message box.

7.2 – Display in Excel Worksheet

This time, let’s generate a random list of whole numbers between 3 and 10.

Steps:

• Right-click on the sheet name, and click on View Code to bring up the VBA window.
• Enter the code below 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

• Run the code.

A list like the one below will appear in the Excel sheet.

Example 8 – Random Number Generator without Duplicates

Particularly when generating whole numbers, the RANDBETWEEN function often returns a random number list containing duplicates. We can combine the RANK.EQ and COUNTIF functions to return only unique random numbers.

Steps:

• Create a list of random numbers between 1 and 10 by inserting the below formula in Cell B5:

• Press Enter.

• Then enter the below formula in Cell C5 and hit Enter.

A random number list is generated containing only unique numbers from 1 to 10.

How Does the Formula Work?

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

The RANK.EQ function returns the rank of a number in a list of numbers, here {5}.

➤ COUNTIF($B$5:B5,B5)

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

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

The final output is {5 + 1 – 1} = {5}.

Download Practice Workbook

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