If you have tried to change the data source for a Pivot Table and received a message saying ‘to change the data source first disconnect the filter controls’ you may have struggled to understand what it is saying.
As shown below this will typically happen when a slicer is involved AND the slicer has connected at least 2 pivot tables so that when you change the slicer option it changes both Pivot Tables (to learn how to do this look at our Pivot Table Course ).
You will need to disconnect the slicer from the multiple Pivot Tables and, change the source and then reconnect the slicer if you want the functionality.
Depending on the amount of work it may just be easier to delete the slicer and then recreate it once the source is changed.
If this is not an option you will need to check the slicers. If you can see them, just:
- (1) click on them (might need to check each one)
- (2) click on the Options ribbon that appears
- (3) Choose Report Connections
- (4) switch off any connections to other Pivot Tables.
You should now be able to change the data source.
What if you can’t see the slicer?
If the slicer is not easily visible, you can use the GOTO Special tool to find all the Objects (a slicer is seen as an object- you can learn more about GoTo Special in the Intermediate Excel Course ).
To find them, on each sheet (this tool works sheet by sheet and slicers could be placed anywhere) click on:
- The Home Tab
- Click on Find & Select
- Click on GO TO Special
This will appear. Choose the Objects option and click OK.
Excel will ‘click’ on all objects. You need to be careful though because it is not only slicers that are considered objects. You may also have ‘clicked’ on pictures and shapes.
You can now investigate any objects found, or, if you need to, delete all the objects and make the change. Just remember to look in all the sheets and perhaps look for hidden sheets.