Applying serial number in a filtered list is a thorny problem. If you hard code the serial number or use the autofill function, the number will be renumbered automatically. In that case, you will have to manually change the number again.
In this article, I will show you two ways to apply serial number after filter. The first way is to add a new column of serial number and the second way is to make the serial number renumber automatically.
Sample
What we would like to do
To have number sequence when the filter is applied
You might also be interested in Edit The Same Cell In Multiple Excel Sheets
Apply serial number after filter in Excel by adding a column
Step 1: Apply the filter as usual
In this case, I only kept the “Papaya” in the column.
Step 2: Add a new column
Step 3: Enter this formula in Cell D6
=COUNTA($D$1:D5)
In this example, I will enter the formula in cell D6. You will have to adjust the formula a bit when you use it.
If the condition is like this:
- you are entering the serial number in column B,
- the first cell of the sequence is in row 11,
Then the formula will be like this
=COUNTA($B$1:B10)
Step 4: Copy the formula down
You might also be interested in How to split Excel sheets into separate workbooks
Apply serial number that will renumber automatically
The beauty of this method is that you don’t have to repeat the above process when the filter is changed. After you applied the formula below, the serial number will renumber automatically.
Step 1: Enter this formula in cell A2
=SUBTOTAL(3,$B$1:B2)-1
If the serial number is in column G, then you should change the above formula to =SUBTOTAL(3,$H$1:H2)-1.
Step 2: Copy the formula down
For example, the formula in cell A3 should be =SUBTOTAL(3,$B$1:B3)-1
What will happen when filter is applied
Let’s say I would like to display “Papaya” only.
The number in column A will renumber automatically.
Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!
