Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education


Automatic numbering rows filtered data

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 number 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 row 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 the number of numbered numbers. Column 'B' contains the formula. Other columns contain data.

Numbering of data in Excel

In cell 'G1' (see picture above) there is a formula that automatically counts all the data in a cell range (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)


Filter 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 cell 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.

Run automatic data filtering in Excel

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.

Automatic continuine numbering of filtered rows

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 function SUBTOTAL No. 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
  • Filtering data in Excel
  • Filtering text in Excel
  • Filtered Data In Excel