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

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))

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])

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])

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],...)

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.

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

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.

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.

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.

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.

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.

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! 🙂