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

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: Easy

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

So how else can we solve this problem…?

Sort a List of Unique Numbers in Random Order

One simple way is sort a list of numbers in random order.  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.  Fill the formula to more than 100 cells, then copy/paste values.  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! 🙂

Please share
Jon Acampora

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 33 comments
whbtomboy - July 19, 2017

i wang to know which software to make gif and edit picture

whbtomboy - July 19, 2017

i want to know which software to make a gif and comments

Adonis - June 13, 2017

Thank you

harsha547 - April 23, 2017

Thank You !!

Anastasiya Romanova - April 19, 2017

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

Enter in C2 the following formula


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.

Nayan Khandelwal - April 18, 2017

Thank you so much Sir. Your Excel tips are always useful.

Anastasiya - April 17, 2017

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

Enter in C2 the following formula


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.

Ramesh - April 17, 2017

Thanks john, very very useful tips….

Imtiaz - April 17, 2017



Dorji - April 16, 2017

Thank you Jon, I hope you are having a great time. Have a blessed Easter

Rich - April 15, 2017

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?

    Jon Acampora - April 24, 2017

    Hi Rich,
    This technique does use whole numbers. We are just using the RAND function to sort the list of whole numbers in column A. I hope that helps.

Afreen - April 14, 2017

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

Edil - April 14, 2017

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.

    Jon Acampora - April 24, 2017

    Hi Edil,

    RANDBETWEEN only creates whole numbers, so you probably do not need the INT function in that formula.

Rosa - April 14, 2017

Very nice and useful. thanks

sharanu - April 14, 2017


Gerard de Graan - April 13, 2017

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.

mohammed - April 13, 2017

Thank you sir

LSimmons - April 13, 2017


midhun - April 13, 2017


Sandeep Sengupta - April 13, 2017

Thanks Jon
The post was a wonderful example of ‘thinking out of the box’.

Ryan - April 13, 2017

Fill series, just hiding in plain sight! I will definitely explore that a bit more this week. Thanks John!

    Jon Acampora - April 13, 2017

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

Michael - April 13, 2017

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

Marci - April 13, 2017

Thanks so much Jon, love the tips for this easy to use function!!!

shveta - April 13, 2017

Thanks john for the post

shveta - April 13, 2017

Good to know something new. Thanks John

rakesh - April 13, 2017

Thank u sir, learning some different type of logic and shortcuts


Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!