How to number rows automatically in Excel
Formula (Excel and Google Sheets)
=SEQUENCE(COUNTA(B2:B30))Let SEQUENCE count the filled rows: =SEQUENCE(COUNTA(B2:B30)) numbers every task from 1, and the numbers extend when you add a row.
- Result on the example
- a 8-row list that spills from A2 in
A2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in A2. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTA counts the filled cells in B2:B30, 8 here, and SEQUENCE returns that many numbers, 1 to 8, spilling down column A. Type a task in B10 and the count becomes 9, so the numbers grow by one without copying anything.
=SEQUENCE(COUNTA(B2:B30))Result{8 rows × 1 col} 1, 2, 3, 4, …
B2:B3029 rows × 1 col: "Draft budget", "Book venue", "Order badges", "Send invites", …A range: 29 cells in one column.
COUNTA(B2:B30)8COUNTA: Counts cells that are not empty.- value1
B2:B3029 rows × 1 col: "Draft budget", "Book venue", "Order badges", "Send invites", …
- value1
SEQUENCE(COUNTA(B2:B30)){8 rows × 1 col} 1, 2, 3, 4, …SEQUENCE: A grid of sequential numbers. Spills.- rows
COUNTA(B2:B30)8
- rows
In Google Sheets
The same formula works in Google Sheets: =SEQUENCE(COUNTA(B2:B30)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 8 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(B2:B30="","",ROW(B2:B30)-ROW(B1)) | spills 29 × 1Different result | Numbers each row by its position and leaves blank rows blank, so the numbers stay next to the right task even if the list has gaps. Spills to row 30, with blanks after row 9. |
Common mistakes
- COUNTA stops short if the list has gaps: with a blank row in the middle, the last task gets no number. Use the IF and ROW version when gaps are possible.
- Anything typed in column A below the formula blocks the spill and shows #SPILL!. Clear the old numbers first.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Number rows automatically
A2, write one formula that numbers the tasks in column B from 1, and keeps numbering as tasks are added below row 9.Questions
How do I number rows in older Excel?
Put =ROW()-1 in A2 and fill down, or =IF(B2="","",ROW()-1) to skip blank rows.
How do I do this in Google Sheets?
The same formula works. Another common version is =ARRAYFORMULA(IF(B2:B="","",ROW(B2:B)-1)), which covers the whole column.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.