How to Automatically Reapply Auto-Filter When the Data Changes in Excel?

In Excel, a filter is used to group data based on a command value in any column. We can apply a filter to a table using the slicer. When we change the data in the filtered column, we can see that it will not apply to filtering. We need to close the filter and reapply it to update the data. This might create confusion when we are frequently changing the data in the filtered data. This tutorial will help you understand how we can automatically reapply the auto filter when data changes in Excel.

Here we will first insert a table, then use the slicer to complete our task. Let's look at a simple process for automatically reapplying the auto filter when data changes. We can complete this process by using the VBA application.

Step 1

Consider an Excel sheet with data that is similar to the data shown in the image below.

Now we need to create a table, select the data, and then click on "Insert" and "Select table" to get the table as shown in the below image.

Step 2

Now to apply the filter, click on any cell of the table and click on insert, then click on slicer under filter, then select your criteria, then click OK to get the slicer.

Step 3

Now right-click on the sheet name and select View Code to open the VBA application, and enter the below programme in the textbox.

Example

Private Sub Worksheet_Change(ByVal Target As Range)
   'Updated By Nirmal
   Sheets("Sheet1").AutoFilter.ApplyFilter
End Sub

In the code, Sheet1 is the name of our sheet.

Step 4

Now save the sheet as a VBA-enabled template and close the VBA application using the command "Alt + Q". Then, every time we change the data in the filters, they will be updated automatically.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can automatically reapply an autofilter when the data changes in Excel.

Pradeep Kumar

Updated on: 11-Jan-2023

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started