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…
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 ?
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: https://theexceltrainer.co.uk/gL33heMFofK7CuXFC2QJ/slicer-two-sources.zip
can we do the same in excel 2010
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)
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?
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.
Dear Mike,
I liked the video very much but could not apply it myself. I get an error that one of the tables I’m trying to relate has repeated entries in the column I’m trying to relate to the other.
I’m trying to relate a table with pacients with a table of known conditions of such patients, so that table has one or more entries per patient
how can I use the what you do on the video on my case?
thanks a lot
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.
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
Mike,
I am having a problem getting this to work. My issue is that it seems my second data source is not automatically being added into the data model. In your video once you’ve added the first data source into the data model, and then create the second pivot table using the worksheets data model you automatically are able to see both tables that you are able to choose fields from. I only still see the original table in mine. Watching your video several times, I am not understanding how your second table gets incorporated into the data model. Any suggestions you may have would be appreciated. Thanks!
hi. it doesnt work on my mac. i read somewhere that this was a windows-only feature, but that thread was 2 years ago.. does mac still not have this data table feature? i couldn’t find a fix. thanks
Hi
Does this apply to two pivot tables created from different SQL tables that share one field with the same items
Hi Mike, you video is exactly what I was looking for and trying to do. I followed/watched the video multiple time. I created the pivot tables exactly how you did. The issue I am having is that I am only getting 1 table module. It’s the second set of data/pivot table that is not creating a table. Where am I missing the boat? Frustrated..I am running Professional Plus 2013
Thank you,
Hi Mike, I have the same problem as AndrewK. I tried to work around it by creating a third table where the values weren’t duplicated. Then I added a relationship for Table 1 to the dummy table and another relationship of Table 2 to the dummy table. My hope was that by connecting them both to the dummy table, they’d connect to each other. It looks like it should work. For the slicer, I can select the 2 pivot tables I created. However, now I have the same problem as Richard. Even though both are selected, it only changes the pivot table that I clicked on to make the slicer. Do you know if this is just impossible if my original 2 tables have duplicate values?
Hi, both the columns should have unique data for creating the releationship, if not it is not working
What if the relationship between the two tables is the row labels and is the value you are trying to sum up. The relationship needs unique values in one of the tables.
This is wonderful, thank you! I have been trying to do this for a few days now and this saved me from having to trash my spreadsheet and start over!
Hi Mike,
I cannot create the unique column value from each data source and create a table and the pivot it. As my data source has multiple sheet and then connecting them to one slicer is being difficult. Can you suggest something for me please.
Thanks in advance.
How to i do this on a Mac with Office 2016/O365?
Hi Mike. Unfortunately it is not possible to do this on a Mac – the feature is not currently supported. The only workaround I can think of is to use VLOOKUPS to combine the data into a single data source and create your pivot tables and slicers based on that.
Hi Mike,
First of all, excellent video. Very clear and easy to follow.
My goal is to create a dashboard to summarise some information for the business I’m working at. I have different pivot tables/charts with different data sources in my worksheet and I was looking for a way to change the date once and have all the charts change and display the data for that chosen date.
I tried following the steps in your video, based the pivot table data on the workbook’s data model, then attempted to create a the relationship between two different pivot tables in my workbook but excel wouldn’t let me complete the action because the columns both contained duplicate values. The columns I selected contained duplicate dates (i.e. Multiple “10/9/19″‘s and multiple “6/2/18″‘s. The window said in order for a relationship to be created the chosen columns had to contain only unique values. Is there any way around this?
Thanks in advance.
Hi Mike
Is it possible to have the 2 different data sources on different worksheets rather than on the same worksheet?
Hi…yes the data can be on different worksheets
Great vid, wish my company allowed me to install excel 2013 or the plugin you recommended for excel 2010. Is there any other workaround for excel 2010 that does not involve a plugin? Or do I need to copy the solution you recommended for Mac users?
he window said in order for a relationship to be created the chosen columns had to contain only unique values. Is there any way around this?
Hi, Great video
I’ve followed it through all the way to the end but when I create the slicer at the end and connect it to the other pivot under report connects that seems to work but it will only change the 1 table.
Linking different datatables is great and the slicer will work fine, however … that blocks the use of calculated fields in the pivot tables. Hence the need of being able to link 2 pivot tables based on diffrent datatables so that they synchronise (not necessarily through a slicer) and you maintain the use of calculated fields.
Followed each step mentioned till the end & completed without any errors. But slicer changes values in “ONE” pivot table only.
Can you guide what might be the issue????
Same issue as reported by others. Slicer changes only 1 value. I’m using Office 365. Is there a solution to this?