How to Keep Different Filters in PivotTables Sharing Slicers
Question details
The user needs one PivotTable to retain a Top 10 filter while another displays all items, but both currently inherit the Top 10 result despite sharing connected slicers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating multiple PivotTables from the same source data with connected slicers, but requiring independent value filters (like a Top 10 filter) on each table.
- Observed behavior
- Applying a Top 10 filter to one PivotTable automatically applies it to the second PivotTable because they share the same underlying pivot cache.
Ensure your Excel version includes the Power Pivot add-in and supports Data Model features, as you will need this to decouple the PivotTable caches.
Use Power Pivot and the Data Model to Separate Pivot Caches
By building your PivotTables from the Excel Data Model instead of the standard worksheet range, you can maintain separate filter contexts while still keeping your slicers connected.
Standard PivotTables created from the same data range share a single memory block known as a pivot cache. Because of this shared cache, row/column filters like 'Top 10' applied to one table will automatically force the other connected tables to inherit the filter. By adding your data to the Data Model, you force independent evaluation, allowing separate table-level filters.
Go to File > Options > Add-ins. From the Manage drop-down at the bottom, select COM Add-ins, click Go, and check the box for 'Microsoft Power Pivot for Excel'.
Select your source data range (formatted as a Table), navigate to the Power Pivot tab on the ribbon, and click 'Add to Data Model'.
From the Power Pivot window, click PivotTable, or from the standard Insert tab in Excel, choose PivotTable and select 'From Data Model'. Place it on your desired worksheet.
Repeat the previous step to insert your second PivotTable on the worksheet, ensuring it is also sourced from the Data Model rather than the standard cell range.
Add your slicer to the first PivotTable. Right-click the slicer, select 'Report Connections', and check both tables. You can now apply a Top 10 filter to the first table without it affecting the second.
Experience Seamless Data Analysis with WPS Office
If you find Microsoft Excel's add-ins and cache management overly complex or expensive, consider switching to WPS Office. It provides a lightweight, highly compatible, and free alternative for managing spreadsheets, PivotTables, and advanced data analysis without the steep learning curve.
- 1. Download and Install WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Open Your Workbook: Launch WPS Spreadsheet and seamlessly open your existing .xlsx file containing the PivotTables.
- 3. Manage Data Easily: Use the familiar ribbon interface to analyze data, adjust slicer connections, and format PivotTables.
Frequently Asked Questions
Why do my PivotTables automatically share the same filters?
When multiple PivotTables are created from the exact same data range in standard Excel, they share a single underlying memory cache called a Pivot Cache. This optimizes file size and performance but causes them to mirror certain row filters, grouping, and calculated items.
Can I disconnect a slicer from specific PivotTables?
Yes. You can right-click the slicer, select 'Report Connections' (or 'PivotTable Connections' in older versions), and uncheck the boxes next to the PivotTables that should not be controlled by that specific slicer.
Is there a way to separate pivot caches without using Power Pivot?
Yes, you can force Excel to create a separate pivot cache by using a defined Named Range for the second PivotTable instead of the exact cell references, or by using the classic PivotTable Wizard shortcut (Alt+D, then P) and clicking 'No' when asked to share the memory cache.
