How to Generate Unique Random Numbers in Excel (No Duplicates)

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 (i.e., there are no duplicates).

My first instinct was to use the RANDBETWEEN function.

I did a quick check with some random data in Excel, and it failed me – there were repetitions in the results.

Here is what I got when I used the RANDBETWEEN function:

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 generate random numbers with no duplicates?

Let me know in the comments section.

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

16 thoughts on “How to Generate Unique Random Numbers in Excel (No Duplicates)”

  1. I think RANK now contains a tiebreaker argument so you can guarantee a perfect set of unique numbers with your method.

  2. Only way I found out to do this with zero chance of it repeating was with VBA…your solution probably will work if you’re only doing 10 or something like that.

    If it doesn’t, you need to generate a random number, fill it in to the array, and for each next random figure you generate, check to see if it matches any figure in the array; if it does, generate a new number, if it doesn’t, store it and go to the next in the array.

  3. To me, this is a very elegant solution that should work well more than 99% of the time. Thanks!

  4. Hello, I need to know, how I can pick random unique no. in my excel. e.g. I have 2000 account and I need 100 account randomly. When I click one button. Highly appreciate your prompt response.

    • You want to select random records with a click. That would need VBA. Another way could be use randbetween function in a column and then sort the data based on it. Then you can select first 100 records and those would be random.

  5. Another way to do this is join the result of 2/3 of function’s result using “&” sign. This will give more probability of getting unique numbers.

  6. Sir, i have 1 to 10 numbers in column “A” as A1:A10. Then i want 1 number in B1 in one click, then 2 number in B1 in next click, then 3, then 4,…..then 10. But after 10 i want 1 numer in next click.
    Is it possible in excel please reply me.

    • Hi Bhimrao,

      try this macro (leave first cell blank (means take A2:A11 rather A1:10) and select B2 cell and run the macro)

      Sub SerNum3()

      If ActiveCell.Offset(-1, 0).Value < 10 Then

      ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1

      ActiveCell.Offset(1, 0).Select

      Else

      ActiveCell.Value = 1

      ActiveCell.Offset(1, 0).Select

      End If

      End Sub

      Regards
      Hareesh

      • Dear Sir, you give me answer but when we run macro then only in B2 cell
        I have 1, then 2, then 3,…..then 10
        and after 10 i have reapet from 1, then 2, then 3,…..then 10
        again 1 to 10
        again 1 to 10 only in B2 cell

        • Hi Bhimrao,

          what i understood is “for every click excel has to display 1 to 10 numbers and after 10 it is again 1,2,3……..10.

          when i checked with same macro it is running as said above.

          please send a excel sheet (explaining u r requirement) to my mail “hareesh.nalubolu@gmail.com
          i will try to get the answer

          regards
          Hareesh

  7. This is how I do it (same principle)

    1. Fill a range of cells with ascending numbers (unique for example: 1,2,3,4…)

    2.The fill an adjacent range of cells with randomly generated numbers using RANDBETWEEN

    3. Sort the entire range by the second column of random numbers. You will now have unique random numbers.

  8. There is a chance that the RAND numbers will contain a repetition. Thus their RANK would repeat.
    Regards
    Brian

    • Hi Brian.. It should work fine on small data sets. I did some testing on large data sets as well and it came clean. But yes, as I mentioned in the article, there is still a chance, and it is good to check it once

      • My trick is to put the numbers you want to randomize in column A put the rand function beside them in column B then sort by column B.. Even if there might be a duplicate inColumn b column a will have none.

        • Good trick.. Thanks for sharing. It is a fine approach when you want the random numbers once. If you need to randomize again, you will have to sort it again.

Comments are closed.