Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to auto-number rows that have data in Excel and Google Sheets
Topic:Data cleaning & shapingExcel & Google Sheets
=IF(B2<>"",COUNTA($B$2:B2),"")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| # | Name |
| Alice | |
| Bob | |
| Carol | |
=IF(B2<>"",COUNTA($B$2:B2),"")→1
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In A2 (the number column), enter =IF(B2<>"",COUNTA($B$2:B2),"") — replace B with whichever column determines whether a row has data.
- 2Copy the formula down all rows in your data range.
- 3Blank rows in column B return an empty string in the number column; filled rows get a sequential count starting at 1.
Tips
- The expanding range $B$2:B2 is the key — the top of the range is locked, but the bottom reference grows as you copy down, so COUNTA counts all filled rows from the top to the current row.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if I delete a row — do the numbers update?
Yes — because COUNTA recounts from the top each time, the sequence automatically closes any gap left by a deleted row.
Can I restart numbering for each group?
Yes — use COUNTIF: =IF(B2<>"",COUNTIF($C$2:C2,C2),"") where column C is the group label — this gives a per-group sequence number.
More on Data cleaning & shaping
See all →How-to
How to merge two columns
Combine two columns into one with the & operator — perfect for joining first and last names into a full name.
How-toHow to create a sequential invoice number
Automatically generate sequential invoice numbers with a prefix and zero-padded counter using TEXT and ROW in Excel and Google Sheets.
How-toHow to number rows within each group
Assign sequential row numbers that restart for each group using COUNTIF with a mixed reference in Excel and Google Sheets.
How-toHow to remove blank rows
Delete empty rows quickly by filtering for blanks and deleting visible rows, or use Go To Special in Excel for a one-click approach.
How-toHow to transpose rows to columns
Flip rows and columns using the TRANSPOSE function or Paste Special to convert horizontal data to vertical and vice versa.
How-toHow to combine multiple columns into one
Merge values from several columns into one cell using the ampersand operator or CONCAT/TEXTJOIN for delimited output.