Hafiz Islam
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.
Download Practice Workbook
Download the following Excel file for your practice.
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()*50Here 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)+5This 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)/10To 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.
Similar Readings
- Auto Generate Invoice Number in Excel (with 4 Quick Steps)
- Generate Random Number Between 0 and 1 in Excel (2 Methods)
- Random 5 Digit Number Generator in Excel (7 Examples)
- Generate Random Number from List in Excel (4 Ways)
- Random 4 Digit Number Generator in Excel (8 Examples)
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.
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.
Read More: Excel VBA: Random Number Generator with No Duplicates (4 Examples)
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) - 1This 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.
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.
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.
Conclusion
In this tutorial, I have discussed 6 suitable examples of how to use 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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Generate Random 10 Digit Number in Excel (6 Methods)
- Random Number Generator in Excel with No Repeats (9 Methods)
- Generate Random Number between Two Numbers in Excel (4 Ways)
- How to Generate Random Number in a Range with Excel VBA
- Random Number Generator with Normal Distribution in Excel (4 Methods)
- Generate Random Number with Mean and Standard Deviation in Excel
