How to Get a Sequence Number in Excel Automatically

To create a simple sequence number in Excel, enter a formula in the first numbering cell and fill it down. For example, if row 1 contains headers and numbering starts in A2, use =ROW()-1; for a ready-made series that spills into cells below, use =SEQUENCE(10). Choose based on whether you want numbers tied to worksheet rows or a fixed-length list.

What You’ll Need

A workbook in Excel for Microsoft 365 on Windows, or a version that supports the method you plan to use. The SEQUENCE function is available in Microsoft 365 and Excel 2021 or later.
A column or range where the numbers can go. If your data has a heading row, keep that row out of the numbering range.
For numbering only rows that contain records, a neighboring column that shows whether each row has data, such as a name or item description.

Step-by-Step

Choose where the sequence should begin. Decide whether the first number belongs in the first worksheet row or below a heading. If row 1 has headings, put the sequence in A2 or another empty cell on that same data row. If the numbering column already contains information, choose a blank column instead. Check a few rows below the starting cell: formulas that fill down need room, and a SEQUENCE formula needs a clear area for its results.

With the header in row 1 and numbering beginning in A2, enter this formula in A2:

=ROW()-1

It returns 1 in A2 because the row number is 2 and 2 minus 1 is 1. In A3 it returns 2, then 3 in A4. The calculation uses the worksheet row, so it is convenient for a simple list but will change if rows are inserted or deleted above the list.
Fill the formula down the column. Select A2, point to the small square at the lower-right corner of the cell, and drag the fill handle down to the last row you want numbered. Excel adjusts the row reference automatically. To fill a long adjacent list, double-click the fill handle; Excel generally fills down to match the length of the neighboring column. Check the final row, since blank cells in the neighboring column can affect how far the double-click fills.

You can also select the destination range, including the formula cell, and use Home > Fill > Down. On Windows, Ctrl+D fills down in a selected range. If you use the keyboard shortcut, select A2 through the last target cell first. On Mac, use the Fill command on the Home tab if your keyboard settings do not recognize the Windows shortcut. In Excel for the web, the fill handle or Home > Fill > Down are practical alternatives.
Generate a fixed run with SEQUENCE. Use this option when you want Excel to create several consecutive numbers at once, without copying a formula into each row. With cell A2 selected and a clear range below it, enter:

=SEQUENCE(10)

In Microsoft 365 or Excel 2021 and later, the formula spills the values 1 through 10 into A2:A11. The 10 is the number of rows to generate. You can change it to the number of entries you need. The optional arguments let you specify the number of columns, starting value, and step between values; for example, a start of 5 and a step of 5 creates 5, 10, 15, and so on. If you include those arguments, keep their order as rows, columns, start, step.

SEQUENCE is a dynamic array formula: enter it once in the top cell and Excel supplies the results in the cells below. Do not type into the spill area, or Excel cannot display the full series. In Excel for the web, the function works in supported accounts; its spill behavior is similar. Older Excel versions without SEQUENCE can use the fill-handle method instead.
Number only rows that contain a record. A plain ROW formula numbers every row, including blank ones. If names or item descriptions are in column B and you want blanks in B to have no number, start in A2 and use the following formula:

=IF(B2="","",COUNTA($B$2:B2))

This assumes the list starts in row 2, with its entries in B2:B and numbers in A2:A. Fill the formula down as far as records might extend. A blank B cell produces a blank in A; each nonblank entry receives the next count. For example, if B2 contains an item, B3 is blank, and B4 contains another item, the visible numbers are 1, blank, and 2. The dollar signs keep the first part of the counting range anchored as the formula is copied.
Decide whether the number should change after sorting. Formulas calculate from the current row or data, so sorting the records can change which record has which number. That is useful if the numbers are just row labels that should follow the current order. It is not suitable if each number is a permanent record ID. For permanent IDs, assign the numbers and convert them to values: copy the numbered cells, then use Home > Paste > Values. Confirm the pasted results before removing the original formulas. Save a copy first if the IDs are important.

Common Mistakes

Using the wrong row offset. =ROW()-1 starts at 1 in row 2, not in every possible starting row. If numbering begins in row 5, that formula returns 4. To start at 1 in A5, use =ROW()-4; the subtraction must match the starting worksheet row minus 1.
Overwriting a SEQUENCE spill range. Typing in any cell where the series needs to appear blocks the spill. Remove the blocking entry, or move the formula to an area with enough empty cells.
Expecting a formula to create permanent IDs. A formula linked to row position can recalculate when rows move or the list is sorted. Use values for identifiers that must remain attached to a specific record.
Forgetting to extend a copied formula. A formula only numbers cells where it has been filled. If new records are added later, extend the formula, use a table, or choose a method that spills the required count.
Filling into a heading or unrelated data. Before dragging, check the destination range. A fill operation replaces existing contents in the selected cells.

Tips

Give the numbering column a clear heading such as Number or Row. If you use a table, add the formula in the first data cell and check that Excel fills the calculated column consistently.
Use a fill handle for a sequence that should be easy to extend one row at a time. Use SEQUENCE when the intended count is known and you want a compact formula that produces the whole run.
To begin at a value other than 1 with SEQUENCE, set its start argument. For a series that increases by 2, set the step argument to 2. Keep the spill area empty and make sure the row count is large enough.
Format the result as Number or General if Excel displays dates or unexpected formatting. Select the cells and use Home > Number Format to choose a suitable format.
If you need to preserve a sequence before rearranging data, copy the results and paste values. Keep a backup of the formula version until you have checked that each value is beside the intended record.

Troubleshooting

SEQUENCE shows #SPILL!: Excel cannot place one or more results in the required cells. Look below and to the right of the formula for existing values, merged cells, or another obstruction. Clear the cells that should receive the series, or move the formula. Also check that the formula is not inside an Excel table, where dynamic-array spill formulas cannot spill as a range; place it outside the table or use a filled-down formula column.

The formula appears as text instead of calculating: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, remove any apostrophe, then edit and confirm the formula again. A formula should begin with an equals sign. Also check that Show Formulas is not enabled; press Ctrl+` on Windows to toggle it, or use Formulas > Show Formulas. Menu availability can vary slightly in Mac and web interfaces.

The numbers start at the wrong value: Check the formula against the row where it is placed. With numbering starting in row 5, =ROW()-4 returns 1; using =ROW()-1 there returns 4. If you use SEQUENCE, check its start argument and confirm that you have not entered the formula one row away from where you intended.

Numbers skip rows or repeat: For a simple ROW formula, skips usually reflect actual worksheet row positions, including blank rows. For the IF and COUNTA method, confirm that the reference column contains the records you intend to count and that the formula begins at the first data row. If the formulas were pasted rather than filled, inspect the references in a few cells to ensure they adjust as expected.

The numbering changes after filtering or sorting: A row-based formula reflects the current layout, not a permanent identity. If you need numbering only for currently visible filtered records, choose a formula designed to count visible records and test it with your data. If you need fixed identifiers, paste the finished numbers as values before sorting, then verify that each ID remains next to its record.

Frequently Asked Questions

How do I create a sequence number in Excel without dragging? If you use Microsoft 365 or Excel 2021 and later, enter =SEQUENCE(10) in a blank cell to generate ten consecutive numbers. Change 10 to the desired count and leave the spill cells empty. In older Excel versions, enter the first two values and use Home > Fill > Series, or copy a formula down.

How do I start numbering at 100? With a fixed run in Microsoft 365 or Excel 2021 and later, use SEQUENCE with 100 as the start argument. For numbering by a row formula, adjust the offset so the first cell returns 100; check the actual worksheet row before copying the formula.

Can I number rows while skipping blank records? Yes. Use the IF and COUNTA approach in Step 4, changing column B to the column that reliably indicates a real record. The number cell stays blank when that record cell is empty, and the next nonblank record receives the next count.

Will sequence numbers stay the same after sorting? Not necessarily. A formula based on ROW updates to the new row position, and a counting formula may recalculate based on the new order. If the number must remain permanently associated with a record, copy the numbers and paste them as values before sorting.

Why does Excel show #NAME? for SEQUENCE? Your Excel version may not support the function, or its name may have been entered incorrectly. SEQUENCE is available in Microsoft 365 and Excel 2021 or later. In an older version, use a formula such as ROW-based numbering and fill it down, or use the built-in Series command.

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.