Auto Number Rows in Excel (SEQUENCE and ROW)

To auto number rows in Excel, use a formula: =ROW()-1 gives each row its own number, and =SEQUENCE(COUNTA(B2:B100)) numbers a whole list with one formula. Typed numbers break as soon as you delete or insert a row, but formula numbers renumber themselves. This guide is for anyone keeping a numbered list of staff, orders or tasks, and it covers skipping blanks, numbering filtered rows and numbers that must never change.

Which auto numbering method should you use?#

Pick the method by what should happen when the list changes. All examples use a staff list with names in column B, starting in row 2, and the numbers in column A.

How to number rows with the fill handle#

The fill handle is quickest for a list that won't change. It creates plain numbers, not formulas.

1. Type 1 in A2 and 2 in A3.
2. Select both cells.
3. Drag the fill handle (the small square at the bottom-right of the selection) down, or double-click it to fill as far as the data in column B goes.

Excel continues the pattern: 3, 4, 5 and so on. For a long list, use Home > Fill > Series instead: choose Columns, a step value of 1 and a stop value such as 500.

The catch is that these numbers are fixed. Delete row 4 and the list jumps from 2 to 4. That's why the formula methods below are usually better. The Fill Down and Fill Right lesson practises filling with Ctrl+D (⌘D on a Mac).

How to auto number rows with ROW#

ROW returns the row number of the cell it's in. With headers in row 1, subtract 1 so the list starts at 1. In A2, type:

=ROW()-1

Fill it down. A2 shows 1, A3 shows 2, and so on. Delete a row and every number below it moves up by one, so the list never has gaps. Insert a row and you only need to fill the formula into the new cell.

If the list starts lower on the sheet, subtract the header's row number instead. With headers in row 4, use =ROW()-4. A version that doesn't depend on where the list starts is =ROW()-ROW($A$1), where A1 is the header cell.

In an Excel table, a formula in one cell fills the whole column automatically, and new rows get it too. Use =ROW()-ROW(Table1[#Headers]) so the numbers stay right even if the table moves. Replace Table1 with your table's name.

How to auto number rows with SEQUENCE#

In Excel 2021 and Microsoft 365, SEQUENCE makes a list of numbers from one formula. In A2, type:

=SEQUENCE(COUNTA(B2:B100))

COUNTA counts the filled cells in B2:B100, and SEQUENCE spills that many numbers down column A. With six names, you get 1 to 6. Add a seventh name and a 7 appears. You don't need to fill anything.

SEQUENCE takes up to four arguments: =SEQUENCE(rows, [columns], [start], [step]). So =SEQUENCE(5,1,10,10) returns 10, 20, 30, 40 and 50. Two things to know:

• The cells below A2 must be empty. If anything is in the way, you see #SPILL!.
• You can refer to the whole result as A2#, the spill reference. The modern Excel functions lesson practises spill references, and the guide to dynamic drop-down lists uses them as a list source.

COUNTA assumes the names have no gaps. If some rows in B are empty, use the next method.

How to number only rows that have data#

To leave blank rows unnumbered, count the filled cells from the top down to the current row:

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

The range $B$2:B2 grows as you fill down: in row 5 it becomes $B$2:B5. COUNTA counts the names so far, and IF shows nothing for an empty row. If the gaps are mistakes, remove them instead; see how to delete blank rows in Excel.

How to number visible rows after filtering#

When you filter a list, ROW keeps the original numbers, so you might see 2, 5, 6. To number only the visible rows, use SUBTOTAL:

=SUBTOTAL(103,$B$2:B2)

Function number 103 means COUNTA that ignores hidden rows, so the count only includes rows you can see. Filter the list and the visible rows read 1, 2, 3 from the top, with no gaps. Function number 3 also ignores rows hidden by a filter, but 103 also ignores rows you hid by hand; see how to hide and unhide rows and columns.

How to keep numbers fixed or add leading zeros#

Formula numbers always follow the row's position. If you sort the list by name, the numbers stay 1, 2, 3 from the top, and they don't travel with each person. When a number is an ID that must stay with its row, freeze it:

1. Select the numbers.
2. Copy them (Windows: Ctrl+C; Mac: ⌘C).
3. Paste as values: Home > Paste > Values, or Ctrl+Alt+V on Windows and ⌃⌘V on a Mac, then choose Values.

For numbers like 001, apply the custom number format 000 to the ROW formula. The value stays a real number but shows three digits; see custom number formats in Excel. For a text ID with a prefix, join it: ="EMP-"&TEXT(ROW()-1,"000") returns EMP-001 in row 2.

Practise it#

Try the exercise on this page: it numbers a row with ROW. Then practise Fill Down and Fill Right in a live spreadsheet to copy formulas down a column in one keystroke, and practise modern Excel functions and spill references to work with formulas like SEQUENCE that fill many cells at once.