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:
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:
- In a column, use =RAND() formula to generate a set of random numbers between 0 and 1.
- 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.
- 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).
- 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.
Do you use any other technique to do this? Let me know in the comments section.