Sequence Excel Without Duplicates: Formulas & Tips

To create a duplicate-free sequence in Excel for Microsoft 365, enter =SEQUENCE(10) in an empty cell; it spills the numbers 1 through 10 down the sheet. SEQUENCE makes distinct values when its step is nonzero. If you mean choosing random numbers without repeats, use a shuffled sequence instead, as explained below.

How SEQUENCE avoids duplicates

SEQUENCE generates values from a starting number by repeatedly adding a step. Its syntax is SEQUENCE(rows,[columns],[start],[step]); the brackets indicate optional arguments, not characters to type. If you provide only the number of rows, Excel starts at 1, uses one column, and increases by 1 each row. A consistent, nonzero step makes each generated value different from the previous one.

For example, with an empty cell such as A1 selected, =SEQUENCE(10) returns 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10 in A1:A10. The formula occupies only A1; the other results are its spilled array. It does not search existing cells or compare the results against another list. It simply generates a predictable progression.

A zero step is the important exception: it repeats the starting value. A negative, nonzero step counts down without repeating, and a fractional step can also produce distinct values. If the step is very small relative to a very large starting value, Excel’s numeric precision or the cell’s number format may make results appear identical. That is different from SEQUENCE deliberately producing duplicate values.

Set the size, start, and step

Use the optional arguments to specify how many results to generate, how they are arranged, and what values they contain. With an empty cell selected and room for the result to spill, =SEQUENCE(5,1,3,2) returns 3, 5, 7, 9, and 11 in one column. The arithmetic is 3 plus four increments of 2, so there are five values and none repeats.

The first argument is the row count; the second is the column count. If you want a two-dimensional sequence, enter =SEQUENCE(3,4) in an empty cell. It returns a three-row-by-four-column array: the first row is 1 to 4, the second is 5 to 8, and the third is 9 to 12. Each value appears once because Excel continues the same progression across the array.

Rows and columns describe the dimensions of the output, not a starting and ending number. For example, a sequence of 20 values can be arranged as 20 rows by one column or as 4 rows by 5 columns. Decide on the layout your next step needs before entering the formula, and make sure the full output area is clear.

Choose the right method for your goal

People searching for a sequence without duplicates may mean different things. The right formula depends on whether the values should be orderly, randomly selected, or taken from existing data:

Consecutive or evenly spaced values: Use SEQUENCE with a nonzero step. This is the simplest choice for serial numbers, dates represented as numbers, or a planned series.
A random selection with no repeats: Shuffle a complete set of possible values, then return only the required number of items.
Remove repeated entries from a list: Use UNIQUE to return the distinct values already present in a range. This does not create a numeric sequence.
Values that must follow a special rule: Build or filter a set of eligible values first, then select from that set. A basic SEQUENCE formula does not know about rules such as excluding weekends or values already assigned elsewhere.

These methods solve related but different problems. In particular, SEQUENCE does not guarantee uniqueness against values that already exist outside its spill range. If the new results must not overlap a prior list, account for that list when defining the candidates rather than assuming SEQUENCE checks it automatically.

Generate random numbers without repeats

For a random draw, arrange the lower limit in B1, the upper limit in B2, and the number of values wanted in B3. For example, enter 1, 20, and 5 in those cells. In an empty output cell, use =INDEX(SORTBY(SEQUENCE(B2-B1+1,,B1),RANDARRAY(B2-B1+1)),SEQUENCE(B3)). The formula creates every integer from the lower limit to the upper limit, assigns each a random sort value, and returns the first requested items after sorting.

With the example inputs, the candidate set contains 20 integers because 20 minus 1 plus 1 equals 20. The result is five distinct integers from 1 through 20; the exact values can change whenever Excel recalculates, so there is no single fixed result to promise. Since the formula shuffles the candidates rather than drawing each number independently, it cannot select the same candidate twice in one result.

Keep the requested count in B3 at or below the number of available integers. If B1 is 1 and B2 is 20, the count can be no more than 20. The formula also assumes the lower and upper limits are whole numbers in ascending order. It uses SORTBY, RANDARRAY, INDEX, and SEQUENCE, which are available in Microsoft 365 and Excel 2021 or later. Random values generated this way are suitable for ordinary worksheet tasks, not security-sensitive lotteries or encryption.

RANDARRAY is volatile: recalculation can reshuffle the results. If you need to keep a draw, copy the spilled results and use Home > Paste > Paste Values in a separate destination. The pasted values no longer change with recalculation. Be aware that other volatile formulas or workbook calculation can cause a reshuffle before you preserve the results.

Remove duplicates from an existing list

If the goal is to make a list unique rather than generate new numbers, use UNIQUE. With source values in A2:A100 and an empty output cell, enter =UNIQUE(A2:A100). Excel returns each distinct value once, in the order of its first appearance. For example, if the source contains 4, 7, 4, and 9, the result is 4, 7, 9. This is a filtered list of existing values, not a count-up sequence.

UNIQUE is useful for names, categories, product codes, and other data where duplicates may be scattered through a column. If you want the distinct results in ascending order, you can wrap the formula in SORT. With the same source range and a clear output area, =SORT(UNIQUE(A2:A100)) returns the distinct values sorted in ascending order for ordinary numbers or alphabetically for text. Both functions are available in Microsoft 365 and Excel 2021 or later.

Blanks and inconsistent data can affect the output. A blank cell may be treated as a distinct blank result, while text that looks like a number may not behave like a numeric value. Extra spaces can also make two names appear identical to a person while Excel treats them as different text. Clean or normalize the source data first if those distinctions matter.

Spill behavior and common problems

SEQUENCE and the other dynamic-array formulas need an unobstructed area for their results. If a cell in the intended spill range already contains data, Excel displays a #SPILL! error rather than overwriting that content. Clear the obstructing cells, move the formula, or reduce the requested dimensions. A merged cell in the output area can also prevent a spill.

Enter a dynamic-array formula in a regular worksheet cell, not inside an Excel table when it needs to spill across multiple cells. Tables are useful for structured data, but spilled array results should be placed outside the table. You can still reference table columns in formulas, then place the result in a clear worksheet area.

If values seem duplicated, check the step argument first: zero repeats the start. Then inspect the actual values in the formula bar or increase the displayed decimal places; a number format that shows only whole numbers can make different fractional values look the same. Also check whether you are looking at a random formula that has recalculated, or at a separate source list that contains duplicates.

Formula errors can also result from invalid dimensions or a spill range that extends beyond the worksheet. SEQUENCE needs positive row and column counts. If you request a large array, check that enough rows and columns remain available from the formula cell. Do not delete individual cells in the middle of a spill range; change the formula in its top-left cell instead.

Excel version and platform notes

SEQUENCE, UNIQUE, SORT, SORTBY, and RANDARRAY are dynamic-array functions available in Microsoft 365 and Excel 2021 or later. In older perpetual versions, a formula may display as an unrecognized function, and dynamic spilling is not available in the same way. If a workbook will be opened in older Excel, consider creating and saving fixed values or using older-compatible methods instead of relying on a dynamic-array formula.

The formulas and ribbon instructions here are for Excel for Microsoft 365 on Windows. The formulas also work in supported Microsoft 365 and Excel 2021 releases on Mac, though keyboard shortcuts and some interface details can differ. Excel for the web supports many dynamic-array formulas too, but its menus and workbook behavior may differ from the Windows desktop app. If a result must be shared, confirm that the recipient’s Excel version supports the functions used.

Frequently Asked Questions

Does SEQUENCE in Excel create duplicates? With a nonzero step, it generates a progression of distinct values. If the step is zero, it repeats the starting value. It does not check whether generated values already occur elsewhere in the workbook.

How do I create a random number list without repeats in Excel? Create the complete range of eligible integers, randomize their order with SORTBY and RANDARRAY, then select the desired number with INDEX and SEQUENCE. The number requested cannot exceed the number of eligible values.

How can I make a sequence start at a number other than 1? Supply the starting number as SEQUENCE’s third argument. For example, with an empty output cell, =SEQUENCE(4,1,10,1) returns 10, 11, 12, and 13.

Can UNIQUE create sequential numbers? No. UNIQUE removes repeated entries from a source array. Use SEQUENCE to generate a progression, or combine the functions only when you have a specific reason to generate values and then remove repeats.

Why does my SEQUENCE formula show #SPILL!? Excel cannot place one or more results in the required output cells. Clear the obstructing cells, unmerge cells in the spill area, move the formula, or reduce the sequence size.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.