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
.CustomViews("Mine").Show
.CustomViews("Mine").Delete
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.

Read Full Bio

Fatima Wahab

Fatima has been writing for AddictiveTips for six years. She began as a junior writer and has been working as the Editor in Chief since 2014. Fatima gets an adrenaline rush from figuring out how technology works, and how to manipulate it. A well-designed app, something that solves a common everyday problem and looks

Latest from Author

How to Take a Screenshot on Windows Best FPS Counter Software for Windows