Bottom line: Learn about the pivot cache that creates an invisible relationship between slicers and pivot tables. I also explain how to fix and prevent the connected filter controls error.
Skill level: Beginner
Video: Relationship Between Slicers, Pivot Tables, and Pivot Caches
The Connected Filter Controls Error
The following error message occurs when we try to change the source data range of a pivot table, and there is a slicer is connected it and other pivot tables.
Error message: The data source of a PivotTable connected to filter controls that are also connected to other PivotTables cannot be changed. To change the data source, first disconnect the filter controls from this PivotTable or from the other PivotTables.
Whoa, that's a long error message! Where it says “filter controls” it's referring to slicers.[divider style='centered']
The simple rule is: A slicer can only be connected to multiple pivot tables when those pivot tables share the same source data range (pivot cache).
When we try to change the source data range of one pivot table, then Excel will create a new pivot cache in the background based on the new source data range. This effectively breaks the rule above because we are trying to connect a slicer to two pivot tables with different source data ranges (pivot caches).
What is a Pivot Cache?
When we create a pivot table, Excel creates a pivot cache in the background. This pivot cache stores a copy of the data that is in the source data range.
Pivot tables share a pivot cache if they reference the same source data range. This helps reduce file size and prevents us from having to refresh each pivot table that shares the same source data range.
In this example, we have two pivot tables that have the same source data range. They are also connected by one slicer for the Region field.
Let's say we want to add new data to the bottom of the source data range. We now have to change the source data range for each pivot table. When we attempt to change the source data range for PivotTable1, Excel will create a new pivot cache in the background. It does this because PivotTable1 now has a different source data range from PivotTable2.
However, this breaks the rule above that a slicer can only be connected to pivot tables that share the same cache. So how do we fix it?
How to Fix the Connected Filter Controls Error
There are two ways to fix this error. Method #2 below will also prevent this error from occurring in the future.
Solution #1: Disconnect the Slicers First
The first method for getting around this error is to disconnect the slicers from the pivot tables before changing the source data.
This means we have to:
- Disconnect the slicers from all but one pivot table.
- Change the source data range for each pivot table.
- Reconnect the slicers.
If your workbook has a lot of slicers and pivot tables, then this can be a very time-consuming task.
Solution #2: Use Excel Tables for the Source Data Range of the Pivot Tables
This is my preferred solution because it will prevent the error from occurring all together.
When we use an Excel Table as the source data range for all of the pivot tables, the Table Name is used to reference the source data range. Instead of referencing ranges with column letters and row numbers, we just reference the table name.
When new rows or columns are added to the Table, they are automatically included in the Table's range. We do NOT need to update the source data range of the pivot table. It just includes everything that is included in the Table's range.
This acts like a dynamic named range, and means we never have to change the source data range when new data is added.
This also means that the pivot tables will always share the same pivot cache. Therefore, we don't have to worry about breaking the rule and causing the connected filters control error.
Checkout my video on a Beginners Guide to Excel Tables if you want to learn more about this awesome feature of Excel.
PivotPal Helps with Cache Confusion
My PivotPal Add-in has a feature that allows us to see which pivot cache is used by each pivot table.
This feature can save a lot of time when updating your source data ranges, and trying to resolve errors with slicers. It gives us information that cannot be found anywhere in the Excel application.
The PivotPal Add-in is packed with additional features that will save you a lot of time when working with pivot tables.
Pivot Relationships are Complicated
The relationships between pivot tables and pivot caches can get complicated. Especially since the pivot cache is stored in the background, and there is no way to see which pivot tables are sharing a pivot cache in the workbook.
I hope this article helps provide a little clarity as to what is going on with the pivot table and slicer relationship. If you are new to pivot tables then checkout my 3 part video series on pivot tables and dashboards. Pivot tables are an amazing tool that can save us a ton of time when summarizing and analyzing data, and they are not that difficult to learn.
Please leave a comment below with any questions. Thank you! 🙂