Excel SEQUENCE Without Gaps: Create a Continuous Number List

Yes. In Microsoft 365 or Excel 2021, enter =SEQUENCE(10) in an empty cell to create the continuous list 1 through 10, with no skipped numbers. To change the starting number or interval, use the optional start and step arguments; if gaps appear in a data list, the cause may instead be blanks, sorting, or a blocked spill range.

What “without gaps” means in Excel

A sequence without gaps is a run of consecutive values, such as 1, 2, 3, 4, 5, or a regular progression such as 10, 20, 30, 40. The word “gap” can also refer to missing numbers in a list of records—for example, invoice IDs 101, 102, 104, where 103 is absent. Those are related but different tasks: generating a new sequence is straightforward, while deciding whether existing records should be renumbered depends on what the numbers represent.

The SEQUENCE function creates an array of numbers in a requested number of rows and columns. In current Excel for Windows, it is available in Microsoft 365 and Excel 2021 or later. The result spills into neighboring cells automatically, so you enter one formula rather than filling a separate formula into every row. Excel for Mac supports the same function in versions that include it; Excel for the web also supports dynamic arrays, though its menus and keyboard shortcuts differ from Windows.

If you want a simple uninterrupted list, use SEQUENCE. If you want numbering attached to records that may be inserted, removed, or filtered, choose a method that reflects how your data is organized. The distinction matters: an automatically generated run of numbers is not necessarily a permanent or unique identifier.

How SEQUENCE works

With a blank worksheet and an empty range below A1 for the result, enter this formula in A1:

=SEQUENCE(10)

It spills vertically as 1, 2, 3, and so on through 10. The single argument specifies the number of rows. Because the other arguments are omitted, Excel starts at 1 and increases by 1. If cells in the intended spill area contain values, Excel cannot place the full result there and displays a spill error instead of silently skipping those cells.

The full syntax is SEQUENCE(rows, [columns], [start], [step]). Rows is required; the other arguments are optional. Rows and columns specify the shape of the result, start sets its first value, and step controls the difference between successive values. Step can be negative, which makes a descending sequence. Excel evaluates the result as an array, so avoid typing into cells that the array needs to occupy.

For example, with an empty five-cell vertical spill area starting at A1, this formula creates 100, 110, 120, 130, and 140:

=SEQUENCE(5,1,100,10)

There are five rows, one column, a starting value of 100, and a step of 10. The arithmetic is 100 plus four increments of 10, ending at 140. The values are evenly spaced, but they are not consecutive integers; use a step of 1 when you mean a standard counting sequence.

Choose the start, step, and direction

To create a sequence that starts somewhere other than 1, specify the start argument. To count by a different interval, specify the step too. For example, a list of odd numbers can start at 1 and increase by 2. A countdown can start at 10 and decrease by 1. Check the number of requested rows as well as the step: Excel generates that many values, even if the chosen start and step carry the result past a date, limit, or value you had in mind.

With an empty vertical output range beginning at A1, this formula returns 1, 3, 5, 7, and 9:

=SEQUENCE(5,1,1,2)

With an empty vertical output range beginning at A1, this formula returns 10, 9, 8, 7, and 6:

=SEQUENCE(5,1,10,-1)

These examples also show why “without gaps” can mean different things. The odd-number example is regular and gap-free in the odd-number series, but it skips even integers. If the requirement is every integer, use a step of 1. For sequences used in reports or labels, state the intended interval clearly before deciding that values are missing.

Make a sequence across columns or in a grid

SEQUENCE can return more than one column. With an empty 3-row-by-4-column range beginning at A1, enter:

=SEQUENCE(3,4,1,1)

The result is a three-by-four grid filled from left to right and then down: the first row is 1, 2, 3, 4; the second is 5, 6, 7, 8; and the third is 9, 10, 11, 12. This is useful for calendar layouts, numbered grids, and test data. The formula creates the specified shape, not a single long column, so check both dimensions before placing it near existing content.

For a horizontal list, set rows to 1 and set columns to the number of values required. With an empty horizontal range starting at A1, =SEQUENCE(1,5) returns 1 through 5 across the row. In practice, SEQUENCE follows the worksheet’s normal array behavior: the top-left cell holds the formula, and the other results occupy the spill range. You can change the formula at the top-left cell to resize the result.

Number records without numbering blank rows

A common related request is to show consecutive row numbers only beside records that have a value. For example, if names are in column A and some cells are blank, a simple fill-down row-number formula would number blank rows too. Instead, put a conditional numbering formula in B2 and copy it down. With records in A2:A100 and the formula beginning in B2, use:

=IF(A2="","",COUNTA($A$2:A2))

This displays nothing when the corresponding A cell is blank and otherwise counts the non-empty cells from A2 through the current row. If A2 contains a name, A3 is blank, and A4 contains another name, the results in B2:B4 are 1, blank, and 2. The numbering stays consecutive across the records rather than reflecting the worksheet row numbers. Use the fill handle or copy the formula down as far as needed.

This approach is useful for a visible record number, but it is not a fixed identifier. If a record is deleted or the data is rearranged, the numbers can change. Also, COUNTA treats a cell containing a formula that returns an empty string as non-empty, and a cell containing spaces is not truly blank. If your source column contains either, clean or adjust the source data before relying on the count.

If you simply need a separate compact list whose length matches the number of nonblank source cells, use a count to set the sequence length. With source values in A2:A20 and an empty spill area beginning at D2, enter:

=SEQUENCE(COUNTA(A2:A20))

If seven cells in A2:A20 contain values, the formula returns 1 through 7 in D2:D8. This creates a compact list, but it does not place each number beside its corresponding source row; it only uses the count of non-empty cells. That difference is important when blanks occur between records.

Why a sequence may appear to have gaps

SEQUENCE itself does not inspect nearby records or decide that a number is missing. It generates the requested array. When the result is not what you expected, check the formula arguments, the cells around the formula, and whether you are looking at generated numbers or existing IDs.

Wrong step: A step of 2 deliberately creates values such as 1, 3, 5. Use a step of 1 for consecutive integers.
Wrong row count: The first argument controls how many values appear vertically. If it is 9, the function does not generate a tenth value automatically.
Blocked spill range: A value, another formula, or a merged cell in the required output area can prevent the array from spilling. Clear the obstruction or move the formula to a larger empty area.
Filtered or hidden rows: Hiding rows does not change the values created by a SEQUENCE array. A filtered view can make displayed records look discontinuous even though the underlying sequence is intact.
Sorting only part of the data: Sorting the sequence column separately from its associated records can detach numbers from the right rows. Select the complete table or data range when sorting.

When Excel reports a spill error, select the formula cell and inspect the highlighted spill area for obstructions. Dynamic array formulas cannot spill inside an Excel table. Put the SEQUENCE formula outside the table, or use a row-by-row formula in a table column. If you need the generated values to become fixed rather than update with the formula, copy the result and use Paste Special > Values; this replaces formulas with their current values.

Options in older Excel and for fixed numbering

SEQUENCE is not available in older perpetual versions such as Excel 2019 and Excel 2016. For a simple list there, enter a row-based formula and fill it down. With the first output cell at B2, enter this formula and copy it to the rows below:

=ROWS($B$2:B2)

The first row counts a one-row range and returns 1; as the formula is copied down, the range expands and returns 2, 3, 4, and so on. This method does not need SEQUENCE, and it produces values in individual cells rather than one spilled array. To start at a different number, add that starting offset to the row count.

You can also use Excel’s fill tools. Type 1 in the first cell and 2 in the next, select both cells, then drag the fill handle down; Excel recognizes the interval and continues it. For a controlled series, select the starting cell or range and use Home > Fill > Series. The dialog lets you choose rows or columns, a step value, and a stop value. Menu placement can vary slightly by platform, but the formula approach is the same in current Windows, Mac, and web versions that support SEQUENCE.

For permanent business identifiers such as invoice numbers, consider whether automatic renumbering is acceptable. A sequence based on row position can change when rows are inserted, removed, or sorted. If numbers must remain attached to transactions for audit or reference purposes, assign and store them as fixed values, and do not rely on a formula that recalculates their order.

Frequently Asked Questions

How do I make Excel number rows without skipping numbers?
For a new list in Microsoft 365 or Excel 2021, enter =SEQUENCE(10) in an empty cell to spill 1 through 10. For records with blank rows between them, use a conditional count formula beside the records so blank rows do not receive numbers.

Why does Excel show a #SPILL! error with SEQUENCE?
Excel cannot place all the results if a cell in the required spill range is occupied or otherwise blocks the array. Clear the obstructing cells, unmerge cells if necessary, or move the formula to an empty range. A dynamic array formula also cannot spill within an Excel table.

Can SEQUENCE start at 0 or count backward?
Yes. Set the start argument to 0 to begin at zero, or use a negative step to count down. The number of values still comes from the rows argument, and each value changes by the step you specify.

Does SEQUENCE work in Excel 2019?
No. SEQUENCE is available in Microsoft 365 and Excel 2021 or later, not Excel 2019. In an older version, use a formula such as ROWS with a range that expands as you copy it down, or use Excel’s fill handle or Series command.

Can I number only nonblank rows consecutively?
Yes. Put a formula beside the first record that returns a blank when its source cell is blank and otherwise counts nonblank cells so far, then copy it down. Be aware that formulas returning an empty string and cells containing spaces may still be counted as non-empty.

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.