3 Ways to Auto Number or Renumber After Filter in Excel

Using Filter is a widely used feature in Excel. However, if our dataset contains serial numbers, the serial numbers remain the same as before after filtering the dataset. In this article, we will show you how to auto number or renumber after filter in Excel step-by-step with 3 convenient approaches.


📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.


Learn to Establish Auto Number or Renumber After Filter in Excel with These 3 Easy Approaches

Today we will use the following sample dataset to learn how to auto number or renumber after filter.


Approach

1. Applying SUBTOTAL Function

In this approach, we will use the SUBTOTAL function to auto number or renumber after filter.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell B6 and Enter the following formula:

=SUBTOTAL(3,$C$6:C6)

how to auto number or renumber after filter using subtotal function

  • Now use the Fill Handle icon and double-click to apply the formula to the whole column.

  • The serial numbers are updated.

auto number or renumber after filter in excel applying subtotal function

  • Next, select cells B5:E14. Navigate to the Data tab and click Filter from the Sort & Filter group.

  • All the columns now have a drop-down icon.

  • Here we will click the drop-down icon of the Branch We check Austin, Fort Worth, and Houston. Click OK.

  • As you can see from the following screenshot the serial numbers have changed.

auto number or renumber after filter implementing subtotal function

  • Now if you want to sort your dataset according to the Sales, click the drop-down menu and select Sort Largest to Smallest.

  • The serial numbers have changed again accordingly.

📕 Read More: 4 Ways to Add Excel Sequence Number by Group


Approach

2. Implementing AGGREGATE Function

Now, we will use the AGGREGATE function to auto-number or renumber after applying filters.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, select cell B6 and type in the following formula, and press Enter:

=AGGREGATE(3,7,$C$6:C6)

how to auto number or renumber after filter using aggregate function

  • Use the Fill Handle icon and double-click.

  • Later, select cells B5:E14. Then apply Filter from the Data tab.

  • We want to show Austin, Fort Worth, and Houston. Click OK.

  • The serial number has changed according to the filtered data.

how to auto number or renumber after filter applying aggregate function

📕 Read More: 4 Ways to Repeat Rows a Specified Number of Times in Excel


Approach

3. Employing COUNTIF Function

Here, we will apply the COUNTIF function to achieve our goal. We will apply the COUNTIF function to the Branch.

⬇️⬇️ STEPS ⬇️⬇️

  • In cell B6, Enter the formula below:

=COUNTIF($D$6:D6,D6)

how to auto number or renumber after filter using countif function

  • Use the Fill Handle icon to apply the formula to the Serial No. Consequently, you will find the serial numbers are now based on the Branch names.

  • Afterward, select cells B5:E14. Next, Filter using a Branch from the Data tab and you will get a similar result just like in the following photo.

how to auto number or renumber after filter implementing countif function

📕 Read More: 5 Ways to Fill Down to Last Row with Data in Excel


📝 Takeaways from This Article

📌  Firstly, we used the SUBTOTAL function to auto number or renumber after filtering the dataset.

📌  Later, we applied the AGGREGATE function to auto number or renumber after executing Filter.

📌  Finally, we implemented the COUNTIF function to number according to the Branch names.


Conclusion

That concludes the discussion for today. These are some convenient methods to auto number or renumber after filter in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.


Related Articles

(Visited 59 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo