1. Home
  2. MS Office
  3. How to auto refresh filtered data in excel when it is updated

How To Auto-Refresh Filtered Data In Excel When It Is Updated

Microsoft Excel lets you filter data. It’s a useful feature that can help you make sense of large amounts of data. If you lack the technical know-how needed to create a database, this feature is a life saver. The filters are fairly basic and allow you to include or exclude data from a column. The filter extends to and applies to data values in the corresponding rows as well. What the filter doesn’t do is auto-update itself. If a value in a filtered column changes, either because you manually updated it or a formula is in use, you have to apply the filters again. A simple code snippet allows you to update filtered columns on the fly.

You will have to enable Macros and save your Excel file as a macro enabled file, .XLSM, in order to use the code. To save the file as a macro enabled file, go to File>Save As and select Excel Macro Enabled Workbook from the ‘Save As’ dialog box.

Next, select the sheet you have applied filters to. Right-click it and select ‘View code’ from the context menu.

A Microsoft Visual Basic window will open with the current Excel sheet. Paste the following code in the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.FilterMode = True Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

With ActiveWorkbook
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If

End Sub

Save the file. If you didn’t save the file as an Excel Macro Enabled Workbook, you will be prompted to do so again. This file that you save will be saved a copy and will not overwrite the current file.

Once the code has been added, you can update any cell in a filtered column and it will auto-refresh filtered data as per the conditions you’ve set. This code was written by Stack Exchange user Sorin.

1 Comment

  1. Did not work for me. The data has cells that reference a value outside the table. When that cell changes value, the cells in the table change value, but I have to manually refilter.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.