Excel Slicers – 2 Pivot Tables Based on Different Data Sources

I have been asked this question several times “I have 2 (or more) pivot tables in a file. The pivot tables are not connected to the same data source (i.e. each one is associated with a different table within the workbook). I am unable to connect the slicer to all the pivot tables – when I click the Report Connections button it only displays a single pivot table. How can I attach a Slicer to both/all pivot tables?”

This video explains how to do it…

8 Comments

Richard
on November 15, 2017 at 8:42 pm

Great video. Nice and easy to follow. However when I have tried this in Excel 2013 and 2016 I could relate the single slicer to both pivot tables but only the one pivot table changes. Am I missing something ?

Mike Thomas
on November 24, 2017 at 8:07 am

Hi Richard. I have just been through the steps again and it’s working for me. However, I did hit your problem when I created the slicer if the cursor was in the FIRST pivot table at the time I created the slicer and I used the “related field” (i.e. Office) as the Slicer field. I’ve uploaded the file used in the video if you want to download it and have a play. You can get it here: http://theexceltrainer.co.uk/gL33heMFofK7CuXFC2QJ/slicer-two-sources.zip

zeeshann
on August 10, 2018 at 7:44 am

can we do the same in excel 2010

Mike Thomas
on August 13, 2018 at 8:43 am

In Excel 2010 you do not have the “add to data model” checkbox so you will need to download and install Power Pivot (a free addin)

AndrewK
on October 30, 2018 at 7:09 pm

Hi Mike. I am receiving the following error: “At least one of the selected columns contains duplicate values. Both selected columns must contain only unique values to create a relationship between the tables.”

My common variables which I would like to create the slicers off of are definitely not unique. For example, State is one of the variables, and I might have 20 entries for one specific state, in each dataset. However, it looks like in your example, you also have non-unique entries for “Office” in your Employees tables. How would I get around this error?

Mike Thomas
on November 10, 2018 at 6:03 am

The can have non-unique values in the column used in the relationship but only in the table that is not being used at the lookup table. Also you must not have any blanks in that column.

Chandra
on January 23, 2019 at 5:01 am

Hi Mike, I am having the same Q zeeshann asked above. Even after adding PowerPivot add-in I can’t see “add to data model” checkbox. Do I have to do something else. I can see the ‘PowerPivot’ menu option on top.

Mike Thomas
on January 23, 2019 at 9:44 pm

Hi Chandra

Sorry I don’t think I made it clear. Even if you install Power Pivot, you still don’t get the “Add to Data Model” checkbox if you are using Excel 2010. You will need to use the features of Power Pivot itself. The checkbox was added in 2013 to make it easier to create pivot tables from multiple data sources