When we work on an Excel spreadsheet, sometimes it is required to generate some random numbers. As the human mind is biased when people try to input numbers manually, they execute it with lots of duplicates. Excel has several functions to generate random numbers without duplicates. In this context, we will demonstrate to you 7 distinct approaches on **how to generate random numbers in Excel** without duplicates.

In this article, we will generate 10 random numbers in our Excel spreadsheet with the help of different functions. Besides it, we will try to avoid the execution of duplicate numbers in our dataset.

**Table of Contents**hide

## 1. Using RANDBETWEEN to Generate Random Numbers in Excel Without Duplicates

In this process, we are going to use **the RANDBETWEEN function** to generate random numbers without duplicates in Excel. We will create **10** random numbers in our datasheet and the numbers will be in the range of cells **B5:B14**. The steps of this process are given as follows:

**📌 Steps:**

- First of all, select cell
**B5**.

- Now, write down the following formula in cell
**B5**.

`=RANDBETWEEN(10,50) `

- Press the
**Enter**key on your keyboard.

- Then, drag the
**Fill Handle**icon with your mouse up to cell**B14**.

- You will get 10 random numbers.

- To ensure there are no duplicates, you can
**apply conditional formatting**to check. - The values we get from this function can change at any time. To protect those numbers select the entire range of cells
**B5:B14**and press**‘Ctrl+C’**on your keyboard.

- After that,
**right-click**on your mouse and select the**Paste Value**option.

- The numbers will not change anymore.

Thus, we can say that our method worked perfectly.

**Read More:** **Excel Formula to Generate Random Number (5 examples)**

## 2. Combining INDEX, UNIQUE and RANDARRAY Functions to Get Random Numbers

In this method, we will use the **INDEX****, ****UNIQUE****, ****RANDARRAY****, **and **SEQUENCE** functions to generate random numbers without duplicates in Excel. The number of random numbers will be as same as in the last process and the numbers will be in the range of cells **B5:B14**. The steps of this method are given as follows:

**📌 Steps:**

- First, select cell
**B5**.

- Write down the following formula in cell
**B5**.

`=INDEX(UNIQUE(RANDARRAY(30,1,10,50,TRUE)),SEQUENCE(10)) `

- Now, press the
**Enter**key.

- You will get the 10 random numbers.

- You can apply
**conditional formatting**to find out if any duplicate numbers still remain. - The values we get from this function can change after a certain period. To protect those numbers select the entire range of cells
**B5:B14**and press**‘Ctrl+C’**on your keyboard.

- After that,
**right-click**on your mouse and select the**Paste Value**option.

- The numbers will not change anymore.

So, we can say that our formula worked effectively.

**🔍 Breakdown of the Formula**

We are doing this breakdown for cell **B5**.

`👉`

**SEQUENCE(10): **This function returns **10** sequential numbers from **1-10**.

`👉`

**RANDARRAY(30,1,10,50,TRUE)**: This function returns **30** random numbers between **10 to 50**.

`👉`

**UNIQUE(RANDARRAY(30,1,10,50,TRUE))**: This function filters the unique value which is got from the **RANDARRAY** function..

`👉`

**INDEX(UNIQUE(RANDARRAY(30,1,10,50,TRUE)), SEQUENCE(10))**: This returns the first **10** unique value and show them inthe range od cells **B5:B14**.

## 3. Generating Random Numbers in Excel Without Duplicates Using RAND Function

We are going to use **the RAND function** to generate random numbers in Excel without duplicates. In this case, we get 10 decimal values between 0 and 1. The numbers will be in the range of cells **B5:B14**. The procedure is explained below:

**📌 Steps:**

- Select cell
**B5**. - Now, write down the following formula in cell
**B5**.

`=RAND()`

- Press the
**Enter**key.

- Format the number according to your desire. Here, we choose 2 digits after the decimal point. Then, drag the
**Fill Handle**icon with your mouse up to cell**B14**.

- Thus, you get 10 random numbers between 0 and 1.

- You can also apply
**conditional formatting**to see if any duplicate number is still trapped in the dataset. - The values we get from this function can change at any time. To protect those numbers select the entire range of cells
**B5:B14**and press**‘Ctrl+C’**on your keyboard.

- Then,
**right-click**on your mouse and select the**Paste Value**option.

- The numbers will not change anymore.

In the end, we can say that our formula worked effectively.

**Similar Readings**

**Random Number Generator with Data Analysis Tool and Functions in Excel****Random 5 Digit Number Generator in Excel (7 Examples)****Generate Random Number from List in Excel (4 Ways)****Random 4 Digit Number Generator in Excel (8 Examples)****Random Number Generator between Range in Excel (8 Examples)**

## 4. Inserting SEQUENCE Function to Get Random Numbers Without Duplicates

In this case, **the SEQUENCE function** will help us to generate random numbers without duplicates. We will create **10** random numbers in our datasheet and the function will provide us with 10 numbers of equal intervals. The numbers will be in the range of cells **B5:B14**. The process is described below step by step:

**📌 Steps:**

- At the beginning of this process, select cell
**B5**. - After that, write down the following formula in cell
**B5**.

`=SEQUENCE(10,1,10,3)`

- Press the
**Enter**key on your keyboard.

- You will get 10 random numbers.

- In this process, you don’t need any necessity to apply
**conditional formatting**. Because the**SEQUENCE**function will provide us the number of equal intervals. Thus, there is no possibility of duplicate numbers in our Excel datasheet.

Finally, we can say that our function worked perfectly.

**Read More:** **Random Number Generator in Excel with No Repeats (9 Methods)**

## 5. Joining RANDARRAY and UNIQUE Functions to Get Random Numbers

In this method, we will use the **UNIQUE** and **RANDARRAY **functions to generate random numbers in an Excel sheet without duplicates. The numbers will be in the range of cells **B5:B14 **in a quantity of 10 numbers. The process is demonstrated below:

**📌 Steps:**

- For starting the procedure first select cell
**B5**.

- Write down the following formula in cell
**B5**.

`=UNIQUE(RANDARRAY(10,1,10,50,TRUE))`

- Then, press the
**Enter**key on your keyboard and you will get 10 random numbers.

- For checking the duplicate number, you can apply
**conditional formatting**to find them. - The values we get from this function will change when you reopen the file. To protect those numbers select the entire range of cells
**B5:B14**and press**‘Ctrl+C’**on your keyboard.

- After that,
**right-click**on your mouse and select the**Paste Value**option.

- The numbers will not change anymore.

At last, we can say that our method and formula worked successfully.

**🔍 Breakdown of the Formula**

We are doing this breakdown for cell **B5**.

`👉`

**RANDARRAY(30,1,10,50,TRUE)**: This function returns **30** random numbers between **10 to 50**.

`👉`

**UNIQUE(RANDARRAY(30,1,10,50, TRUE))**: This function filters the unique value which is got from the **RANDARRAY** function and shows them in the range of cells **B4:B14**.

## 6. Generating Random Numbers Without Duplicates by Applying SORTBY Function

This procedure will be complete with the help of the **SORTBY**, **SEQUENCE**, and **RANDARRAY **functions. We will generate 10 random numbers without duplicates in our Excel datasheet. The numbers will be in the range of cells **B5:B14**. The steps of this procedure are given as follows:

**📌 Steps:**

- First of all, select cell
**B5**.

- Then, write down the following formula in cell
**B5**.

`=SORTBY(SEQUENCE(10,1,10,3),RANDARRAY(10)) `

- Press
**Enter**and you will get 10 random numbers.

- Now, to ensure that our dataset is free from duplicates, you can apply
**conditional formatting.** - The values we get from this function will change when you reopen the file. To protect those numbers select the entire range of cells
**B5:B14**and press**‘Ctrl+C’**on your keyboard.

- Then,
**right-click**on your mouse and select the**Paste Value**option.

- Thus, the numbers will not change anymore.

So, we can say that our method and formula worked perfectly.

**🔍 Breakdown of the Formula**

We are doing this breakdown for cell **B5**.

`👉`

**SEQUENCE(10,1,10,3)**: This function returns **10** equally step values from **10 to 37** with an interval of **3**.

** 👉 RANDARRAY(10)**: This function returns

**10**Decimal values between

**0 to 1**.

`👉`

**SORTBY(SEQUENCE(10,1,10,3), RANDARRAY(10))** This returns the random values which are got from the other function and show them in the range of cells **B4:B14**.

## 7. Getting Random Numbers with RAND and RANK Functions Ignoring Duplicates

In the following method, we are going to use the **RAND** and **RANK** functions to generate random numbers without duplicates. In this process, we will get 2 different datasets of **10** random numbers. The first set will be the decimal numbers, while the second set will be the Integer Numbers. The numbers will be in the range of cells **B5:C14**. The steps of this method are given as follows:

**📌 Steps:**

- Select cell
**B5**and write down the following formula in cell**B5**.

`=RAND() `

- Now, press the
**Enter**key on your keyboard.

- Drag the
**Fill Handle**icon with your mouse up to cell**B14**.

- You will get 10
**random numbers between 0 and 1.**

- Then, select cell
**C5**. Similarly, write down the following formula into the cell and press**Enter**.

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

- Drag the
**Fill Handle**icon with your mouse up to cell**B14**. Or simply**double-click**on it. - Now, you will get the second dataset which is actually showing the position of the previous dataset’s number.

- For duplicates apply
**conditional formatting**to confirm any of them is not remain, - You can see that the values we get from this function change continuously with our every step. To protect those numbers select the entire range of cells
**B5:C14**and press**‘Ctrl+C’**on your keyboard.

- Then,
**right-click**on your mouse and select the**Paste Value**option.

- The numbers will not change anymore.

Finally, we can say that our method and formula worked successfully and are able to generate random numbers in Excel without duplicates.

**Read More:** **Excel VBA: Random Number Generator with No Duplicates (4 Examples)**

**Download Practice Workbook**

Download this practice workbook for practice while you are reading this article.

## Conclusion

That is the end of this article. I hope that the context will be helpful for you and you will be able to generate random numbers in Excel without duplicates. If you have any further queries or recommendations, please share them with us in the comments section below.

Don’t forget to check our website **ExcelDemy** for several Excel-related problems and solutions. Keep learning new methods and keep growing!

**Related Articles**

**How to Generate Random 10 Digit Number in Excel (6 Methods)****Generate Random Number in Excel with Decimals (3 Methods)****How to Generate Random Number in a Range with Excel VBA****Random Number Generator with Normal Distribution in Excel (4 Methods)****Generate Random Number with Mean and Standard Deviation in Excel****Auto Generate Invoice Number in Excel (with 4 Quick Steps)**

=UNIQUE(RANDARRAY(10,1,10,50,TRUE)) does not always return 10 numbers. Only those that are unique. Duplicates are removed do i often get 8 or 9 numbers.

Hi Tim,

Thanks for your query. You are right. The combination of UNIQUE and RANDARRAY functions sometimes provide us with less than 10 number due to the duplicates. But, most of the time, it shows the 10 unique numbers on your first attempt. So, if you get less than 10 values, delete them and input the formula again. Once you get the 10 numbers, please copy and paste them in Value format asap to terminate further modification.

Moreover, you can also look to our other methods if your dataset provides you with such flexibility to use them.