How to Generate Unique Random Numbers in Excel

I was going through the questions on the Microsoft Excel Community, and one of the questions was on generating random numbers in Excel between 1 to 10, where none of the numbers repeat.

My first instinct was to use the RANDBETWEEN function. I did a quick check in Excel, and it failed me – there were repetitions in the results.

Here is what I got:

Generate Unique Random Numbers in Excel - Using Randbetween

So I had to resort to the RAND function.

It works well with a negligible probability of the numbers repeating (I tested it multiple times on more than 100,000 cells, and there were no repetitions. If you are generating a lot of random numbers, you can test it once).

Generating a Set of Unique Random Numbers in Excel

Here is how you can use the RAND function to generate a set of unique random numbers in Excel:

  1. In a column, use =RAND() formula to generate a set of random numbers between 0 and 1.
  2. Once you have generated the random numbers, convert it into values, so that it won't recalculate again and again to make your workbook slow.Unique Random Numbers in Excel - RAND function
  3. In the adjacent column, use the following RANK formula and copy/drag for other cells (here I have used it for 10 numbers only, hence A2:A11. You can adjust the range accordingly).
    =RANK(A2,$A$2:$A$11)Unique Random Numbers in Excel - RANK function
  4. That's it. You would have a list of random numbers from 1 to 10, and none of the numbers would repeat.

NOTE: Remember to convert cells with RAND function from formula to values, else it will recalculate and change the list of Random Numbers every time you change anything in the workbook.

Caution: While I checked and didn't find repetitions in the result of the RAND function, I still recommend you check once you have generated these numbers. You can use Conditional Formatting to highlight duplicates or use the Remove Duplicate option to get rid of it.

Do you use any other technique to do this? Let me know in the comments section.

You May Also Like the Following Excel Tutorials:

Online Excel Tips Tutorials Training and Videos


51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)