How to Create a List of Random Numbers with No Duplicates or Repeats

Bottom line: Learn how to create a list of unique random numbers with no duplicates or repeats.

Skill level: Intermediate

How to Create a Random List of Numbers Without Duplicates in Excel

My friend and fellow blogger, George Mount, posted a question on Facebook yesterday about creating a list of random non-repeating numbers.

Unfortunately, there is no function built into Excel that will create a list of unique random whole numbers (see update below).  We can use the RANDBETWEEN function to create random numbers between a certain range, say 1 and 100, but there is nothing in the function to prevent duplicate numbers from being created in the range.

Update January 20, 2020: We can now solve this with a formula using a few of the new Dynamic Array Functions, SORTBY, SEQUENCE, and RANDARRAY.  I have added a section below that explains the formula.  You will need to be on the latest version of Office 365 for this to work.  Otherwise you can use the other “old method” explained in the post below.

The “New” Dynamic Way

Microsoft has released a new set of Dynamic Array Functions that allow us to return multiple results to a range of cells (spill range).  Check out my article and video on the Dynamic Array Formulas to learn more. 

Note: you will need the latest version of Office 365 for this method to work.  If you don't have that then see the other options below. You can skip down to the “old” way if you are not on Office 365 yet.

These functions allow us to create the entire random list from one formula.  Here is the formula for a list of the numbers 1 to 10 in random order.

=SORTBY(SEQUENCE(10),RANDARRAY(10))

Create List of Numbers in Random Order with Dynamic Array Functions in Excel

The formulas uses three of the new Dynamic Array Functions.  Let's take a look from the inside out to better understand the formula.

The SEQUENCE Function

The SEQUENCE Function returns a list (array) of numbers.  The function has 4 arguments and the last 3 are optional.

=SEQUENCE(rows,[columns],[start],[step])

SEQUENCE Function to Return List of Numbers in Excel

For this formula we just want a list of numbers from 1 to 10.  So we only need to specify a 10 for the rows argument.  The start and step default to 1, which means we do not need to specify them.  We want to start the list at 1 and step (increment) by 1.  

Note: If you don't have the SEQUENCE function then you are not on a version of Excel that supports dynamic arrays.   You can skip down to the “old” way section if you'd like.

Now that we have our list of numbers, we need to sort it in random order.

The RANDARRAY Function

The RANDARRAY function returns a list (array) of random numbers.  The function has 5 optional arguments.

=RANDARRAY([rows],[columns],[min],[max],[integer])

RANDARRAY Function to Return List of Random Numbers in Excel

In this case we are only specifying a 10 for the rows argument, which will be the amount of numbers in our list.  Here is what the result looks like.

The SORTBY Function

The SORTBY function sorts a range/array based on another range/array.  The function has a minimum of 2 required arguments and additional optional arguments to sort by multiple columns/arrays.

=SORTBY(array,by_array,[sort_order1],...)

SORTBY Returns a List Sorted by Another List Range in Excel for List of Random Numbers

For the array argument we will use the SEQUENCE formula because we want to sort the list of numbers that SEQUENCE creates.

For the by_array function we will use the RANDARRAY function to sort the list in random order.

Important Notes

RANDARRAY is a volatile function and will recalculate every time the workbook is changed or recalculated.  Therefore, you might want to copy & paste values on the spill range if you just want to create the random list and never have it change.

If you need more numbers then you just change the 10 for both functions.  You can also add the start and step arguments in SEQUENCE if you want to start at a specific number and increment by a specific amount.  Here is the formula to generate a random list of 20 even numbers starting at 0.  

=SORTBY(SEQUENCE(20,,0,2),RANDARRAY(20))

The 2 in the step argument will increment by 2, giving us even numbers.

The “Old” Way to Create a Random List

If you are not on Office 365 yet, then you can use the following technique to create the random list of uniques.

We can do this by creating a list of numbers, adding a column of random numbers with the RAND function, and sorting the column.  Here is a quick screencast that shows how to create the random list.

How to Create a List of Unique Random Numbers in Excel

Checkout my posts on shortcuts for Ctrl+Enter and the Fill Handle to learn some of the fill techniques I used in the screencast above.

Here are detailed instructions on how to create the randomly sorted list of numbers.

Step 1: Create a list of numbers

The first step is to create a list of numbers in sequential order.  This can be 1 to 10, or 1 to 1,000,000, or anything in between.  We can use the Fill Handle to quickly create this list in a column.

We can also use the Fill Series menu to create the list by specifying the stop value (upper limit).

Create List of Numbers with Fill Series in Excel

The Fill Series tool is much faster than dragging the fill handle down hundreds or thousands of rows.

Step 2: Create list of random numbers

We now need to create another column of random numbers.  We can use either the RAND or RANDBETWEEN functions for this.  The RAND function is the fastest because we don't have to specify any arguments.  The RAND function returns a random decimal number to the cell.

Excel RAND Function for Random List of Unique Numbers

Input the formula =RAND() in the first cell and double-click the fill handle to copy the formula down.

Step 3: Sort the column of random numbers

The last step is to apply filters to both columns and sort the column that contains the random numbers.  Since this column is random, the sort order applied to the first column will be completely random.  The first column now contains a list of unique numbers in random order.

List of Unique Numbers Sorted in Random Order

The RAND & RANDBETWEEN Functions are Volatile

It's important to note that both the RAND & RANDBETWEEN functions are volatile.  This means they recalculate every time Excel calculates, regardless of any formula dependencies.

RAND Function Volatile Recalculates on Sort - Random Number List

So, each cell that contains a random function will generate a new random number every time you make a change to the workbook.  This includes applying the Sort to the range.  To prevent this, we can copy/paste values on the column of random numbers after copying down the formula.

Sort Names or Text in Random Order

This same technique can be used to sort lists of names or text in random order.  In the following example we have a list of names in alphabetical order.

We can add random numbers in column B using the RAND function, then sort the range by column B.  This will put the names in column A in random order.

Sort List of Names or Text in Random Order with RAND

I wish my teachers knew about this when I was in school.  With the last name of Acampora, I always had to sit in the front row of the classroom and was picked first for class presentations… 🙂

Other Ways to Create a Unique List of Random Numbers

My friend Oz du Soleil from Data Scopic had a great suggestion for creating a list of random numbers when you do NOT need to include every number in the range.  For example, let's say we need a list of 100 random numbers between 1 and 1,000.

Create List of Unique Numbers from Larger Range

His suggestion was to use the RANDBETWEEN function to create a long list of random numbers.  

  1. Fill the formula to more than 100 cells.
  2. Then copy/paste values.  
  3. Then use the Remove Duplicates feature to remove any possible duplicates that are generated by RANDBETWEEN.

That will leave us with a long list of unique random numbers.  We can then delete an extra cells that we do not need.  Maybe Oz can create one of his awesome Excel on Fire YouTube videos to explain this technique in more detail.

What methods do you use to create lists of random numbers?  Please leave a comment below with any suggestions or questions.  Thank you! 🙂

59 comments

Your email address will not be published. Required fields are marked *

  • Not sure if this comment is timely due to the date on the article, but I saw at least one person commenting recently.

    There is a problem (omission) with the last method in the article. That problem is that the RANDBETWEEN function is volatile and will recalculate when any change takes place in the sheet. So, when you remove duplicates, the RANDBETWEEN recalculates, generating another set of random numbers with potential duplicates. For example, I generated a list of 1000 random numbers, then removed duplicates. It removed 372 duplicate instances, but then recalculated a list with 160 duplicates.
    You must copy paste VALUES from your RANDBETWEEN column to “freeze” the randomization. Then you can remove duplicates from this frozen data. (Or turn off calculation before removing the duplicates … but you are going to need “values” data sooner or later or the recalculation will come back at you.)

    • Hi Kevin,
      Thank you for highlighting that potential issue. The article did mention copy/paste values, but it was a bit buried. I reformatted the paragraph to make it stand out more.

      I also added a section with a solution using the new Dynamic Array Functions.

      I hope that helps. Thanks again and have a nice weekend!

  • Hello, how to setup random number generator and ask to avoid using previous and certain numbers I add in future ? Like I have list of numbers I don’t want to use in future need to setup the number generator to give different numbers.

    • One method would be to test your new set of numbers with a VLOOKUP to a list of previously used numbers. If you get a hit and not a #N/A result, then you have a number in your new set of numbers that was used before and can use that result to further cull your list of new numbers.

    • Yes, that is correct. However, we are just using RAND for the column to sort by. Therefore, duplicate numbers in the rand column won’t affect the results. The duplicate rand numbers would just be listed next to each other in the sort column. The actual numbers we are sorting would still be in random order.

      The only way that would fail is if RAND produced all duplicates. And I believe the chance of that is very small if not impossible.

      I hope that helps. Thanks again and have a nice weekend!

  • Hello I’m trying to do a project but I need a number generator the generates multiple numbers that don’t have any repeating numbers in the number and I want no repeating generated numbers do you get what I’m saying if so can you try to do it for me thanks!

  • Hi Jon,

    Thank you for your great article.
    I wonder how you make the red progress bar at the bottom of each gif picture?
    Thank you.

  • Hi Jon,
    I am facing a problem in generating random number, for example randbetween(1,412) and i want to get 101 random numbers. But problem is repeating the numbers. So how I tackle this problem?
    Thanking in Anticipation.

    • Thanks a lot for the helpful article. It is also my belief that mesothelioma cancer has an extremely long latency phase, which means that the signs of the disease may well not emerge until eventually 30 to 50 years after the original exposure to mesothelioma. Pleural mesothelioma, which can be the most common style and has an effect on the area around the lungs, could cause shortness of breath, chest pains, including a persistent coughing, which may result in coughing up bloodstream.

  • Hello
    I was wondering if you could help me with a similar problem:
    I have a list of numbers from A1:A100. Now I would like to repeat the SAME 100 numbers with a different (random) order 1000 times (some kind of simulation) in column B, C, ….. every row being random but using all of the 100 numbers from A1:A100.
    (in case the info is needed: the numbers are some kind of returns varying between (0;0,5) & (-1) appearing once in a while).
    Thanks in advance.
    Marko

  • My Q is i have 60 teams and i would like to randomize the teams into 20 folds each 60 times with a team not repeating it self with in the same folds.i need the formula to use in excel for that purpose.can u derive a formula for me.THANKS AND HOPE TO HEAR FROM U SOON.

  • This solution was the product of a heavily constrained working practice.
    1. Avoid all direct referencing in a solution. Use only defined names or structured references;
    2. Avoid relative referencing other than within a Table. Use array formulas if possible;
    3. Avoid the use of helper ranges unless they contain information meaningful to the end user.

    Within those constraints, I developed a solution that examined the previous selections and made the new one in the light of that knowledge. The final formula is deceptively simple:
    permutation: = MATCH( random, bins, 0 ) [even the result range is named]

    The name ‘random’ was simply a random number guiding the next selection
    random: = RANDBETWEEN( 1, 7-p ) [sequence of random numbers for each play]
    whereas ‘bins’ is a sequence of nested ranges accumulating the growing list of prior selections
    bins: = k – COUNTIFS( priorValues, “<="&k )

    The core of the method is the definition of this sequence of ranges
    priorValues: = OFFSET(permutation,0,-1,1,p)

    It is the fact that p is an array constant that produces a sequence of ranges.
    p: ={1,2,3,4,5,6} [index, used to extract prior values and to generate random numbers]

    The other index column 'k' is used to select the object returned
    k: ={1;2;3;4;5;6} [option index]

    If you substituted for all the names you would eventually get a single nested formula but it most likely would be horrible. This approach based upon named formulas allows the solution to be built step by step almost programmatically.

  • I would like to randomize non sequential numbers with a range from 1 through to 40. Example: 1, 2, 4,7,8,9,11,14,17 …..40. Twenty four numbers in total.
    Randomize in groups of 6 without duplicating a number in a group.
    This is for training drills in 7 different applications ensuring each application has four new groups of 6 every time to ensure fairness.

    Herbert

  • Another way to create a list of non-repetitive random numbers by using your first example:

    Enter in C2 the following formula

    =INDEX($A$2:$A$11,MATCH(LARGE($B$2:$B$11,ROW()-1),$B$2:$B$11,0))

    and fill down to C11. We may also use the function SMALL instead of LARGE. Then press DEL repeatedly in any empty cell. Voilà!

    We may also put any text in the range A2:A11 to create a list of non-repetitive random texts.

  • Another way to create a list of non-repetitive random numbers by using your first example:

    Enter in C2 the following formula

    =INDEX($A$2:$A$11,MATCH(LARGE($B$2:$B$11,ROW()-1),$B$2:$B$11,0))

    and fill down to C11. We may also use the function SMALL instead of LARGE. Then press DEL repeatedly in any empty cell. Voilà!

    We may also put any text in the range A2:A11 to create a list of non-repetitive random texts.

  • Thanks though one important question for me…

    I dearly want to do this but I do not want it to be a decimal. Is there an easy way to convert it to a whole number with no decimal or best to create whole numbers to begin with?

  • Thanks Jon, your posts are always helpful.

    There is something I was hoping you could help me with.

    I have been trying to find out how a total beginner can start the journey of becoming an MCT. The website shows a lot of details but I find them very confusing.

    I will be grateful if you could help.

    – Afreen

  • I use the INT() function in combination with the RANDBETWEEN() function to create a list of random whole numbers (e.g. =int(randbetween(20,100)), then I copy/paste values in another range, so I have the original random numbers and finally I use the remove duplicates from the Data Tab => Data Tools Group.

  • In the seventies of the last century Excel was not available. I used FORTRAN. For a simulation program I needed to produce many series of unique random numbers of 1 to N. I wrote a subroutine to get these series. First, an array would be filled with the numbers 1, 2, …, N. The number of remaining random numbers M would be set to N. The number of random numbers, lets call it J, would be set to 1. Then I would draw an integer random number between 1 and M. Lets call it I. The I-th number of the first array would be entered into the J-th location of the output array. The M-th number of the first array would be copied into the I-th position of the first array. I := I+1, J:=J+1, M:=M-1 and the above would be repeated in a loop until M=0. The output array would then contain the numbers 1, 2, …, N in random order. No checking on duplicates, no checking on missing numbers, and no sorting needed. It was an extremely fast method. To imitate shuffling a card deck, you would use N=52.

    You can easily write an Excel macro doing the same.

    • Haha! I totally agree. It’s a great little feature that I often forget about too. Here is the MSDN Help Page that explains more about each option in the menu. The info about it towards the bottom of the page in the section titled, “Fill cells with a series by using the Fill command”.

      Here is an explanation of each Type from that page:

      • Linear – Creates a series that is calculated by adding the value in the Step value box to each cell value in turn.
      • Growth – Creates a series that is calculated by multiplying the value in the Step value box by each cell value in turn.
      • Date – Creates a series that fills date values incrementally by the value in the Step value box and is dependent on the unit specified under Date unit.
      • AutoFill – Creates a series that produces the same results as dragging the fill handle.

      That page also contains a few examples of different types of data you can fill. I’ll do a follow-up post on this in the future as well. Thanks Ryan! 🙂

  • There might be a solution using Goal Seek together with a helper column next to the column in which the unique random numbers are entered. The helper column uses CountIfs function to find any duplicates. The sum of the helper column must equal the number of unique values, finally.

    Goal Seek uses the sum cell of the helper column as the “Set Cell”. The “To value” is the number of unique values needed. The “By Changing Cell” can be any empty cell in the worksheet.

    With 100 random values I got it managed within two or three trials. I increased the number iterations to 1000. So lees trails were needed.

    Quite a workaround but still having some automatic calculations.

    Cheers Michael

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly