ExcelInsider Team
Generating random numbers between a range is useful when you are creating random questions or options for a quiz or games, assigning Random Values or Categories, passwords or coupon codes. RAND, RANDBETWEEN, and RANDARRAY are the built-in functions in Excel for generating random numbers based on different criteria. By using these functions, you can quickly generate whole numbers or decimal values within any custom range. You can also generate random numbers between a range using VBA and the Data Analysis Toolpak.
To generate random numbers between a range using the RANDBETWEEN function, follow the steps below:
➤ Click on the cell E2 and write this formula: =RANDBETWEEN(60, 100)
➤ Press Enter to get a random number between 60 and 100
➤ Drag the formula down to generate more random numbers in the remaining cell.
In this article, we’re going to learn different suitable methods of generating random numbers between a range in Excel, which include using the RANDBETWEEN function, using the RAND Function with a Custom Formula, using the RANDARRAY Function, using a Custom VBA Function, and using the Data Analysis ToolPak.
Generate Random Numbers within a Range with RANDBETWEEN Function
The RANDBETWEEN function gives an integer number between the range you specified. The bottom number is the lower limit, and the top number is the upper limit. The function will return a random number between these two numbers.
Let’s look at the dataset below, where we will generate a random ticket number using the RANDBETWEEN function in column E. We will generate a random ticket number between 60-100.
Steps:
➤ Write the formula in cell E2:
=RANDBETWEEN(60, 100)
➤ Press Enter to get a random number between 60 and 100
➤ Drag the formula down to generate more random numbers in the remaining cell.
Note:
If you edit a cell or press Enter, the values will change every time, and the numbers can be repeated if you are using this function to generate random numbers.
Inserting RAND Function with a Custom Formula
The RAND function generates a random decimal number between 0 and 1. You can generate random numbers between a range using a simple formula.
Steps:
➤ Click on cell E2 and enter the formula:
=RAND()*(105-80)+80
➤ Press Enter to see a decimal number between 80 and 105
➤ Drag the formula down to fill the column
Note:
If you edit a cell or press Enter, the values will change every time.
Using the RANDARRAY Function
You can create multiple random numbers at once in a defined range with both integer and decimal numbers using the RANDARRAY function.
The syntax of the RANDARRAY function is: =RANDARRAY([rows], [columns], [min], [max], [whole_number]). Whole_number is TRUE for integers, FALSE for decimals.
Steps:
➤ Click on cell E2 and enter the formula:
=RANDARRAY(10, 1, 10001, 99999, TRUE)
➤ Press Enter, and you will see 10 random numbers (between 10001 and 99999) instantly filled. It’s a dynamic function, so you don’t need to drag it down.
➤ Select the random numbers > click the number box > choose number > keep the decimal place 0, and then click ok to hide decimal numbers.
➤ Now you can see the integer random numbers are generated between 10001-99999
Note:
The RANDARRAY function is only available in Office 365 and Excel 2021 or later.
Automate with Custom VBA Macro
Using a custom VBA macro, you can generate fixed random numbers in Excel that do not change every time the sheet updates.
Let’s apply this method to our employee dataset, where we’ll generate a random Ticket Number between 60 and 100 using VBA.
Steps:
➤ Press Alt + F11 to open the VBA Editor
➤ Click on Insert > Module to add a new module
➤ Paste the following VBA code:
Sub GenerateTicketNumber()
Dim i As Integer
For i = 2 To 11 'Rows 2 to 11 for 10 buyerId
Cells(i, 5).Value = WorksheetFunction.RandBetween(60, 100)
Next i
End Sub
➤ Press Ctrl + S and save the file as a Macro-Enabled Workbook (.xlsm)
➤ Close the VBA Editor and go back to Excel
➤Select the cell E2
➤ Press Alt + F8 to open the Macro box, select GenerateTicketNumber, and click Run
➤ Now you can see that random numbers are generated between the 60-100 range, and the numbers are fixed.
Note:
You have to save the file in .xlsm format, otherwise the macro will be lost. The random values are constant unless you run the macro again.
Using Data Analysis Toolpak
Data Analysis Toolpak is an add-in in Excel. Using these add-ins, you can generate large random decimal numbers instantly. The decimal numbers are between 60-100 range and these numbers will not change automatically.
Steps:
➤ Click on the File tab
➤ Then select “Options” from the bottom.
➤ Click Add-ins, then select Excel Add-ins from the manage box, then click Go
➤ Check the box for Analysis ToolPak and click OK
➤ Go to the Data tab
➤ Click on Data Analysis
➤ From the list, choose Random Number Generation and click OK
➤ Set the Number of Variables: 1, Number of Random Numbers: 10, because we have 10 buyer details. Distribution: Uniform, parameter Between: 60 and 100, Output Range: E2, Click OK
➤ Now you can see that 10 cells are filled instantly with random numbers between 60 and 100.
Frequently Asked Questions
Is it possible to stop updating random numbers every time?
Random numbers are updated when you are editing or clicking in any cell. That means you are using functions, and these functions are volatile. If you want to stop updating random numbers every time and fix values, follow these steps:
➤ Select the column with random numbers.
➤ Press Ctrl + C to copy.
➤ Right-click > Paste Special > Values.
Is it necessary to install anything to generate random numbers in Excel?
Random number generator functions are built-in functions in Excel. You don’t need to install anything, but you need to activate the Data Analysis Toolpak from the add-ins menu.
Why are the random numbers not showing in Excel?
If your random numbers are not showing in the cell, that means you need to recheck the following things.
➤ Make sure you pressed Enter after typing a formula
➤ Check if calculation is set to manual (go to Formulas > Calculation Options > Automatic)
➤ If using macros or Toolpak, check if they are properly enabled
Wrapping Up
In this article, we have discussed different ways to generate random numbers between a range in Excel. Random number generation processes are shown step by step using built-in functions like RANDBETWEEN, RAND, RANDARRAY, VBA and the Data Analysis Toolpak option. Feel free to download the practice file and share your thoughts and suggestions in the comments section.
