Automatic Numbering of Rows after Filtered Data
The Excel program has the ability to filter data or rows, whether the data is in a cell or Table range. The average user most often uses the "Auto Filter" located on the HOME tab in the "Editing - Sort & Filter" group. There is also an "Advanced Filter" that you can use to Filter Data in Excel.
In this Excel tutorial I will show you how you can automatically numbering rows after you filter the data (table). Take the situation to have data in a cell range. The first column 'A' shows the numbered ordinal numbers of all data. After filtering data by one column, columns 'A' display their ordinal numbers. Well, so for some data that is filtered under the header, there is a number of 307 and you actually want the item to have the ordinal number 1 because it is the first in the order of the filtered data.
So to create a new numbering rows after filtering the data, see this tutorial showing how to count the filtered data in Excel.
The situation is the same as in the picture below.
Note the following: In column "A" we have a classical numbering or sequence of numbered numbers in a sequence of 1,2,3,4,5 ... and further. After filtering the data, these numbers are not changed, so a broken sequence of numbers appears.
In column "B" we also have numbered numbers in the range of 1,2,3,4 ... for the data in question, but after filtering these numbers are changed and displayed by new numbering, i.e. they keep number of numbered numbers. Column 'B' contains the formula. Other columns contain data.
In cell 'G1' (see picture above) there is a formula that automatically counts all the data in a range of cells (in this case counts rows visible only) and it is the following:
(first argument number 2 is valid for number data, number 3 is valid for textual data)
Filtering Data in Excel
I believe you know how to filter data in Excel using Auto Filter. If you do not see the picture below. So when we want to Filter Data in a cells range (or in a table) then we first choose a column that contains the conditions under which we want to filter the data. In this case, I have chosen a column 'D' that contains the states' names. So click on the triangle (top-down) and mark only the items you want to display the unlimited filtering.
Automatic Numbering of Filtered Rows of Data in Excel
After filtering the data, we have the following situation from the first image of this tutorial.
In the picture above, notice the following:
I've filtered data by criterion in column 'D'. The first column 'A' containing the row numbers of all data in the cell range remained the same. In column "B", the serial numbers have changed for each data item. That is my goal, now all the data in column 'B' have a sequence of numbers in the range of 1,2,3,4 ... so on. If desired, the column 'A' can be hidden or removed from the table (cell range), I have set it up for this example only to understand what I am writing.
The cell formula 'B2' that automatically generates a continuous sequence of numbers after filtering the data is the following:
The first argument of the Excel SUBTOTAL function number 3 refers to textual data (number 2 refers to numeric data).
Note that in the formula I used the absolute address of the first cell line in the range