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.