How to Number Filtered Cells

We have already explained how to number our rows in Excel in various ways, and how to format these numbers.

In the example below, we will show how to number the rows that are filtered in Excel.

Number the Filtered Rows

For our example, we will use the table with NBA players, their statistics from several categories: points, rebounds, assists, and turnovers:

Table

Description automatically generated

We will create another row that will be the new column A, and we will input the ordering numbers for our players:

Table

Description automatically generated

If we now filter only the players from the Eastern conference, we will notice that these numbers will not change, i.e. that we do not have the new order:

Table

Description automatically generated with medium confidence

To make this work, we need to use the SUBTOTAL formula.

We will now remove the filter and delete the numbers in column A. In cell A2 we will input number 1 and in cell A3 we will input the following formula:

=SUBTOTAL(3,B$2:B2)+1

The SUBTOTAL function allows us to create groups and after that to perform various functions, such as SUM, COUNT, MAX, etc.

When we create it, we first need to choose the function (by the number) among many:

Graphical user interface, table

Description automatically generated

We will choose the COUNTA function for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column Bcell B2, which will be locked, and cell B2 (which will be changed when dragged). We will then add number 1 to this number.

Results in cell A3 will be number 2 (1+1), in cell A4 3 (1+2), etc.

We will drag this formula till the end of our list:

Table

Description automatically generated

We will then filter only Eastern conference in our table:

Table

Description automatically generated with medium confidence

You can notice that although Kyrie Irving was numbered as player number 6 in the original table, he is now second on our list, and thus has the number 2 by his name.

This functions on a basis that the SUBTOTAL formula only observes the filtered data in our list, and it changed accordingly.