Microsoft Office Tutorials and References
In Depth Information
Numbering Rows in a Table
Numbering Rows in a Table
If you have a table (created by choosing Insert
Table), you might want to number the
rows in the table. Excel doesn’t provide a direct way to number table rows, but this tip describes
how to accomplish it with a formula.
Tables
Figure 161-1 shows a table with an empty column labeled Number. The goal is to create a formula
that displays the row number — and that remains correct even if table rows are added or deleted
or if the table is filtered or sorted.
Figure 161-1: This table needs a row number in the first column.
The following formula, entered in column B5, does the job:
=SUBTOTAL(3,C$4:C4)
This formula (which Excel copies to other cells in column B) uses the SUBTOTAL function with an
argument of 3 (COUNTA). The formula returns the count of all cells in column C, starting with the
header row and ending with the row that’s one cell above the cell with the formula. Notice that
the formula is in column B but references column C, to avoid a circular reference.
Figure 161-2 shows the table after entering the formula.
This formula continues to show consecutive row numbers if the table is sorted or filtered or if
new rows are added or deleted.
Figure 161-3 shows the table after sorting and filtering to show only rows in which the score is
greater than 90. Notice that the Number column continues to show consecutive numbers.
 
Search JabSto ::




Custom Search