# How to number rows automatically in Excel

Let SEQUENCE count the filled rows: `=SEQUENCE(COUNTA(B2:B30))` numbers every task from 1, and the numbers extend when you add a row.

**Excel:** `=SEQUENCE(COUNTA(B2:B30))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| # | Task | Owner |
|  | Draft budget | Ana |
|  | Book venue | Raj |
|  | Order badges | Lee |
|  | Send invites | Ana |
|  | Confirm catering | Kim |
|  | Print programs | Lee |
|  | Arrange AV | Raj |
|  | Plan seating | Kim |

Formula in A2: `=SEQUENCE(COUNTA(B2:B30))`

Result (spills 8 × 1): 1; 2; 3; 4; 5; 6; 7; 8

## 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.

## Other ways

- `=IF(B2:B30="","",ROW(B2:B30)-ROW(B1))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/number-rows-automatically