Random Number Excel Formula (5 Examples)

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.

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

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:

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

We get random letters between a to z. To get uppercase letters, we need to insert A and Z in the formula.

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.

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.

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:

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.

Related Articles

• Generate Random Number from List in Excel (4 Ways)
• Random Number Generator in Excel with No Repeats (9 Methods)
• How to Generate Random Number with Excel VBA (4 Examples)
• Random 4 Digit Number Generator in Excel (8 Examples)
• Random 5 Digit Number Generator in Excel (7 Examples)