In this short Excel tutorial, you will learn how to automatically generate serial numbers after filter in Excel.
Using this method, serial number changes dynamically after filter in Excel.
Let’s quickly see the issue with the static serial number.
In the below image you can see a not filtered table with a serial numbers.
In the below image of the same table, data is filtered for a country named Germany.
As you can see after filter serial number is not getting changed automatically.
To dynamically update serial number after filter I will use the Subtotal function for Counta.
Subtotal function performs a calculation on the visible range only. It takes two inputs.
1st is the function number representing the function name and 2nd is range.
Subtotal Formula In Excel | Sum visible only visible cells after filtering in Excel
Counta counts the number of non-empty cells in the range.
In the below image cell B3 formula is
=SUBTOTAL(3,$C$3:C3) here 3 represents Counta function.
In the above formula in the range $C$3:C3, $C$3 is the absolute reference (fixed).
This type of range setup is necessary so that the below successive cell range will contain one more cell than the previous.
This is how you will get serial numbers using the formula.
In the below image you can see after filtering Germany, serial numbers got auto-updated.
Cell B4 formula is =SUBTOTAL(3,$C$3:C4), range $C$3:C4 contains 2 non empty cells.
Since the Subtotal function performs calculations on visible cells and cell C3 is hidden thus not taken into account and output is 1.
Cell B9 formula is =SUBTOTAL(3,$C$3:C9), in this only 4 non-empty cells are visible hence the output is 4.
So this is how you dynamically get or auto change the serial numbers after filtering data in Excel.
