Microsoft Office Tutorials and References
In Depth Information
Tip 76: Numbering Table Rows Automatically
If you filter the table, rows that don’t meet the filter criteria will be hidden. In such a case, some table
row numbers will not be visible. Figure 76-2 shows the table after filtering to display only the Clerk
Figure 76-2: When the table is filtered, the row numbers are no longer consecutive.
If you want the table row numbers to remain consecutive when the table is filtered, a different
formula is required. Referring to the example in Figure 76-1, enter this formula in cell B5, and it will be
propagated to the other rows:
This formula uses the SUBTOTAL function, with a first argument of 3 (which corresponds to COUNTA).
The SUBTOTAL function ignores hidden rows, so only visible rows are counted. Notice that the
formula refers to a different column — which is necessary to avoid a circular reference error.
Figure 76-3 shows the filtered table using the SUBTOTAL formula in column B.
Figure 76-3: When the table is filtered, the row numbers remain consecutive.