Power Query Problems

How to Use One Slicer to Filter Two Pivot Table Fields in Excel

Phi Hung Vo Sep 30, 2026 869 views

Question details

The user wants to use a single slicer to filter events across two different theme columns in a pivot table, without altering the structure of the original calendar planning table.

How to Use One Slicer to Filter Two Pivot Table Fields in Excel

Product: Microsoft Excel
Device & OS: Windows and macOS
Scenario: Creating an event dashboard where each event might have two themes (Theme 1 and Theme 2), requiring a unified slicer to filter by theme across both columns.
Observed behavior: Standard pivot tables create separate slicers for separate columns, making it impossible to natively filter both Theme 1 and Theme 2 simultaneously with a single slicer selection.

Before you start

Ensure your source data is formatted as an official Excel Table (press Ctrl+T) and verify that you have access to Get & Transform Data (Power Query) in your Excel version.

Solution 1Recommended

Use Power Query to Unpivot Columns (Recommended)

Use Power Query to flatten the two theme columns into a single column. This allows you to create a unified pivot table and slicer while keeping the original table intact.

By unpivoting the data, Power Query creates a new background table where both Theme 1 and Theme 2 are merged into a single 'Theme' column. This accurately assigns themes to events and enables a single slicer.

Load data into Power Query

Select any cell in your original data table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Unpivot the theme columns

Hold the Ctrl key and click the headers for both 'Theme 1' and 'Theme 2' to select them. Right-click either header and choose 'Unpivot Columns'. This flattens them into 'Attribute' and 'Value' columns.

Clean and load the data

Rename the newly created 'Value' column to 'Theme'. You can right-click and remove the 'Attribute' column if it is not needed. Click 'Close & Load To...' from the Home tab and select 'PivotTable Report'.

Add the slicer

In the new Pivot Table, drag your fields into the rows and values areas. Right-click the new 'Theme' field in the PivotTable Fields pane and select 'Add as Slicer'.

Use Power Query to Unpivot Columns (Recommended)

Refreshing Data: When you add new events or modify themes in the original table, simply right-click the Pivot Table and select 'Refresh'. Power Query will automatically process the new data.

Solution 2

Manually Flatten the Data (For Excel for Mac Limitations)

If you are using an older version of Excel for Mac that lacks the 'From Table' button in Power Query, you will need to flatten the data manually or adjust your data collection method.

Free Microsoft Office alternative

Analyze Data Seamlessly with WPS Office

If navigating complex Power Query setups across different operating systems is slowing you down, WPS Office provides a lightweight, highly compatible alternative. It natively supports .xlsx formats, Pivot Tables, and Slicers, helping you manage dashboards without a steep learning curve.

1. Download and Install: Download WPS Office for free and install it on your Windows or Mac computer.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing Excel workbook containing your event data.
3. Utilize Pivot Tables: Navigate to the Insert tab to seamlessly create Pivot Tables and add Slicers to analyze your data efficiently.

Fully compatible with Microsoft Excel (.xlsx) files, standard Pivot Tables, and Slicers.Lightweight and fast, offering a smooth experience on both Windows and macOS.Free to use with a familiar, easy-to-navigate tabbed interface.Built-in advanced data analysis and visualization tools without complex configurations.

microsoft office alternative - wps office

Frequently Asked Questions

Can I connect one slicer to two different Pivot Tables?

Yes. If both Pivot Tables are built from the same underlying data source or Power Query connection, you can right-click the Slicer, select 'Report Connections', and check the boxes for all Pivot Tables you want it to control.

Why does my Excel for Mac not have the 'From Table' button in Power Query?

Older or non-subscription versions of Excel for Mac have limited Power Query authoring capabilities. Upgrading to the latest Microsoft 365 version for Mac unlocks full Power Query support, including the 'From Table/Range' option.

Will unpivoting my data cause duplicate event counts in my Pivot Table?

Yes, unpivoting creates a new row for each theme. If an event has two themes, it appears twice in the background data. When analyzing total unique events, you may need to use 'Distinct Count' in your Pivot Table's Value Field Settings to avoid double-counting.

How do I update my Power Query Pivot Table when the original data changes?

Navigate to the 'Data' tab on the ribbon and click 'Refresh All', or simply right-click anywhere inside the Pivot Table and select 'Refresh'. Power Query will automatically pull the new data, unpivot the columns, and update your dashboard.

Excel & Data Problems