Random Number Excel Formula (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of random number Excel formula


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()

Random Number Excel Formula using RAND function

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.

Converting random numbers into the percentile

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

Multiply RAND function to generate random numbers


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

Use of RAND function to generate random decimal numbers

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.

Syntax of RANDBETWEEN function

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)

Use of RANDBETWEEN function to generate random integers

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)

Formula based on INT & RAND functions to generate random integers

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)

Use formula combining ROUND & RAND functions to generate random integers


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.

Syntax of RANDARRAY 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)

Using RANDARRAY create an array of random numbers

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


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))

Combination of INDEX & RANDARRAY creates random numbers from a list

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()

Create random numbers using RAND function

  • Select range B5:B14 >> copy pressing Ctrl + C >> click Paste from Clipboard group >> choose Values(V) from Paste Values.

Copy and paste random numbers

  • Insert the below formula in cell C5 and drag the Fill Handle icon.
=RANK.EQ(B5,$B$5:$B$14)

Set rank of random numbers using RANK.EQ function

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.

Enter into Excel options for enabling add-ins

  • Mark the Analysis ToolPak option and click on OK.

Select the desired add-ins

  • Now, move to the Excel worksheet. Select Data Analysis from the Data tab.

Click on the Data Analysis section

  • Choose Random Number Generation from the Analysis Tools section of the Data Analysis window.
  • Then press the OK button.

Select Random number generator option from data analysis window

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

Insert required values for generating random numbers

  • Press the OK button and get a 10 X 4 table of random numbers.

See the generated random values on the worksheet


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.

Enter into VBA window

  • 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

Insert VBA code in the module

  • Press F5 to run the code.
  • Insert the number of random values on the pop-up and press OK.

Input the number of random variables

  • Again, choose a cell to paste the random numbers in the Input window and press OK.

Select a cell to paste the random values

  • Finally, look at the worksheet to see the random values.

Random values are generated after applying vba

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))

Generate random times in Excel

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"))

Creating random dates in Excel

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

Creating both random time & dates at a time

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")))

Creating random letters using the combination of the CHAR, RANDBETWEEN, and CODE functions

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))

Combination of Excel CHAR and RANDBETWEEN functions to create random passwords

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")

Random name generator formula using the combination of CHOOSE and RANDBETWEEN functions

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.

Prevent recalculation from Calculations Options

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


Related Articles

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo