A random number is not necessary for all users of Microsoft Excel. Most of the users use fixed numbers to perform any operation in Excel. But when we need to work with large files, we do not have any sample data. Then we must take the help of random numbers. We will illustrate how to generate a random number Excel formula in this article.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Why Randomize Numbers in Excel?
In Excel, we need random numbers for different purposes like simulation, testing, data analysis, etc. For those works, we need random numbers to check if the simulation model or system can cope with the uncertain practical situation.
Random Number Excel Formula: 5 Examples
Random numbers can be generated for different requirements in Excel using formulas. Like decimals, integers, within a range, arrays, etc. We discussed all the examples in the below section.
1. Random Number Excel Formula: Decimal Between 0 and 1 in Excel
The RAND function usually generates random decimal numbers. By default, it creates numbers between 0 and 1.
- Look at the formula used on cell B5.
=RAND()
- Then, drag the Fill Handle icon.
The RAND function can work without any argument.
For a better presentation, we convert the decimals into percentile form.
- Choose range B5:B14 and then click on Percent Style from the Number group.
We can also use the Ctrl + Shift + % keyboard shortcut for percentages.
2. Random Number Excel Formula: Decimal Between 0 and Another Number
In this example, we will generate random numbers between 0 to a specific number. For example, consider the specific number is 5. We will use a random number Excel formula based on the RAND function. The RAND function always considers the lower limit 0.
- Insert the following formula on cell B5 and drag the Fill Handle icon.
=RAND()*5
3. How to Generate Random Numbers in Excel within a Range
In this example, we will generate random numbers in Excel within a given range. We will generate both decimals and integers here.
3.1 Random Decimal Numbers
The RAND function is used to generate the above random number Excel formula between 0 to 1 or a specific number. But it is possible to generate random numbers within a range. In this example, we want to generate random decimals between 1 to 20.
The format will be like this:
=RAND()*(B-A)+A
Here, B is the higher number, and A is the lower number.
- Insert the following formula on cell B5 and then pull the Fill Handle icon.
=RAND()*(20-1)+1
We get random decimals between 1 and 20.
3.2 Random Integer Numbers
The RANDBETWEEN function generates random numbers between two integers. It only generates integers, no fractional values.
In this example, we will use the RANDBETWEEN function to generate random integers within a range of 1 to 20.
- Insert the following formula on cell B5 and drag the Fill Handle icon.
=RANDBETWEEN(1,20)
Here, 1 is the lower limit, and 20 is the upper limit. So, we get a random integer between 1 to 20.
Read More: Random Number Generator with Data Analysis Tool and Functions in Excel
Additional Ways to Generate Integer Using RAND and Other Functions:
We can also use the RAND function for random integers. For that, we modified the formula used in example 3.1.
- We used a formula based on the combination of the INT and RAND functions on cell B5.
=INT(RAND()*(20-1)+1)
Due to the INT function, the random numbers are integers.
We can also use the ROUND function with the RAND function to get the integers, and the formula will look like this.
=ROUND((RAND()*(20-1)+1),0)
4. Generate an Array of Random Numbers with the RANDARRAY Function (Both Decimal and Integers)
The RANDARRAY function is used in the random number Excel formula for an array. This function is only available in Excel 365 and Excel 2021. We can generate both decimal and integer random numbers using this function.
In this example, we will create an array of 10 X 4 for random numbers between 1 to 20.
- Put the following formula on cell B5 and drag the Fill Handle icon.
=RANDARRAY(10,4,1,20,FALSE)
We used the FALSE argument in the formula to generate decimal numbers. For integers, we need to use the TRUE argument.
Note:
Our selected array must be blank. This function can not overwrite any data. If any data is found on any cell of the array, the #SPILL error will show.
Read More: How to Generate Random Numbers Without Duplicates in Excel (7 Ways)
Similar Readings
- Auto Generate Invoice Number in Excel (with 4 Quick Steps)
- Generate Random Number with Mean and Standard Deviation in Excel
- Random Number Generator with Normal Distribution in Excel (4 Methods)
- How to Generate Random 10 Digit Number in Excel (6 Methods)
5. Excel Random Number Generator From List
In this example, we will generate random numbers from a given list. For that, we will use a combination of the INDEX and RANDBETWEEN functions.
- We have 10 numbers in the List Now, insert the following formula on cell C5 and pull the Fill Handle icon downwards.
=INDEX($B$5:$B$14,RANDBETWEEN(1,10))
Formula Breakdown
- RANDBETWEEN(1,10)
This generates random integer numbers between 1 to 10.
- INDEX($B$5:$B$14,RANDBETWEEN(1,10))
Based on the value of the RANDBETWEEN, the INDEX function returns a value from range B5:B14.
Use RANK.EQ Function to Check Uniqueness of Random Values in Excel
In generating random numbers using the Excel formula, there may be duplicates. To check the duplicates, we can use the RANK.EQ function. If there is any duplicate rank will not show properly.
📌 Steps:
- First, we generated random numbers using the formula based on the RAND function on cell B5. Then, drag the Fill Handle icon.
=RAND()
- Select range B5:B14 >> copy pressing Ctrl + C >> click Paste from Clipboard group >> choose Values(V) from Paste Values.
- Insert the below formula in cell C5 and drag the Fill Handle icon.
=RANK.EQ(B5,$B$5:$B$14)
We can see the corresponding rank of numbers is showing. If there are any duplicates, the same rank will show.
Generate Random Number in Excel without Formula
We can also generate random numbers without using formulas. We will show here two techniques Analysis ToolPak and VBA Macro.
Method 1: Create Random Numbers Using Analysis ToolPak
Analysis ToolPak is used when we need complex statistical or engineering analyses. It saves our time, and no need to follow complicated steps. Here, we will use this tool to create random numbers in Excel.
📌 Steps:
- Follow File >> Options >> Add-ins.
- In the Manage section, choose Excel Add-ins and press the Go button.
- Mark the Analysis ToolPak option and click on OK.
- Now, move to the Excel worksheet. Select Data Analysis from the Data tab.
- Choose Random Number Generation from the Analysis Tools section of the Data Analysis window.
- Then press the OK button.
- Put the required numbers in the marked sections. Here, Number of Variables for the number of columns, Number of Random Numbers for the number of rows.
- Choose the Uniform distribution system and set parameters between 1 to 20.
- Finally, select a cell in the dataset for Output Range.
- Press the OK button and get a 10 X 4 table of random numbers.
Method 2: Excel VBA to Generate Random Number
We can also generate random numbers using VBA code. Follow the steps below.
📌 Steps:
- Go to the Sheet Name >> press the right button of the mouse >> choose View Code from Context Menu.
- The VBA application window appears. Insert >> Module >> paste the VBA code.
Sub Randon_Number_Generator()
Dim i As Integer
Dim Number_Randoms As Integer
Dim Random_Number As Double
Dim Start_Cell As Range
Number_Randoms = InputBox("Number of random values")
Set Start_Cell = Application.InputBox(prompt:="Select the starting cell", Type:=8)
Randomize Timer
For i = 1 To Number_Randoms
Random_Number = Rnd()
Start_Cell.Offset(i - 1, 0).Value = Random_Number
Next i
End Sub
- Press F5 to run the code.
- Insert the number of random values on the pop-up and press OK.
- Again, choose a cell to paste the random numbers in the Input window and press OK.
- Finally, look at the worksheet to see the random values.
Code Breakdown
- Dim i As Integer, Dim Number_Randoms As Integer, Dim Random_Number As Double, Dim Start_Cell As Range
Here, declaring the variables with their type.
- Number_Randoms = InputBox(“Number of random values”)
Use the input box to set the value of variable Number_Randoms.
- Set Start_Cell = Application.InputBox(prompt:=”Select the starting cell”, Type:=8)
Set the value of the Start_Cell variable from the input box.
- Randomize Timer
This line creates random numbers based on the present system time and generates different random numbers each time.
- For i = 1 To Number_Randoms
Start a For loop, where the value of i considers from 1 to value of Number_Randoms.
- Random_Number = Rnd()
Random numbers are generated and saved in Random_Number.
- Start_Cell.Offset(i – 1, 0).Value = Random_Number
Paste the values of Random_Number in the worksheet specified by Start_Cell.
- Next i
This is the end of For loop.
Read More: Excel VBA: Random Number Generator with No Duplicates (4 Examples)
How to Generate Random Dates and Time in Excel
We can generate random dates and times in Excel using a formula separately or combinedly.
1. How to Insert Random Times in Excel
Now. we will insert random times in Excel. For that, we will use a combination of the TIME and RAND functions. Here, we want to get random times between 8:30:00 to 18:00:00.
- Insert the following formula on cell B5 and drag the Fill Handle icon.
=TIME(8,30,0) + RAND() * (TIME(18,0,0) - TIME(8,30,0))
Formula Breakdown
- TIME(8,30,0), TIME(18,0,0)
This generates a time -based on the arguments given as hour, minute, and second.
Result: 8:30:00, 18:00:00
- TIME(18,0,0) – TIME(8,30,0)
This results in the subtraction of two-time values.
Result: 9:30:00
- RAND() * (TIME(18,0,0) – TIME(8,30,0))
Multiply the Subtactes time with the RAND function.
- TIME(8,30,0) + RAND() * (TIME(18,0,0) – TIME(8,30,0))
Adds the multiplied time value with the start time.
2. How to Insert Random Date in Excel
We can also generate random dates using the formula in Excel. For that, we need to use a combination of the RANDBETWEEN and DATEVALUE functions.
The DATEVALUE function retrieves the serial number of a corresponding date. Then the RANDBETWEEN function generates random dates between the given dates. Here, we will generate random dates between 1st February 2020 to 29th February 2020.
- Apply the formula based on cell B5 and drag the Fill Handle icon.
=RANDBETWEEN(DATEVALUE("1-Feb-2020"),DATEVALUE("29-Feb-2020"))
Formula Breakdown
- DATEVALUE(“1-Feb-2020”), DATEVALUE(“29-Feb-2020”)
Retrieves the serial number of the given dates.
Result: 43862, 43890
- RANDBETWEEN(DATEVALUE(“1-Feb-2020”),DATEVALUE(“29-Feb-2020”))
This generates random dates between the two given dates.
3. Generating Random Dates and Times at a Time
We can generate both random dates and times at a time using a single formula. For that, we will join RANDBETWEEN, DATEVALUE, and TIMEVALUE functions in a single formula.
- Insert the following formula on cell B5 and pull the drag the Fill Handle icon.
=RANDBETWEEN(DATEVALUE("1-Feb-2020"),DATEVALUE("29-Feb-2020")) +RANDBETWEEN(TIMEVALUE("8:30 AM") * 10000,TIMEVALUE("5:00 PM") * 10000) / 10000
Formula Breakdown
- DATEVALUE(“1-Feb-2020”), DATEVALUE(“29-Feb-2020”)
The DATEVALUE function retrieves the serial number of the given dates.
- TIMEVALUE(“8:30 AM”)*10000, TIMEVALUE(“5:00 PM”)*10000
The TIMEVALUE function retrieves the decimal number of corresponding time ranges from 0 to 0.99988426. Then, multiply that value by 10000.
- RANDBETWEEN(DATEVALUE(“1-Feb-2020”),DATEVALUE(“29-Feb-2020”))
The RANDBETWEEN function generates random dates between the given dates.
- RANDBETWEEN(TIMEVALUE(“8:30 AM”) * 10000,TIMEVALUE(“5:00 PM”) * 10000) / 10000
The RANDBETWEEN function generates random times between the given times multiples by 10000. After that, divide the times by 10000.
- RANDBETWEEN(DATEVALUE(“1-Feb-2020”),DATEVALUE(“29-Feb-2020”)) +RANDBETWEEN(TIMEVALUE(“8:30 AM”) * 10000,TIMEVALUE(“5:00 PM”) * 10000) / 10000
Add the random dates and times.
Generating Random Letters in Excel
Random letters can also be generated using a formula in Excel. For that, we will use a combination of the CHAR, RANDBETWEEN, and CODE functions. Here, the CODE function returns the numeric code of any alphabet and this numeric value is used as the argument of the RANDBETWEEN function. After that, the CHAR function uses those random values to generate random characters.
Here, we will generate random letters from “a” to “z” which means all lowercase letters.
- Apply the following formula on cell B5 and drag the Fill Handle icon.
=CHAR(RANDBETWEEN(CODE("a"),CODE("z")))
We get random letters between a to z. To get uppercase letters, we need to insert A and Z in the formula.
Formula Breakdown
- CODE(“a”),CODE(“z”)
The code function generates corresponding numeric codes for “a” and “z“.
Result: 97, 122
- RANDBETWEEN(CODE(“a”),CODE(“z”))
The RANDBETWEEN function generates random values based on the numeric values generated by the CODE function.
- CHAR(RANDBETWEEN(CODE(“a”),CODE(“z”)))
The CHAR function converts the random values into letters.
Generating Random Passwords in Excel
To generate a password, we need numbers, letters, and special characters. We can create random passwords using the formula in Excel. A combination of the CHAR and RANDBETWEEN functions can simply do this work.
We will generate a random password of 4 digits, where 1st one will be a number between 0 to 9, 2nd digit is an uppercase letter, 3rd one is a lowercase letter, and the last digit is a special character.
- Apply the formula below on cell B5 and pull the Fill Handle icon.
=RANDBETWEEN(0,9) & CHAR(RANDBETWEEN(70,90))&CHAR(RANDBETWEEN(102, 122)) & CHAR(RANDBETWEEN(35,45))
Formula Breakdown
- RANDBETWEEN(0,9)
Generate random numbers between 0 to 9.
- CHAR(RANDBETWEEN(70,90))
The RANDBETWEEN function generates random numbers between 70 to 90, and based on this CHAR function generates upper-case letters from F to Z.
- CHAR(RANDBETWEEN(102, 122))
Here, RANDBETWEEN function generates random numbers between 102 to 122, and based on this CHAR function generates lowercase letters from f to z.
- CHAR(RANDBETWEEN(35,45))
The RANDBETWEEN function generates random numbers between 35 to 45, and based on this CHAR function generates special characters.
- RANDBETWEEN(0,9) & CHAR(RANDBETWEEN(70,90))&CHAR(RANDBETWEEN(102, 122)) & CHAR(RANDBETWEEN(35,45))
The ampersand symbol(&) adds all the random values one by another.
Creating a Random Name Generator in Excel
Here, we will create a random name generator using the combination of the CHOOSE and RANDBETWEEN functions.
We have inserted 3 names in the formula. The RANDBETWEEN function creates a random number between 1 to 3, and based on this value, the CHOOSE function retrieves a name.
- Insert the following formula on cell B5 and pull the Fill Handle icon.
=CHOOSE(RANDBETWEEN(1,3),"Biden","Obama","Hillary")
Formula Breakdown
- RANDBETWEEN(1,3)
Generates random numbers between 1 to 3.
- CHOOSE(RANDBETWEEN(1,3),”Biden”,”Obama”,”Hillary”)
Based on the value of the RANDBETWEEN function the CHOOSE retrieves a name.
Frequently Asked Question
1. How to prevent random generator functions from recalculating?
Ans: When using random generator functions like RAND, RANDBETWEEN, and RANDARRAY always recalculates values automatically. It becomes a problem to use the generated random values because change every time. You can follow the below ways to get a fixed value.
- Go to the cell where any random function exists. Then switch to the formula bar and press the F9 button.
- Go to Formula tab >> Calculation group >> Calculation Options >> select Manual.
- You can extract values following this section or select cell >> press Ctrl + C to copy >> click Paste from Clipboard group >> choose Values(V) from Paste Values.
2. How do I generate random numbers from 1 to 100 in Excel?
Ans: Yes, I can generate random numbers from 1 to 100 in Excel. Follow example 3 for that. The RANDBETWEEN function will be most convenient in this case and the formula will be:
=RANDBETWEEN(1,100)
Things to Remember
- If you need fixed values then stop the recalculation.
- To get unique random values use the RAND function instead of the RANDBETWEEN function.
- If we need only integer random values use the RANDBETWEEN function.
- The RANDARRAY function can generate both decimal and integer random values.
Conclusion
In this article, we have discussed the random number Excel formula. We showed 5 different examples. We also showed how to generate random numbers without formulas. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.