Add Row Numbers and Skip Blanks in Excel
- Total rows
- 10 rows
- You entered
- Blank rows
- 3 rows
- You entered
This guide explains how to add row numbers in Excel while skipping blank rows, and how to handle formulas that need to ignore empty cells. You will learn the difference between static numbering, dynamic formulas, and array-based approaches, and get step-by-step instructions for each.
Why Skip Blanks When Numbering?
When you have a dataset with blank rows, a simple fill-down sequence (1, 2, 3…) will number the blanks too. That can break sorting, filtering, and lookups. Skipping blanks keeps the row number aligned only with rows that contain data, making the list cleaner and formulas like VLOOKUP or INDEX/MATCH work correctly.
Basic Method: Manual Fill
The simplest way to add row numbers is to type 1 in the first data row, then drag the fill handle down. But this numbers every row, including blanks. To skip blanks manually, you would have to delete the numbers from blank rows afterward. That works for small datasets but is tedious and error-prone for larger ones.
Formula That Ignores Blank Rows
Use a formula that checks if the adjacent cell is empty. In cell A2 (assuming data starts in row 2), enter:
=IF(B2="","",COUNTA($B$2:B2))
This counts non-empty cells in column B from the top to the current row. If B2 is blank, it returns an empty string; otherwise it returns the count. Drag down. The result is a sequence that skips blanks. Note that this numbers only rows where column B has any value. If your data has multiple columns, adjust the reference accordingly.
Using the SUBTOTAL Function
Another approach uses SUBTOTAL with a function number that ignores hidden rows. For visible rows only, use:
=SUBTOTAL(3,$B$2:B2)
This counts non-empty cells in the range, but it does not skip blanks by itself. To skip blanks, combine with IF as above. SUBTOTAL is useful when you filter the list, because the numbering updates to only visible rows.
Dynamic Array Formula (Excel 365)
In Excel 365 and Excel 2021, you can use the FILTER function to create a dynamic list that excludes blank rows entirely. For example, to list all non-empty values from column B:
=FILTER(B2:B100, B2:B100<>"")
Then add row numbers with SEQUENCE:
=SEQUENCE(ROWS(FILTER(B2:B100, B2:B100<>"")))
This spills the numbers automatically. If the data changes, the list updates. This is the cleanest method for modern Excel but not available in older versions.
Common Mistakes and How to Avoid Them
- Using absolute references incorrectly: In the COUNTA formula, the first cell must be absolute (with $) and the second relative. If both are relative, the range shifts and the count is wrong.
- Counting blanks as data: If a cell contains a space character, it is not truly blank. Use
=IF(TRIM(B2)="","",...)to treat spaces as blanks. - Numbering after filtering: A normal formula numbers all rows, even hidden ones. Use SUBTOTAL to renumber visible rows.
- Dragging over blanks: If you drag a formula over a blank row, the formula still evaluates. Make sure the IF condition catches all blank rows.
When to Use Each Method
- Manual fill: Only for tiny static lists where you never update.
- COUNTA formula: Good for most datasets, works in all Excel versions.
- SUBTOTAL: Best when you filter frequently and want renumbering.
- FILTER + SEQUENCE: Best for dynamic data in Excel 365, but not backward compatible.
Frequently Asked Questions
How do I skip blank rows when numbering in Google Sheets?
The same formulas work in Google Sheets. Use =IF(B2="","",COUNTA($B$2:B2)) and drag down. For dynamic arrays, Google Sheets supports FILTER and SEQUENCE as well.
Why does my COUNTA formula count blanks?
COUNTA counts cells that are not empty, including cells with formulas that return an empty string. If your data column contains formulas returning “”, COUNTA will count them. Use =IF(B2="","",...) but note that if B2 has a formula returning “”, it is technically not blank, so the IF may not catch it. Use =IF(B2="","",...) works if the formula returns “” because that is equal to an empty string.
Can I use ROW() to number rows and skip blanks?
Yes, but you need to subtract the header offset. For example, =IF(B2="","",ROW()-1) gives 1 for row 2, but it does not skip blanks—it just numbers the row number minus one. To skip blanks, you still need a counting formula.
How do I number only visible rows after filtering?
Use =SUBTOTAL(3,$B$2:B2) in a helper column. This counts only visible non-empty cells. But it does not skip blanks by itself; combine with IF to skip blanks.
What if my data has blank rows in the middle but not at the end?
The COUNTA formula handles that correctly because it counts from the top. It will assign the next number after a blank row to the next non-blank row.