Excel Miracle: How to Sync Filters Across Multiple Sheets (Finally Solved)

Syncing Excel Filters Across Sheets: The Impossible Solved

TUTORIAL December 05, 2025 | By The Tech Mirror

Stop what you are doing. If you are manually toggling back and forth between two tabs in Microsoft Excel just to apply the same filter twice, you are wasting hours of your life.

It sounds like a basic feature, right? You filter "Column A" in Tab 1, and Tab 2 should automatically update to match. But here is the harsh reality: Excel does not support this natively.

🔥 THE QUICK FIX (TL;DR):
1. Native Failure: Excel filters are "local" to the specific worksheet.
2. The Workaround: You must use a short VBA script to "listen" for changes.
3. The Alternative: If you use PivotTables, "Slicers" are the only non-code way to link filters.

The Problem: Why Can't Excel Do This?

The architecture of Microsoft Excel treats every worksheet as an isolated island. When you turn on "AutoFilter," it only looks at the data range on the current active sheet.

However, your use case is brilliant. You have Values on Tab 1 and Formulas on Tab 2. You want to see the calculated results on Tab 2 for the exact same items you just filtered on Tab 1.

Since there is no button for this, we have to build one using VBA (Visual Basic for Applications). Don't worry—it’s copy-paste simple.

The Solution: The "Mirror Filter" Script

We will use a VBA script that triggers whenever you switch tabs. When you filter Tab 1 and click on Tab 2, the script will instantly read the filter criteria from the first sheet and apply it to the second.

Step 1: Prepare Your Data

Ensure that Column A in "Sheet1" and Column A in "Sheet2" contain the exact same type of data (e.g., IDs, Names, or Dates). If the data doesn't match, the filter will hide everything.

Step 2: Open the VBA Editor

Press ALT + F11 on your keyboard to open the developer window.

Step 3: Insert the Magic Code

On the left sidebar, double-click on ThisWorkbook. Paste the following code into the white window. This code runs every time you change sheets.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim wsSource As Worksheet

    Dim wsDest As Worksheet

    Dim filterRange As Range

    Dim criteria As Variant

    

    ' DEFINE YOUR SHEET NAMES HERE

    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' The sheet with values

    Set wsDest = ThisWorkbook.Sheets("Sheet2")   ' The sheet with formulas

    

    ' Only run if we are activating the destination sheet

    If Sh.Name <> wsDest.Name Then Exit Sub

    

    ' Turn off screen updating for speed

    Application.ScreenUpdating = False

    

    ' Clear existing filters on destination to avoid errors

    If wsDest.AutoFilterMode Then wsDest.AutoFilterMode = False

    

    ' Check if Source has a filter applied

    If wsSource.AutoFilterMode And wsSource.FilterMode Then

        ' We assume the filter is on Column A (Field 1)

        With wsSource.AutoFilter.Filters(1)

            If .On Then

                criteria = .Criteria1

                ' Apply the captured criteria to Sheet 2

                wsDest.Range("A1").AutoFilter Field:=1, Criteria1:=criteria

            End If

        End With

    End If

    

    Application.ScreenUpdating = True

End Sub

IMPORTANT: Change "Sheet1" and "Sheet2" in the code above to match your actual tab names exactly.

Method 2: The Non-Coding Alternative (Slicers)

If your company blocks macros or you are using Excel Online, you cannot use VBA. Your only alternative is to convert your data into Excel Tables and use Slicers.

Note: Standard Slicers usually control one table. To control two, you often need to pivot the data or use a Data Model connection, which is significantly more complex than the VBA method above.

Summary of Results

Once you save the file as an Excel Macro-Enabled Workbook (.xlsm), here is your new workflow:

• Action: You filter "Column A" on Tab 1 to show only "Project X".
• Trigger: You click the tab for "Tab 2".
• Result: Tab 2 instantly updates, hiding all rows except "Project X".

Frequently Asked Questions

Can I filter multiple columns at once?

Yes, but the code above is specific to Column A (Field 1). To filter multiple columns, you would need to expand the VBA script to loop through all columns in the AutoFilter range and copy criteria for each one.

Does this work in Excel Online (Browser)?

No. VBA Macros do not run in the browser version of Excel. This solution works only on the desktop versions of Excel for Windows and Mac.

Why did my filter stop working?

Ensure you saved the file as .xlsm (Macro-Enabled). Also, check that your column headers in both sheets are in the first row (A1) and that the data in Column A matches exactly between sheets.

Is there a formula-only solution?

You can use the Office 365 =FILTER() function to pull data from Sheet 1 to Sheet 2 based on criteria, but this creates a copy of the data. It does not hide rows in an existing dataset of formulas like the user requested.

Will this slow down my spreadsheet?

The impact is negligible for standard datasets. However, if you have hundreds of thousands of rows with complex formulas on Sheet 2, the recalculation that occurs after filtering might take a second or two.