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.