ExcelInsider Team
If you’re working in Excel and need a list of random numbers without duplicates, using a basic formula like RANDBETWEEN may not be enough. It can produce repeated values, which isn’t ideal when you need unique entries for things like ID numbers, sampling, or randomized lists.
Excel offers simple solutions to create non-repeating random numbers using functions like SEQUENCE, RANDARRAY, SORTBY, INDEX. These functions help you build randomized lists with unique values. Even if you are using an older Excel version, there are manual methods you can follow.
In this article, we’ll learn different ways to generate random numbers without repeats.
Here’s how to generate random numbers in excel without repeats by combining three functions such as SEQUENCE, RANDARRAY, and SORTBY:
➤ Open your dataset in Excel.
➤ Click on cell E2, where you want to insert the random ticket number for the first participant.
➤ Type the following formula
=SORTBY(SEQUENCE(10), RANDARRAY(10))
➤ Press Enter.
➤ This will return a randomly shuffled list of ticket numbers from 1 to 10.
➤ The numbers will spill into cells E2 to E11 automatically, assigning a unique random ticket number to each buyer based on their name, location, and ticket type.
Using SORTBY, SEQUENCE, and RANDARRAY to Generate Unique Random Numbers
In the following dataset, we have a list of event ticket buyers who registered from different locations. There are five main columns: Buyer ID, Name, Location, Ticket Type, and Ticket Number. Column A holds unique Buyer IDs, Column B lists each buyer’s Name, Column C shows their Location, and Column D indicates the type of ticket they purchased such as VIP or Regular.
Column E is currently empty and will be used to generate a unique random ticket number for each buyer.
Our goal is to fill this column with non-repeating numbers using Excel formulas, ensuring each participant receives a different ticket number.
The easiest way to create non-repeating random numbers in Excel, especially if you’re using Excel 365 or Excel 2021, is by combining three functions such as SEQUENCE, RANDARRAY, and SORTBY. This method works by creating a list of numbers and then randomly shuffling their order.
In this method, we’ll generate numbers from 1 to 10 and assign them to each participant in our dataset without any duplicates.
Here’s how to do it step by step:
➤ Open your dataset in Excel.
➤ Click on cell E2, where you want to insert the random number for the first participant.
➤ Type the following formula
=SORTBY(SEQUENCE(10), RANDARRAY(10))
➤ Press Enter.
➤ This will return a randomly shuffled list of numbers from 1 to 10.
➤ The numbers will spill into cells E2 to E11 automatically, assigning a unique random ticket number to each buyer based on their name, location, and ticket type.
Create Unique Random Numbers in a Custom Range
If you want to generate non-repeating random numbers within a specific range such as between 100 and 999, this method is a great option. It combines the INDEX, UNIQUE, and RANDARRAY functions to create a custom list of unique random values.
This is especially useful when you need random numbers that are not just from 1 to 10, but from a wider range like sample codes or ID numbers.
Here’s how to apply this method step by step:
➤ Open your dataset in Excel
➤ Click on cell E2 where you want to place the first random number
➤ Type the following formula
=INDEX(UNIQUE(RANDARRAY(100, 1, 100, 999, TRUE)), SEQUENCE(10))
➤ Press Enter.
➤ This formula generates 100 random numbers between 100 and 999, filters out duplicates, and returns the first 10 unique values.
➤ The results will automatically spill from E2 to E11, giving each buyer a unique random ticket number.
Using RANDBETWEEN with Remove Duplicates (For Older Excel Versions)
If you’re using an older version of Excel that doesn’t support dynamic array functions like SEQUENCE or RANDARRAY, you can still generate non-repeating random numbers using a manual method. In that case, you can use the RANDBETWEEN function along with Excel’s built-in Remove Duplicates feature.
This method gives you more control over the range and can be useful when working with smaller lists.
Here’s how to do it step by step:
➤ Open your dataset in Excel.
➤ Click on cell E2 and enter the following formula
=RANDBETWEEN(100, 999)
➤ Press Enter.
➤ Drag the fill handle from E2 down to E11 to apply the formula to the rest of the rows.
➤ Select the range E2 to E11 and go to the Data tab and click Data Tools >> Remove Duplicates to eliminate any repeated numbers.
➤ A dialog box will appear with two options. Select the 2nd option Continue with the current selection. If you want to expand your selection then you can select the 1st option.
➤ Next, click on the Remove Duplicate button.
➤ Now, click the OK button from the next prompt.
➤ If there any duplicates remain they will be removed. Since our dataset has no duplicate values it will appear like the following image. Click OK.
➤Now, you’ll get a result in Column E with zero repeat values.
Frequently Asked Questions
How do I randomize in Excel without repeating?
To randomize numbers in Excel without repeats, you can use a combination of formulas like SORTBY(SEQUENCE(n), RANDARRAY(n)). This generates a list of unique values from 1 to n and shuffles their order.
If you need numbers within a custom range, use INDEX, UNIQUE, and RANDARRAY to pull distinct values and limit how many appear.
How to avoid repeating numbers in Excel?
To avoid repeats when generating random numbers, do not rely on RANDBETWEEN alone. Instead, use formulas that ensure uniqueness, such as SORTBY(SEQUENCE(…), RANDARRAY(…)) or remove duplicates manually if you’re using helper columns.
You can also use the UNIQUE function along with RANDARRAY to remove any duplicate values automatically.
Wrapping Up
When you need to generate random numbers in Excel without any repeats, there are several reliable methods you can use. For users with Excel 365 or Excel 2021, formulas like SORTBY, SEQUENCE, and RANDARRAY make it easy to create unique random lists instantly. If you want numbers from a specific range, combining INDEX, UNIQUE, and RANDARRAY gives you more control.
Even if you’re using an older version of Excel, you can still generate non-repeating values by using RANDBETWEEN with helper columns and removing duplicates manually.
These techniques are especially useful when working with unique identifiers, randomized testing, or sampling data. Choose the method that fits your version of Excel and copy the final results as values to keep them from changing.
