While working in Microsoft Excel, sometimes we require a random 5 digit number generator. Especially while doing statistical analysis we might need to generate 5 digit numbers. Again we may use a 5 digit number generator to create passwords or IDs. Luckily Excel has several options to get random 5 digit numbers. This article will guide you to use those options.

**Table of Contents**Expand

## Random 5 Digit Number Generator in Excel: 7 Examples

### 1. Excel RANDBETWEEN Function as 5 Digit Number Generator

First of all, we will use **the RANDBETWEEN function** as a 5 digit number generator. This function allows us to get random numbers between specified numbers. For example, I will generate 5 digit numbers between **10000 **and **99999**. To get the desired result follow the below steps.

**Steps:**

- Type the below formula in
**Cell B5**and press**Enter**.

`=RANDBETWEEN(10000,99999)`

- As a result, we will get the below 5-digit number. Next, use the
**Fill Handle**(**+**) tool to get 5-digit numbers over the range**B6:B10**.

- Consequently, we will get the below output.

**⏩**** Note:**

The** RANDBETWEEN** function is a volatile function. Random numbers generated by this function change every time a cell on the worksheet is calculated. If you want to avoid these changes in numbers follow the below steps.

**Steps:**

- First, copy the random numbers generated by the
**RANDBETWEEN**formula by following**Home**>**Copy**or**Ctrl + C**.

- Then paste them as
**Values**by following**Home**>**Paste**>**Paste Values**(see screenshot).

- As a result, you will get the numbers as values.

### 2. Generate Random 5 Digit Number with LEFT & RANDBETWEEN Functions

In this method, I will use a formula with the combination of **LEFT** and **RANDBETWEEN **functions. This formula will generate random numbers depending on the length of the numbers given in the cell referenced by the formula. Let’s see how we can do the task.

**Steps:**

- First, type the below formula in
**Cell B6**and hit**Enter**. The formula will return an empty cell.

`=LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),B5)`

- Now, type
**5**in**Cell B5**as you need a random number with 5-digits. Once you hit**Enter**, in**Cell B6**you will get a 5-digit random number.

**🔎**** How Does the Formula Work?**

**RANDBETWEEN(1,9)**

Here the above formula returns a random number between **1** to **9**.

**RANDBETWEEN(0,999999999999999)**

Here the **RANDBETWEEN **function returns a random number between **0** to **999999999999999.**

**LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),B5**

Lastly, the above formula returns a random number containing the length of **Cell B5**.

### 3. Create 5 Digit Number Using ROUND & RAND Functions in Excel

This time I will use the combination of **ROUND** and** RAND** functions as a 5-digit random number generator. The generic formula for generating the numbers is:

`=ROUND(RAND()*(Y-X)+X,0)`

Where** X** and** Y** is the bottom and top number between which you want to generate 5-digit numbers.

**Steps:**

- Type the below formula in
**Cell B5**. Next press**Enter**.

`=ROUND(RAND()*(99999-10000)+10000,0)`

- As a consequence, you will the below 5-digit numbers.

**🔎**** How Does the Formula Work?**

**RAND()**

Here the **RAND **function generates random decimal numbers.

**RAND()*(99999-10000)+10000**

In this part, the result of the **RAND** function is multiplied by **89999**. Then the result is added to **1000**.

**ROUND(RAND()*(99999-10000)+10000,0)**

Finally, the **ROUND** function rounds the result of the previous formula to zero decimal places.

### 4. Combine INT & RAND Functions as 5 Digit Number Generator

This method is kind of similar to the previous method. Instead of the **ROUND **function, we will use **the INT** **function **here. To create 5-digit random numbers between** 10000** and** 99999** follow the below steps.

**Steps:**

- Type the below formula in
**Cell B5**. Then press**Enter**.

`=INT(RAND()*(99999-10000)+10000)`

- As a result, you will get the following output.

Here the above formula works in a similar way mentioned in **Method 3**. First, the** RAND** function generates random decimal numbers. Then the resulting decimal number is multiplied by **89999** and added to **1000**. Lastly, the **INT **function rounds the number to the nearest 5-digit integer.

### 5. Create Random 5 Digit Number with RANDARRAY Function

You can use the **RANDARRY **function as a random 5-digit number generator. To create 5-digit random integers between **10000** and **99999**, and spread over** 2** columns and **6 **rows follow the below instructions.

**Steps:**

- Type the following formula in
**Cell B5**.

`=RANDARRAY(6,2,10000,99999,TRUE)`

- Once you hit
**Enter**, the above formula returns 5-digit random numbers (integers) over columns**B**&**C**and rows**5:10**.

### 6. Apply Analysis ToolPak to Generate 5 Digit Numbers in Excel

In this method, I will use an Excel add-in as a 5-digit number generator. First I will show you adding the add-in to the **Excel Ribbon**. Later, I will use that add-in to generate 5-digit random numbers.

**Steps:**

- First, go to the
**File**tab from the ribbon.

- Secondly, select
**Options**.

- Next, the
**Excel Options**dialog will appear, click on**Add-ins**. Check**Excel Add-ins**selected from the**Manage**drop-down menu and press**Go**.

- As a result, the
**Add-ins**dialog will show up, put a checkmark on**Analysis ToolPak**and press**OK**.

- Now go to the
**Data**tab, and the**Data Analysis**option is available. Click on it.

- Consequently, the
**Data Analysis**dialog box appears, select**Random Number****Generation**from the**Analysis Tools**list, and press**OK**.

- When the
**Random Number Generation**dialog shows up, enter**2**as the**Number of Variables**, and**6**as the**Number of Random Numbers**. - Then, choose
**Uniform**from the**Distribution**drop-down. In the**Parameters**section enter the range of 5 digit numbers (**10000**and**99999**) in the**Between**field. - After that, select the
**Output Range**and choose the destination cell (here**Cell $B$5**). Press**OK**to close the dialog.

- Finally, we can see the output below.

**⏩**** Note:**

- 5-digit random numbers generated by
**Analysis ToolPak**contain decimals. To convert those numbers to zero decimal places you can use the**ROUND**or**INT**functions (described in**Method 4**and**Method 5**).

### 7. Apply Excel VBA as 5 Digit Number Generator

You can use **Excel VBA** to generate 5-digit random numbers.

**Steps:**

- First, go to the sheet where you want to get the 5-digit random numbers. Then right-click on the sheet name and select
**View Code**to bring up the**VBA**window.

- Now type the below code in the
**Module**and run using the**F5**key.

```
Sub Random5DigitNumber()
Dim N As Integer
For N = 5 To 10
ActiveSheet.Cells(N, 2) = Round(Rnd() * (99999 - 10000) + 10000, 0)
Next N
End Sub
```

- Finally, upon running the code you will get the below 5-digit numbers.

## Things to Remember

- The result we receive from the
**RANDBETWEEN**function contains duplicates. To detect the duplicate numbers you can use the**RANK.EQ**function in Excel. - The
**RAND**function is also a volatile function. You can convert the results returned by the**RAND**formula to values using the**Paste Special**option.

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

## Conclusion

In the above article, I have tried to discuss several examples of a random 5 digit number generator in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

**<< Go Back to Random Number in Excel |** **Randomize in Excel** **| Learn Excel**