How Slicers and Pivot Tables are Connected + Filter Controls Error - Excel Campus
23

How Slicers and Pivot Tables are Connected + Filter Controls Error

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.

pivot-table-connected-to-filter-controls-error

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.

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).

how-slicers-and-pivot-tables-are-connected

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).

slicers-cannot-be-connected-to-pivot-tables-with-different-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.

the-relationship-between-source-data-pivot-cache-pivot-tables

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.

example-of-2-pivot-tables-connected-to-one-slicer

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.

disconnect-the-slicers-before-changing-the-source-data-range

This means we have to:

  1. Disconnect the slicers from all but one pivot table.
  2. Change the source data range for each pivot table.
  3. 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.

use-an-excel-table-as-the-source-data-range-of-a-pivot-table

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.

PivotPal Pivot List Feature Shows List of Pivot Tables and Cache Index Number

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.

PivotPal Box 250x250

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 23 comments
Mendy Rodriguez - November 16, 2017

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

Reply
Robert - August 12, 2017

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

Reply
Dhaval Maisheri - June 13, 2017

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!

Reply
Kayla - April 28, 2017

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) ?

Reply
Amanda - April 23, 2017

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).

Reply
Amanda - April 18, 2017

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?

Reply
    Jon Acampora - April 24, 2017

    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.

    Reply
Mudassir Ahmed - March 21, 2017

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?

Reply
    Jon Acampora - March 29, 2017

    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!

    Reply
Wed - December 27, 2016

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

Reply
    Jon Acampora - December 27, 2016

    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.

    Reply
Rakesh Choubay - December 26, 2016

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..

Reply
    Jon Acampora - December 27, 2016

    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.

    Reply
Matteo Olla - October 2, 2016

this Excel tip is so cool!

Reply
Klaas Vaak - September 24, 2016

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.

Reply
    Jon Acampora - September 27, 2016

    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!

    Reply
Chaminda - September 23, 2016

This is interesting. Thank you very much for sharing very clear information with all of us.

Reply
Cathy Bellue - September 22, 2016

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.

Reply
Rob Dawson - September 22, 2016

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.

Reply

Leave a Reply: