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.

Contents hide

📁 Download Excel File

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

1. Applying SUBTOTAL Function

2. Implementing AGGREGATE Function

3. Employing COUNTIF Function

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

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

Auto Numbering or Renumbering After Filter.xlsx

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)

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

The serial numbers are updated.

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.

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)

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.

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)

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.

📝 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.

(Visited 52 times, 1 visits today)