How to / Counting
How to number rows automatically in Excel
In Excel 2021 and Microsoft 365, use =SEQUENCE(COUNTA(B2:B4)) in the first cell. It numbers as many rows as the list has.
Numbers for a list of names
| A | B | |
|---|---|---|
| 1 | # | Name |
| 2 | 1 | Ana |
| 3 | 2 | Ben |
| 4 | 3 | Chloe |
In A2: =SEQUENCE(COUNTA(B2:B4)) → 1; 2; 3
How it works
- COUNTA counts how many names are in the list.
- SEQUENCE returns that many numbers, starting at 1, and they spill down the column.
- Give COUNTA a longer range, such as
B2:B500, and the numbering grows as names are added.
Variations
In any version of Excel
A B 1 # Name 2 1 Ana 3 Ben 4 Chloe In A2:
=ROW()-1→ 1ROW() is the row the formula is in. Fill it down: row 3 gives 2, row 4 gives 3. Subtract whatever puts 1 on your first row.
Only number the rows that have a name
A B 1 # Name 2 1 Ana 3 Ben 4 Chloe In A2:
=IF(B2="", "", COUNTA($B$2:B2))→ 1Filled down, it skips empty rows and carries the count on after them.
Watch out for
- Typed numbers do not renumber. Typing 1 and 2 and dragging the fill handle gives plain numbers. Delete a row and there is a gap. The formulas above close it.
- Sorting. Row-number formulas stay in order when the list is sorted, which is what you want for a position column. For an ID that must stay with its row, type the numbers.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- SEQUENCE: Generates a list of sequential numbers that spills into neighbouring cells.
- ROW: The row number of a reference (or of the current cell).
- COUNTA: Counts cells that are not empty.
- IF: Returns one value if a condition is TRUE and another if it is FALSE.