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
Click here to watch the video in your browser
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.
Click here to learn more about PivotPal
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! 🙂
Excellent video. You really make it very clear what the cause of the error is and how to solve it but how to avoid it occurring in the first place. Thanks a lot.
Thank you Rob! 🙂
Thank you so much. I have watched a number of your videos for various project, which are all so helpful. I am so glad to get on your mailing list for tips.
Thank you Cathy! I’m happy to hear you are enjoying the videos. 🙂
This is interesting. Thank you very much for sharing very clear information with all of us.
Thank you Chaminda! 🙂
Good article Jon, well done. Since discovering Excel Tables, I always put my data into an ET because of the many advantages and the not so many, zero as far as I can tell, disadvantages. Your preferred solution, #2, also makes use of ETs. Is there a reason why ETs should be used for all one’s data lists? I know it is off topic w.r.t. this article, but it would be good to hear your view.
And if you agree with my view about ETs people should be made more aware of them.
Thank you Klaas. I am a big fan of using Excel Tables. For me, the advantages far outweigh the disadvantages. I believe the number one thing that stops people from using Tables are the structured reference formulas. This is a new language to learn with its own rules, and it is a bit confusing at first. I believe a lot of users prefer regular cell references (A2) over structured references ([@Column1]).
So, it’s best to consider your users when developing Excel files with Tables. If the users of your files are not skilled Excel users, then they will find it difficult to understand the structured reference formulas. You will have to train them on how to read and use structured references. I have experienced difficulty with this, even with experienced Excel users.
All-in-all I still love Tables. That is just one of the major disadvantages I have experienced. I hope that helps answer your question. Thanks again!
this Excel tip is so cool!
Thank you Matteo! 🙂
Hi Jon,
i am trying to connect slicer with 2 pivot table with 2 different data source , but not getting option to connect slicer…
Can you please help me..
Hi Rakesh,
The pivot tables must share the SAME data source to connect multiple pivots to one slicer. To get around this you can use Power Pivot to create relationships between the data sources.
Hi Jon, my Slicer only work for 1 of 2 pivot tables. The 2 pivot tables have 2 different source tables, related in the data model. Please assist.
I required, I can upload a sample file.
Thank you in anticipation.
Hello Jon
Thank you for your informative Videos. Helped alot.
I am however facing a problem on my Dashboard that maybe you can help me with. On the dahsboard I copied to different Chart tables that share the same data source but Show different data analyses combination. I have 2 different slicers for each. I want to show only one month on one Chart using the 1st slicer and all 3 months on the 2nd Chart table using a 2nd slicer! The slicers are connected and using the method you explained in your Video above did not work! Any tips? Thabk you
Hi Wed,
If the slicers are connected to both pivot tables, then they will apply the same filter to both pivots. You will need to disconnect the slicer from both pivots and create a new slicer that is only connected to one pivot. I hope that helps.
Hi Jon,
I have a question. I have created a dashboard tab listing all the slicers and I have some 15 different tabs having pivots showing different kind of information with multiple filters in each pivot. All the pivots are created based on the single data source and all the slicers in the dashboard tab are connected to all of the pivot tables.
Question – Can I have multiple filters in pivot table and use slicers to see the information in each tab? Ex. Pivot 1 has bookings filtered by 4 years and pivot 2 has bookings filtered by 5 qtrs. In Slicer, can I select 1 sales rep name and see the values in both the pivots for the sales rep?
And, Can we apply filters in pivot tables showing different views and also use Slicers which are linked to these pivots to further drill up/down?
Hi Mudassir,
Yes, the slicer can be used to filter pivot tables on multiple sheets if those pivot tables share the same source data. You can set this by:
1. Right-clicking the slicer and selecting Report Connections or Pivot Table Connections.
2. Check the checkboxes for all pivot tables you want connected. You will see all the pivot tables across multiple sheets listed there.
I hope that helps. Thanks!
Hi Jon,
I tried Solution #2 however it doesn’t seem to work probably because my source table is located in a separate workbook from the pivot table and slicers. Is there any way around this?
Hi Amanda,
You should still be able to create the pivot table from a Table in a different workbook. When you select the data it will reference the range address, but you can change that to the Table name.
‘\Users\username\Excel\Pivot.xlsx’!A1:Z5000
‘\Users\username\Excel\Pivot.xlsx’!Table1
I hope that helps.
Hi Jon,
Would solution 2 work if my data source is in a different workbook from the pivot table and slicers? I keep the data source in a separate workbook due to its size (60mb).
Hi Jon!
This is a great article. I have 1 excel file “Weekly YoY Family Trends v4.28.2017.xlsb” with many pivot tables. Many of my pivot tables reference a table called ‘Family’ rather than referencing specific columns and rows of the table (I have other tables that reference a different table name – all within the same file – just FYI). When I click to look at the data source – it just says ‘Family’ which is what I want! Now, I want to change the name of my excel file. Normally, I would go to File, Save As, and save my file as a new name “Weekly Trends”. When I do this, and I click on the pivot tables to see the data source, they are referencing the old file name. Is there a way to change my excel file name to “Weekly Trends” (this is now what I see for the data source: ‘Weekly YoY Family Trends v4.28.2017.xlsb’!Family) , without having to go into every pivot table to update the data source to ‘Weekly Trends.xlsb’!Family) ?
Hi Jon,
In pivot, I grouped some columns as required and named the bunch of grouped filed as Departments, then again I grouped those departments as required and named as segments.
But when insert slicer for segments it is showing department names only.
Hope you understood my query..please help me as it will help me a lot..
Thanks!
HI Jon, great video and exactly what I needed. I have a question with slicers that I need help with. I have a dashboard set up with multiple pivot tables and slicers. I want to have the slicers filter to only show available options from the previous slicer selection. An example is that I have data for a fiscal year, site, manager. When I select the slicer for fiscal year to one specific year then select a specific site from the site slicer I want the manager slicer to only show those managers at that site. The problem that I’m having is that the manager slicer is still showing all the managers from my data. How can I fix this?
Thanks,
Robert
Hello,
I am getting error can you help?
Excel error this type of workbook connection cannot be used to insert a slicer. to insert a slicer connected to a pivottable using this workbook connection select the pivottable and then insert the slicer
thank you
mendy
Hi Jon,
Is it possible to have the same slicer affecting at the same time the Workbook and the Pivot table? Exemple, If in the pivot table slice I click on Paris, the pivot table will filter Paris, and the workbook will also filter Paris.
Thank you. ¸
Thank you so much Jon. It was really very well narrated and explained. This video was a great help.
I needed to create you a little observation so as to thank you very much again over the lovely thoughts you have featured at this time. It has been so pretty open-handed of people like you to provide easily all that a lot of folks might have supplied as an e book to end up making some bucks on their own, precisely now that you might have tried it if you desired. Those creative ideas in addition worked to become great way to fully grasp someone else have similar dream really like my personal own to understand lots more in regard to this matter. I think there are some more pleasurable opportunities in the future for people who scan your site.
I have pivot chart with slicer.
But when i export that pivot chart sheet to new workbook the report connection to slicer is automatically reset.
Please suggest solution.
Amazing Jon, well done
Super helpful and clear. Thanks
Years after the video was posted, it is still providing much needed help is a wonderfully didactic fashion. Many thanks!
I have 2 pivot tables created from 2 different tables. I created a relationship between the 2 tables, which is a 1 to many relationship using the common field. I created a slicer but it did not sync the 2 pivot tables. Kindly advise. Thank you.
Giselle, I have the same issue as you. Have you found a solution?
option 2 is nice, but how does that work when the table is on another tab?
Can anyone help me , If i copy slicer table data and past in to new excel sheet immediately share workbook option got disabled due to same reason we are not able to open excel sheet two members same time .
Hi Jon
I am a big fan of you and I have learned so much with your tutoring so thank you very much. I am doing a project on all the falls in the hospital by department in one of the public hospital in sydney.
I have a huge data for 3 years and I have created a pivot table and slicer and a 10 graphs in dashboard. The main filter is by wards but some criteria is by year and some by months so I have created 2 different pivot table. 4 graphs by year and 6 graphs by month now how do I link these two slicer in the dashboard. I desperately need your help.
I have pivots based on a model that are all connected via slices in a dashboard. When I refresh the model, any changes that were made to previously added tables from the data source of the query (files from a folder) will refresh correctly in the model, but all the pivots cannot be changed. None of the filters can be changed either by slicer or from the individual pivots. Nor can the slicers be disconnected.
Great post! I never realized how interconnected slicers and pivot tables could be until now. The troubleshooting tips for filter controls were especially helpful. Thanks for breaking it down so clearly!