How to auto number rows in Excel

Method 1: the fill handle (fixed numbers)

The sheet is a guest list with two empty rows left for late additions. The quickest way to number it is to let Excel continue a pattern. The numbers it writes are plain values, not formulas, so they never change on their own.

Double-clicking the fill handle fills down as far as the column next to it has data, and it stops at the first gap. Here it stops at row 4 because row 5 is empty, so drag instead when your list has holes.

1. Type 1 in A2 and 2 in A3.
2. Select A2:A3.
3. Drag the small square at the bottom-right corner of the selection (the fill handle) down to the last row, or double-click it.
4. For a long list, type 1 in A2, select it, then go to Home > Fill > Series, choose Columns, set Step value 1 and Stop value 500, and click OK.

Method 2: ROW()-1 (renumbers when you delete)

ROW() returns the row number of the cell it is in. The header takes row 1, so =ROW()-1 in A2 gives 1, in A3 gives 2, and so on. Fill it down once and you are done. Delete a row and every number below moves up by one, with no gap.

If the header is not in row 1, subtract the header's row instead: =ROW()-ROW($A$1). That keeps working if someone later inserts rows above the table, because the $A$1 reference moves with the header.

The catch is inserted rows. A new row has no formula, so it stays empty and the next row's number skips ahead until you copy the formula into it. Turning the list into a table (Insert > Table) fixes this: a formula typed in a table column fills itself into every new row.

Method 3: SEQUENCE and COUNTA (Excel 365 and 2021)

SEQUENCE(n) spills the numbers 1 to n down the column, and COUNTA counts the filled names, so =SEQUENCE(COUNTA(B2:B100)) in A2 grows by itself as you add guests. Point COUNTA at a range longer than the list so new names are counted.

It only lines up when the list has no gaps. The guest list has 7 names over 9 rows, so the spill writes 1 to 7 into A2:A8. The empty row 8 gets a 7, and Hannah in row 10 gets no number at all. Use this method on lists without blank rows, and keep the cells below A2 empty or you get #SPILL!. Spilled formulas do not work inside an Excel table either.

For a numbered copy of the list with the gaps removed, Excel 365 users can spill both columns at once: =HSTACK(SEQUENCE(COUNTA(B2:B10)),FILTER(B2:B10,B2:B10<>"")).

Number only the filled rows (skip blanks)

To leave empty rows unnumbered and keep counting on the next name, test the row first and count the names so far. Put this in A2 and fill it down to A10: =IF(B2="","",COUNTA($B$2:B2)). The range starts at a locked $B$2 and grows one row at a time, so each row shows how many names appear from the top down to it.

On the guest list that gives 1, 2, 3, blank, 4, 5, blank, 6, 7. A cell that holds only a space looks empty but is not, so it gets a number and is counted. Clear stray spaces (Home > Find & Select > Replace, a space with nothing) before you trust the result.

Numbers that survive sorting and deleting

Decide first what the number means. If it is the row's position (1 at the top, always in order), use ROW()-1: after you sort by name, the numbers still read 1, 2, 3 from the top, but they no longer belong to the same guests.

If it is an ID that must stay with the record, such as a ticket or guest number, it has to be a fixed value. Number the list with the fill handle, or with a formula and then convert it: select the numbers, copy, and paste back with Home > Paste > Paste Values. Include column A when you sort and the IDs travel with their rows. Deleting a row then leaves a gap, which is usually what you want for an ID.

Avoid the =A2+1 chain (1 in A2, =A2+1 in A3 and so on). It looks fine until you delete a row: the formula below loses its reference and shows #REF!, and every row under it shows #REF! too.

For a filtered list, =SUBTOTAL(3,$B$2:B2) filled down counts only the visible names, so the numbers stay 1, 2, 3 on screen when you filter. The app's engine cannot run SUBTOTAL, so there is no live result here.

Which one to pick

Permanent IDs that move with sorting: the fill handle or pasted values. A position number that closes up after deletes: ROW()-1, inside a table if people insert rows. A list that grows with no gaps in Excel 365: SEQUENCE(COUNTA()). Blank rows in the list: the IF and COUNTA formula. To count repeats of the same value (1st Acme order, 2nd Acme order), use a running count with COUNTIF instead.