Exceljet

Quick, clean, and to the point

Automatic row numbers in Table

Excel formula: Automatic row numbers in Table
Generic formula 
=ROW()-ROW([#Headers])
Explanation 

To add automatic row numbers to an Excel Table, you can use a formula based on the ROW function. In the example shown, the formula in B5, copied down, is:

=ROW()-ROW(Table1[#Headers])

Note: The table name is not required. However, Excel will add the table name automatically if omitted.

How this formula works

When no argument is provided, the ROW function returns the "current row", that is, the row number of the cell that contains it. When a cell reference is provided, ROW returns the row number of the cell. When a range is provided, ROW returns the first row number in the range.

In the example shown, the formula in B5 is:

=ROW()-ROW(Table1[#Headers])

The first ROW returns 5, since ROW is provided no argument, and resides in cell B5. The second ROW uses a structured reference:

Table1[#Headers] // header row

The header row resolves to the range $B$4:$F$4, so ROW returns 4. For the first 3 rows of the table, we have:

B5=5-4 // 1
B6=6-4 // 2
B7=7-4 // 3

No header row

The formula above works great as long as a table has a header row, but it will fail if the header row is disabled. If you are working with a table without a header row, you can use this alternative:

=ROW()-INDEX(ROW(Table1),1,1)+1

In this formula, the first ROW function returns the current row, as above. The INDEX function returns the first cell in the range Table1 (cell B5) to the second ROW function, which always returns 5. For the first 3 rows of the table, the formula works like this:

B5=5-5+1 // 1
B6=6-5+1 // 2
B7=7-5+1 // 3

This formula will continue to work normally even when the header row is disabled.

Author 
Dave Bruns
See also 

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables