Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to number rows automatically in Excel and Google Sheets
Topic:Data cleaning & shapingExcel & Google Sheets
=ROW()-1✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| # | Name |
| Alice | |
| Bob | |
| Carol | |
| David | |
| Eve |
=ROW()-1→0
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 cell A2 (assuming row 1 is the header) enter =ROW()-1 — this returns the current row number minus 1, giving 1 for row 2, 2 for row 3, and so on.
- 2Copy the formula down through all data rows.
- 3If your header is not in row 1, adjust the subtracted offset: if data starts in row 3, use =ROW()-2.
Tips
- ROW()-based numbering always stays correct even if you delete or insert rows — unlike a hardcoded 1, 2, 3 series that would need manual correction.
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
How do I number only rows that contain data (skip blanks)?
Use =IF(B2<>"",COUNTA($B$2:B2),"") — this counts non-blank cells from the top down, giving a sequential number only to filled rows.
Can I restart numbering at 1 for each group?
Yes — use COUNTIF to count occurrences of the group label so far: =COUNTIF($B$2:B2,B2) gives the within-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.