Select Page

Generate a set of unique numbers in random order

To generate random numbers in Excel between 1 to 20, where none of the numbers repeat, we can use the excel function RAND().

In cell A1 type formula =RAND() and drag formula to cell A20.

excel rand function

You will get a list of random values between 0 and 1. It will change everytime you edit any cell in a worksheet, so it is recommended to select whole range and do copy/paste as values, especially if you work with large data sets.

The next step is to add the excel formula RANK to column B which will make a list of random numbers between 1 and 20 without repeating. So, in cell B1 type =RANK(A1, $A$1:$A$20) and then drag for other cells. This formula is to get 20 random numbers, which is why we used range A1:A20. If you need more numbers, you will adjust the range accordingly.

random list

After this, you will have the random list of numbers between 1 and 20 in column B.