Continuous numbers without gaps
=ARRAYFORMULA(IF(B2:B="","",MATCH(ROW(B2:B),FILTER(ROW(B2:B),B2:B<>""),0)))Counts only filled rows.
PASTE-READY RECIPE
Number Rows Automatically in Google Sheets
Generate row numbers only when the row contains data.
QUICK ANSWER
The formula and where it fits.
Generate row numbers only when the row contains data. Use =ARRAYFORMULA(IF(B2:B="","",ROW(B2:B)-ROW(B2)+1)), then replace the sample ranges with the columns in your sheet.
ROW creates the sequence; IF leaves unused rows empty.
- Put the source data under one header row.
- Paste the formula into an empty result cell.
- Change the ranges and criteria to match your sheet.
- Check the result against one row you can verify manually.
TRY ITEdit the table.
Edit the table.
Watch the result change.
| A | |
|---|---|
| 1 | Task |
| 2 | |
| 3 | |
| 4 | |
| 5 |
FORMULA OUTPUT1 · 2 · blank · 4Recalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
SEQUENCE
=SEQUENCE(COUNTA(B2:B))Creates a compact list from 1 to the count.
Start at 1001
=ARRAYFORMULA(IF(B2:B="","",ROW(B2:B)-ROW(B2)+1001))Uses a custom starting number.
COMMON ERRORS
When the result looks wrong.
Numbers appear beside blank-looking formulas
Test B2:B="" rather than COUNTA when source formulas return empty text.
Sorted rows keep old manual numbers
Use the array formula rather than typed numbers.
QUESTIONS
Answers before you paste.
Numbers appear beside blank-looking formulas
Test B2:B="" rather than COUNTA when source formulas return empty text.
Sorted rows keep old manual numbers
Use the array formula rather than typed numbers.
MAKE IT YOURS
Adjust this formula →Change the ranges, labels or criteria.
Send this recipe to the SheetSteps assistant and describe how your columns are arranged.