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:

  • Brian

    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

      • Ropebender

        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.

  • Harry Flashman

    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.

  • Bhimrao Rokade

    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.

    • Hareesh Nalubolu

      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

      • Bhimrao Rokade

        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

        • Hareesh Nalubolu

          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

  • শুভোজিত চন্দ্র

    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.

  • Amit Saini

    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.

  • John Schlumpf

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