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

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

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.

    Reply
  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.

    Reply
  3. 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.

    Reply
    • 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.

      Reply
  4. 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.

    Reply
  5. 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.

    Reply
    • 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

      Reply
      • 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

        Reply
        • 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

          Reply
  6. 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.

    Reply
    • 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

      Reply
      • 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.

        Reply

Leave a Comment

FREE EXCEL E-BOOK

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

FREE EXCEL E-BOOK

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

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

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