How to Generate Random Numbers Without Duplicates in Excel (7 Ways)

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.


Download Practice Workbook

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


7 Easy Ways to Generate Random Numbers Without Duplicates in Excel

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.


1. Using RANDBETWEEN Function to Generate Random Numbers

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.

Using RANDBETWEEN Function to Generate Random Numbers

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

Using RANDBETWEEN Function to Generate Random Numbers

  • You will get 10 random numbers.

Using RANDBETWEEN Function to Generate 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.

Using RANDBETWEEN Function to Generate Random Numbers

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

  • The numbers will not change anymore.

Using RANDBETWEEN Function to Generate Random Numbers

Thus, we can say that our method worked perfectly.

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


2. Combining INDEX with UNIQUE and RANDARRAY Functions

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.

Utilizing INDEX Function to Generate Random Numbers Without Duplicates

  • You will get the 10 random numbers.

Utilizing INDEX Function to Generate Random Numbers Without Duplicates

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

Utilizing INDEX Function to Generate Random Numbers Without Duplicates

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

  • The numbers will not change anymore.

Utilizing INDEX Function to Generate Random Numbers Without Duplicates

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. Generate Random Numbers 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.

Generate Random Numbers Using RAND Function

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

Generate Random Numbers Using RAND Function

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

Generate Random Numbers Using RAND Function

  • 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


4. SEQUENCE Function to Generate 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.

Applying SEQUENCE Function to Generate Random Numbers Without Duplicates

  • You will get 10 random numbers.

Applying SEQUENCE Function to Generate Random Numbers Without Duplicates

  • 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. Use of RANDARRAY and UNIQUE Functions

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

Use of RANDARRAY and UNIQUE Functions to Generate Random Numbers Without Duplicates

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

Use of RANDARRAY and UNIQUE Functions to Generate Random Numbers Without Duplicates

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

Use of RANDARRAY and UNIQUE Functions to Generate Random Numbers Without Duplicates

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

  • The numbers will not change anymore.

Use of RANDARRAY and UNIQUE Functions to Generate Random Numbers Without Duplicates

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

Execute Through SORTBY Function to Generate Random Numbers Without Duplicates

  • Press Enter and you will get 10 random numbers.

Execute Through SORTBY Function to Generate Random Numbers Without Duplicates

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

Execute Through SORTBY Function to Generate Random Numbers Without Duplicates

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

  • Thus, the numbers will not change anymore.

Execute Through SORTBY Function to Generate Random Numbers Without Duplicates

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. RAND and RANK Functions to Get Random Numbers Without 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.

RAND And RANK Functions to Get-Random Numbers Without Duplicate

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

RAND And RANK Functions to Get-Random Numbers Without Duplicate

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

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

RAND And RANK Functions to Get-Random Numbers Without Duplicate

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

RAND And RANK Functions to Get-Random Numbers Without Duplicate

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

RAND And RANK Functions to Get-Random Numbers Without Duplicate

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)


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

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

2 Comments
  1. =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.

Leave a reply

ExcelDemy
Logo