Random numbers are those numbers that are generated by a process in which the outcome is not predictable and in a defined interval/set, the values are uniformly distributed. Random numbers are used in statistical sampling, computer simulations, data encryption, lottery, and in the areas where unpredictable results are desired. In this article, we will see many processes on how to generate random numbers in Excel. We will also see the procedures for generating letters, words, dates, and times also.

## Download Practice Workbook

**4 Ways to Generate Random Numbers in Excel**

In this section, we are going to explore 4 unique ways to generate random numbers in Excel. I will demonstrate them here one by one. Let’s check them out!

**1. Using RAND Function**

The **RAND function** is used to generate random numbers. If we use it directly it generates random numbers between zero and one.

Whenever you edit your worksheet, new numbers are generated in the area where **RAND functions** are placed. The reason for this is that the **RAND function** is a volatile function. It doesn`t have any arguments.

**1.1. RAND Function with Default Range**

Normally the **RAND function** generates random numbers which are between **0** and** 1**.

Let’s say, we have a dataset of some persons participating in a lottery. We need to create some random numbers for these participants. Let’s create random numbers with this function.

**Steps**:

- First of all, select a cell and type the following formula in the cell.

`=RAND()`

- Now, press
**ENTER**and the cell will return you a random number. - Now, drag the
**Fill Handle**tool to the other cells to**Autofill**the formula to the cells downward.

- Here, all the cells will show you the results.

The above procedure shows the primary use of the **RAND function** where many random numbers are generated.

**1.2. RAND Function with a ****Customized ****Range**

Normally the **RAND function** generates random numbers which are between **0 **and **1**. But if you want to produce random numbers between two values you need to modify the **RAND function**.

**1.2.1. Specifying Upper Limit**

In this way, you can produce random numbers from** 0** to **N** where **N** is the upper limit in this case. You just need to multiply the **RAND function** by **N**.

Let’s say we want to generate some random numbers from** 0 to 25**. We will multiply **25 **with the **RAND function**. It will produce random numbers in a sequence that are greater than/equal to **0 **but less than** 25**.

*Note:**In Excel, the upper limit is never included in the sequence of random numbers. If you want to generate numbers between 0 and 4 including 4, you need to specify the upper limit as 5.*

**1.2.2. Random Numbers Between Two Numbers**

The previous example shows us the random numbers which are between **0** and any value** N**. A question might arise *“How can we create random numbers between two values that don’t start with zero?”* Well, we can use a formula to execute that which is,

`=RAND () *(Y-X) +X`

Where **Y** is the highest value and **X** is the lowest value in the range.

The following picture shows an example of generating arbitrary numbers between **100 **and **50**.

*Note:** This formula never returns a number that is the largest in a specific range.*

**1.2.3. Random Integer with Specified Upper Limit**

To create random integer numbers in Excel we can use the above two formulas wrapped in the **INT function**.

For creating random integers which are between **0** & **25**, we can use the formula

`=INT (RAND () *25)`

**1.2.4. Random Integer with Changed Range**

For creating arbitrary integers between **100** and **50**, we can use the formula

`=INT (RAND () *(100-50) +50)`

**2. Use of RANDBETWEEN Function to Generate Random Numbers**

In the previous examples, we create random numbers between two numbers with the **RAND function**. But it is more useful to use the **RANDBETWEEN function** in this case. **RANDBETWEEN function** returns a random number that is between a bottom and top range. Like the **RAND function**, the **RANDBETWEEN** is also a **volatile function **and it will generate new random numbers whenever you edit your worksheet.

**2.1. Random Integer with RANDBETWEEN Function**

The interesting thing about using the **RANDBETWEEN function **is that generally, it will produce **Integer** random numbers whenever you are using it. Even with the decimal range, it will always generate integer random numbers.

For instance, if you want to create random integers between **15** and **30**, the following formula can be used.

`=RANDBETWEEN (15, 30)`

The following picture shows us the result of this example. You can see that all the outputs are between 15 and 30.

**2.2. Random Numbers with Decimal Places**

As stated before, the **RANDBETWEEN function **is designed to produce random integer numbers in Excel, but by some little modification, it can also be used to generate random decimal numbers.

Let’s work with the previous example where we got random integer numbers from **15** to **30**. Here instead of getting the integer numbers if we want to get the decimal numbers, the modification of the formula will be:

`=RANDBETWEEN(15*10,30*10)/10`

The above picture shows us the numbers with **1 decimal **place. If we want to get random numbers with **2 decimal** places the formula will be

`=RANDBETWEEN(15*100,30*100)/100`

**3. Generate Unique Random Numbers (with No Duplicates/Repeats)**

Generating random numbers is easy but when it comes to generating unique random numbers the task is complicated. There are so many methods that we can perform. In this section, we will demonstrate some of the methods available for generating unique random numbers.

**3.1. Sorting a List of Unique Numbers in Random Order**

In this method, we will create a list of numbers in serial order. Then again in another column, we will use the **RAND function**. The two columns, then, will be filtered and we will see that the 1^{st} column where the numbers are in serial order will be distributed randomly. As the numbers that we provided are not repeated here, we will find a list of unique numbers in a column.

- First, make two columns titled
**Number**and**Random**. The number, column creates a series of**15**numbers in serial order starting from**1**. To do this, go to the**Home**tab> click on the**Fill**option from the**Editing**group.

- A box named
**Series**will come up, select columns there. Write**1**as the**Step value**and**15**as the**Stop value**. Then press**OK.**

- In the
**Random**column create a list of arbitrary numbers. Make sure to create the column in the same range as the first column.

- After that, click on the
**Sort & Filter**option and click on**Filter**

- In the Random column click any two options:
**Sort Smallest to Largest**/**Largest to Smallest**.

- You will see, by doing the sorting the numbers in the 1
^{st}columns will change their places randomly from one cell to another

**3.2. Ranking Random Numbers**

For ranking the random numbers in a list, we will use the **RANK function**.

- 1
^{st}create some random numbers in a column. - In cell
**C5**, type the formula and drag this formula to cell**C20**.

`=RANK(B5,B$5:B$20)`

Here,

**B5**= number**B$5:B$20**= reference

You will see that **20** unique random numbers will be generated in column B.

**3.3. Using Largest Values and MATCH Function**

Here, we will use the **MATCH**,** ROW**, and** LARGE **functions.

- 1
^{st}create some random numbers (i.e.**10**random numbers) in column A. - In cell
**C5**, write the formula

`=MATCH(LARGE($B$5:$B$14, ROW(C5)-4), $B$5:$B$14, 0)`

` `

- Finally, drag the formula from
**C5**to**C14**. It will return unique random numbers from**1**to**10**

** 💡 Formula Breakdown**

**ROW(C5) **gives the row number: **5**

**LARGE($B$5:$B$14, ROW(C5)-4) **= **LARGE($B$5:$B$14, 5-4) **= **LARGE($B$5:$B$14, 1) **returns the first large number in the range **$B$5:$B$14**.

Output=> **0.960104699**

**MATCH(0.960104699, $B$5:$B$14, 0) **gives the position of **0.960104699**.

So, Output=> **8**

*Note:**You can use*

**COLUMN**instead of**ROW**. In that case, you have to drag the formulated cell on the right/left side. If you use**ROW**in the formula and drag it to left/right it will produce the same numbers.**3.4. Using RANK and INDEX Functions**

In this section, we will see the use of **RANK** and **INDEX** functions.

- 1st create a list of arbitrary numbers, let’s say create
**10**random numbers in column A. - In column B, create
**10**Random numbers. - Use the formula in cell
**D5**and drag it down to cell**D14**.

`=INDEX(A$1: A$10, RANK(B1, B$1: B$10))`

** 💡 Formula Breakdown**

**RANK(B1, B$1: B$10) **gives the rank of **0.652397109** (cell value of **B1**) in the range **B$1: B$10. **Output=> **4**

**INDEX(A$1: A$10, 4) **checks whether **4 **is present in the array. As it finds the value in the array, Output=> **4**

- You will see
**10**unique random numbers will appear in column**D**

**4. Generate Random Numbers Using Excel VBA**

Generating unique random numbers is more efficient in **VBA** code than using different functions of Excel. In Excel, there are so many **VBA **codes available to generate numbers that can be unique or repeated. In this example, I generate 10 arbitrary numbers using **VBA** code. The code is given below.

**Code**:

```
Public Sub GeneratingRandNum()
lowerlimit = 1
upperlimit = 15
Set randrange = Range("B3:B17")
randrange.Clear
For Each sm_rng1 In randrange
Number = Number + 1
Next
If Number > upperlimit - lowerlimit + 1 Then
MsgBox ("Cell's Number > Count of Unique Random Numbers")
Exit Sub
End If
For Each sm_Rng In randrange
randnum = Int((upperlimit - lowerlimit + 1) * Rnd + lowerlimit)
Do While Application.WorksheetFunction.CountIf(randrange, randnum) >= 1
randnum = Int((upperlimit - lowerlimit + 1) * Rnd + lowerlimit)
Loop
sm_Rng.Value = randnum
Next
End Sub
```

To use this code, follow the following steps.

- Click on the
**Developer**tab - From the Code group, select
**Visual Basic** - Enter the above code in the worksheet module and run it.

You will see that **15** numbers from** 1-15** appear in column **B**. The below picture shows the result of the above code.

**Generate Random Letter in Excel **

To produce random letters in Excel a combination of three different functions are required. Let’s say we want to create random letters from **A **to **Z**. We will use the formula stated below:

`=CHAR(RANDBETWEEN(CODE("A"),CODE("Z")))`

In the above formula:

- Firstly, numeric
**ASCII codes**for the specified letters are returned by the**CODE function**. - The numbers returned by the
**CODE function**are taken by the**RANDBETWEEN function**as the upper and lower values of the range. - Finally, corresponding letters are converted by the
**CHAR function**(**CHAR**converts random**ASCII codes**returned by**RANDBETWEEN**)

**Generate Random Dates in Excel**

To produce some arbitrary dates between two dates, we can use the **RANDBETWEEN function** with the **DATEVALUE function**. Here in this example, we will generate some dates between **1st March 2022 **to **17th March 2022**. The Formula is given below.

`=RANDBETWEEN(DATEVALUE("1-Mar-2022"),DATEVALUE("17-Mar-2022"))`

*Note:**Sometimes the cells may not give you a date rather they will give you some random number. You need to format the cell in a specific*

**Date Format**to show dates in the cells.## Generate Random Time in Excel

The procedure of generating arbitrary times between two times is the same as above. The formula can be given as follow by using the **TIMEVALUE function**.

`=TIMEVALUE("Starting Time") + RAND() * (TIMEVALUE("Ending Time") - TIMEVALUE("Starting Time"))`

So, here in this example, we will use,

`=TIMEVALUE("12:00 AM") + RAND() * (TIMEVALUE("6:00 PM") - TIMEVALUE("12:00 AM"))`

The same thing can be gotten with the **TIME function **and for this, the formula will be,

`=TIME(12,0,0) + RAND() * (TIME(18,0,0) - TIME(12,0,0))`

*Note:**The*

**TIMEVALUE function**actually gives a valid time from a text string and the**TIME function**gives time with hours, minutes, and seconds. If you again find a random number instead of time, format the cells with a specific time format.**Generating Random Dates & Times together**

In order to generate random dates and times together, we need to combine **RANDBETWEEN**, **DATEVALUE**, and **TIMEVALUE** functions together. Here we used the following formula to create dates and times together in a range.

`=RANDBETWEEN(DATEVALUE("1-Mar-2022"),DATEVALUE("17-Mar-2022"))+RANDBETWEEN(TIMEVALUE("8:00 AM")*10000,TIMEVALUE("8:00 PM")*10000)/10000`

Where the start and end dates are **1-Mar-2022** & **17-Mar-2022** respectively. The start and end times are **8:00 AM **& **8:00 PM** respectively.

**Conclusion**

Random numbers are useful in so many ways. As we can see there are so many methods of generating random numbers. I hope this article may help you. If you have any better methods or any queries, please feel free to comment. Thanks for keeping in touch.

*Read More…*

*Read More…*

Thanks for another great blog post! I can’t wait to share it with my Excel students. I always learn something new from your post.

I found a small typo in Generating Excel random integer with RAND function. You have 50, instead of 25, as the multiplier (correct in the photo).

Thanks again for sharing!

Thanks for identifying the typing mistake. I have solved it already. It`s good to know that you find this article useful.

Cheers!!!

Thank you. I tested the vba code with my own and this one is the absolute winner. I tested with 100 and 15.0000 random numbers. My code 0.04 and 13.22 seconds. This code 0.01 and 8.66 seconds. Nothing more to say.

Thanks for the comment Mr. JAN. Well, I didn’t test the code. I found accurate results that I was looking for. The other codes that I found out weren`t that much helpful. There were too many errors.