To number rows automatically in Excel, you can use a formula that counts records or generates a sequence, so numbering updates as your data changes.
The right method depends on whether numbers should follow worksheet rows, skip blank cells, or adjust when you filter a list.
Choose a numbering method for your data
For a simple list, a formula based on the row position is quick to set up.
For a table that grows as you add records, a formula that counts nonblank cells is often more reliable.
Newer Excel versions also include dynamic array functions that can spill a complete sequence into neighboring cells.
- Fixed row-based numbering: Useful when every row in a range should have a number.
- Number nonblank records: Useful when the data column may contain empty cells.
- Dynamic sequence: Useful when you want Excel to generate many numbers from one formula.
- Filter-aware numbering: Useful when visible records should be renumbered after filtering.
Number rows with the ROW function
The ROW function returns a worksheet row number.
To start numbering in cell A2 with 1, enter this formula in A2:
=ROW()-1
Copy the formula down the column.
A2 returns 1, A3 returns 2, and A4 returns 3.
The subtraction adjusts for the header in row 1.
If your list starts in row 5 and its first number should be 1, use =ROW()-4.
This method follows worksheet positions. After recalculation, sorting the complete range keeps the numbers tied to the displayed row order rather than permanently attached to individual records.
Deleting a row also recalculates the remaining positions. If you need a permanent record ID, assign a stored value instead of using a row-position formula.
Number only rows with data
To number records only when a corresponding cell contains data, use COUNTA.
For example, if names are in column B and numbering begins in A2, enter:
=IF(B2="","",COUNTA($B$2:B2))
Fill the formula down.
If B2 contains “Ava,” A2 returns 1.
If B3 is blank, A3 remains blank.
If B4 contains “Noah,” A4 returns 2.
The expanding range $B$2:B2 counts nonblank cells from the first record row through the current row.
Use this approach when the data column has occasional gaps.
Choose a column that is filled for every valid record; otherwise, a blank in that column will make the formula omit a row.
COUNTA counts cells containing formulas too, even when those formulas display an empty string.
If your data uses formulas that return "", a count-based formula may treat those cells as nonblank.
Generate a sequence with SEQUENCE
Microsoft 365 and Excel 2021 or later support SEQUENCE, which returns an array of consecutive numbers.
To generate 20 numbers starting at 1, enter this in an empty cell:
=SEQUENCE(20)
Excel spills the results into 20 cells below the formula.
To start at 100 and increase by 10, use =SEQUENCE(20,1,100,10).
The arguments specify the number of rows, number of columns, starting number, and step size.
For a sequence that matches a populated list, you can use a count of the records.
For example, if names are in B2:B21, enter =SEQUENCE(COUNTA(B2:B21)).
This generates one number per nonblank cell counted in the range, but it does not create blanks in the sequence for gaps within the source data.
The spill range must be clear; otherwise, Excel returns a #SPILL! error.
Make numbering expand with an Excel table
Excel tables automatically expand when you type or paste data directly below them.
To create a table, select the data and choose Insert > Table, then confirm whether it has headers.
In the table’s first numbering cell, use a structured-reference formula such as:
=ROW()-ROW(Table1[#Headers])
Replace Table1 with the table’s actual name.
The formula subtracts the header row’s position, making the first data row number 1.
Excel usually fills a calculated-column formula through the table and applies it to new table rows.
Table numbering based on ROW reflects the current worksheet position.
If you need values to remain attached to records as rows are rearranged, consider storing assigned IDs as values rather than recalculating row positions.
A sequence number is useful for display and ordering; it is usually not a durable record identifier.
Renumber visible rows after filtering
When a filter hides some records, ordinary ROW formulas still reflect the worksheet rows, so visible numbers may contain gaps.
In Excel 2010 and later, use SUBTOTAL with function number 103 to count visible nonblank cells.
If column B contains a value for every record, enter this in A2 and fill down:
=IF(B2="","",SUBTOTAL(103,$B$2:B2))
With a filter applied, visible records are numbered in order, while hidden rows are excluded from the count.
Function number 103 ignores manually hidden rows as well as rows hidden by a filter.
If manually hidden rows should still count, use function number 3 instead: =IF(B2="","",SUBTOTAL(3,$B$2:B2)).
Both versions rely on column B being nonblank for each record.
Common problems and fixes
- The first number is not 1: Check the formula’s starting row.
For a list beginning at row 2,=ROW()-1starts at 1. - Blank rows still receive numbers: Use an
IFtest tied to a consistently populated data column. - Numbers have gaps after filtering: Use the
SUBTOTALpattern to count visible records. - SEQUENCE returns
#SPILL!: Clear cells in the intended output area, including cells that appear empty but contain spaces or formulas. - New table rows do not inherit the formula: Check that the data was added directly adjacent to the table and that calculated-column formulas are enabled in Excel’s AutoCorrect options.
- Numbers change after sorting: Decide whether numbering represents current order or a permanent ID.
Formulas update with worksheet changes; fixed identifiers should be stored as values.
For older Excel versions without SEQUENCE, fill down a ROW or COUNTA formula.
These formula approaches work in desktop Excel versions that support the functions used, and the basic ROW and COUNTA methods are also available in Google Sheets, though table structured references are Excel-specific.