How to Apply Serial Number After Filter in Excel?

How to apply serial number after filter in Excel?
Table of Contents

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

Filtered data with serial number
Original table

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.

Apply the filter as usual
Apply the filter as usual

Step 2: Add a new column

Add a new column
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!

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
International Open Academy

Join Our Newsletters!

JOIN OUR NEWSLETTERs!