Soumik Dutta
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.
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.