# How to connect 1 Slicer to 2 different PivotTables - Excel 2010



## Legacy 227120

I am working with PivotTables and Slicers. However if I can first explain the scenario:


I am collating data from a table that is updated every month. I transfer the table into Excel 2010. The column titles on the monthly tables are always the same, it is the data that changes.
For example, I have the xls spreadsheets from August 2012 and September 2012. From these 2 tables I create a PivotTable for both months, structuring them exactly the same.
I then Place both PivotTables side by side on the same page.
I then inserted a Slicer for table A.
I would like to know if I can get the Slicer to control BOTH PivotTables at the same time, bearing in mind that both PT's are drawing their data from different sources.
Please help!


----------



## Norie

How can a Slicer control a pivot table?


----------



## Legacy 227120

Norie said:


> How can a Slicer control a pivot table?




Maybe 'control' was the wrong word to use then. A slicer helps you to manipulate the data you want displayed.


----------



## Norie

How are you using it for the first pivot table?


----------



## Legacy 227120

PT1 draws its data from Table1. Once I have selected the rows and columns I want displayed I then click anywhere on PT1, go to the 'Options' tab, then 'Insert Slicer'.

PT2 draws its data from Table2. I select exactly the same structure for PT2 as I did for PT1 (as the column headings for both Table1 and Table2 are the same). So that both PT1 and PT2 show exactly the same headings, but different data for different months of the year.

I then paste PT2 next to PT1, on the same worksheet. At this point I want the Slicer I inserted earlier for PT1 to be able to 'control' both PT's (ie. when I select something from the Slicer, I want it to change both PT's at once to display the same fields, albeit with different data). I want to use this as a comparison tool to compare information for two different months worth of data.

Hope this helps!


----------



## MD610

I have 2 suggestions you could try...

1. I think the easiest would be to combine your 2 data sets before importing them into PowerPivot.  If they are formatted identically, then it should be easy.  Then you have all your data in a single table, you can still compare one month to the next in a single PivotTable controlled by the slicer.

2. Leave the data in 2 separate tables.  Create new tables with the unique values for each slicer you want to use and then relate the new slicer tables back to your to original data tables.  You will need one slicer table of unique values for each slicer.  After you set up your 2 separate pivots, use the new slicer tables to create your slicers.  Since they are related to both tables, they will manipulate both tables.


----------



## Legacy 227120

Thanks buddy

However I managed a work around: I still have my 2 PT's side by side, with a slicer for each. I then recorded a macro to change each slicer simultaneously at the touch of a button. Good enough for now.


----------



## MD610

Any solution that works is a good solution!

One thing to remember with macro's and PowerPivot is that currently excel services in SharePoint doesn't support macros in online workbooks but if you will never be hosting this workbook online then it isn't an issue for you.


----------



## Legacy 227120

Thanks again MD610. I did not know that about SharePoint, however I wont be publishing this for the time being. Helpful info tho!


----------



## Hartke2421

FShaikh,  Can you show the macro that you recorded?  I'm dealing with a similar issue and can't figure out how to sync multiple slicers that deal with similar data sets.


----------



## Legacy 227120

I am working with PivotTables and Slicers. However if I can first explain the scenario:


I am collating data from a table that is updated every month. I transfer the table into Excel 2010. The column titles on the monthly tables are always the same, it is the data that changes.
For example, I have the xls spreadsheets from August 2012 and September 2012. From these 2 tables I create a PivotTable for both months, structuring them exactly the same.
I then Place both PivotTables side by side on the same page.
I then inserted a Slicer for table A.
I would like to know if I can get the Slicer to control BOTH PivotTables at the same time, bearing in mind that both PT's are drawing their data from different sources.
Please help!


----------



## Legacy 227120

Hi Hartke2421

The Macro code is listed below, where DNSP, PSNSP, GCNSP are the 3 fields in each slicer. And True/False refers to which fields need to be displayed at the same time. I hope this helps. If not, let me know and I will run you through how I recorded the macro.



		Code:
__


[B]Sub DNSP()[/B]
[B]'[/B]
[B]' DNSP Macro[/B]
[B]'[/B]

[B]'[/B]
[B]    With ActiveWorkbook.SlicerCaches("Slicer_Type")[/B]
[B]        .SlicerItems("DNSP").Selected = True[/B]
[B]        .SlicerItems("GCNSP").Selected = False[/B]
[B]        .SlicerItems("PSNSP").Selected = False[/B]
[B]    End With[/B]
[B]    With ActiveWorkbook.SlicerCaches("Slicer_Type1")[/B]
[B]        .SlicerItems("DNSP").Selected = True[/B]
[B]        .SlicerItems("GCNSP").Selected = False[/B]
[B]        .SlicerItems("PSNSP").Selected = False[/B]
[B]    End With[/B]
[B]End Sub[/B]


----------



## Hartke2421

Thanks, I'll try to work with this, but I think I need the macro to be a little more dynamic.  I want the second slicer to mimic whatever is selected in the first slicer.


----------



## Jerry Sullivan

Hi Hartke2421, Are you using PowerPivot or just Excel PivotTables?

There are many code examples on this forum that show how to sync the manual filters on PivotTables so that when a Master is changed, all the other Pivots with the same field have the same filters applied. 

That code could be modified to do a similar process with slicers instead of manual filters.


----------



## Legacy 227120

Hartke2421 said:


> Thanks, I'll try to work with this, but I think I need the macro to be a little more dynamic.  I want the second slicer to mimic whatever is selected in the first slicer.




No worries, this macro does exactly what you require (as I needed the same thing). To be honest, in this case it would be easier simply recording the macro as opposed to coding it. They are only simple steps that require no code work. Good luck!


----------



## Hartke2421

JS411, I'm just using Excel's pivot tables, but I wasn't sure if PowerPivot could do this more easily.  I've been looking for macros like this for a while and can't find anything.  Can you direct me to one?

FShaikh, are you suggesting to record separate macros to change to each selected pivot item in the field?  If that's the case, I'm not sure that's a good option for me because there are 36 different pivot items in this field.


----------



## NickyvV

As I read your question I assume you are using regular PivotTables? Have you tried PowerPivot (Tables) then? 
By importing the data as a Linked Table in PowerPivot you can relate the two tables. By making a relationship between the tables you can link 1 slicer to many PT's. You could even make measures that compare the current data with that from last month.
Please let me know if this was helpful!


----------



## Hartke2421

NickyvV,
The more research I do, the more I think that PowerPivot might be my best option.  Thanks for the suggestion and I think I'll try that in the meantime while I continue to look for ways for this to work with regular Pivot tables in Excel.

Any links on where to do this in PowerPivot?


----------



## NickyvV

Well,
Rob Collie's blog (New Visitor? « PowerPivotPro) would be a good one!
I don't have a specific example at the ready, let me know if this works for you..


----------



## canpa12

FShaikh said:


> Thanks again MD610. I did not know that about SharePoint, however I wont be publishing this for the time being. Helpful info tho!



Dear Fshaik kindly share the macro that you recorded.. i am also having same kind of issue


----------



## canpa12

FShaikh said:


> No worries, this macro does exactly what you require (as I needed the same thing). To be honest, in this case it would be easier simply recording the macro as opposed to coding it. They are only simple steps that require no code work. Good luck!




Dear Fshaik Can you guide me how to use macros to control slicers ( am new to macros recording) am giving my email id..

_E-Mail Address Removed - Moderator_


----------



## Legacy 227120

I am working with PivotTables and Slicers. However if I can first explain the scenario:


I am collating data from a table that is updated every month. I transfer the table into Excel 2010. The column titles on the monthly tables are always the same, it is the data that changes.
For example, I have the xls spreadsheets from August 2012 and September 2012. From these 2 tables I create a PivotTable for both months, structuring them exactly the same.
I then Place both PivotTables side by side on the same page.
I then inserted a Slicer for table A.
I would like to know if I can get the Slicer to control BOTH PivotTables at the same time, bearing in mind that both PT's are drawing their data from different sources.
Please help!


----------



## tscrishinag

Hi Guys,

I was also looking for something very similar thing. Here I have made two pivot tables from different data-sets, but they have some common columns, which are basically business divisions etc.

I want the same slicer (Business Divisions in this case) to display set of information extracted from those two different data-sets. People have mentioned about recording a macro or using PowerPivot. Can someone please guide me further, which one would be better and how to execute that particular method.

Thanks in advance.


----------



## tscrishinag

I was able to solve my problem by hit and trial... if anybody want help on how to do the same, just send across a message of reply on this thread


----------



## faxmonkey

I would love to see your solution. tscrishinag


----------



## schbrownie

I know it's a little bit late in this thread... BUT... start by creating the first pivot table with all of the slicers you want.  Then create the second pivot table (OMIT the slicers for now).  Once you're happy with the second table, <right-click> on a particular slicer than you want to associate with a second pivot table.  Choose "PivotTable Connections" from the menu.  In the pop-up window that appears, choose any and all applicable tables that you want this slicer associated with.

this solves your original request without any macros.


----------



## RoryA

That only works if the two pivot tables use the same data source, which is not the case here.


----------



## abhimehta90

Guys,
Same problem. Using excel '13. Want to connect a slicer to pivots with different source data. Tried to do it with a macro, but dynamic changes with that seems difficult...any 1 who has done???
No idea if there's anything in power pivot...any other option is welcome. Please it's urgent!
thanks


----------



## jafa1970

Old thread but came across it and in case this is of use to the person above with Excel 2013:  10+ steps to using Excel 2013's new data modeling feature - TechRepublic

Shows how to link two data sets using Excel 2013, and then can use Pivot over them.


----------



## Jerry Sullivan

@jafa1970, Nice of you to share that article. 

As Rory pointed out, a slicer can only be shared by PivotTables that share the same Data Source. The term "Data Source" in that context means the PivotCache.

AFAIK, that's still true for PivotTables made using Excel 2013's data modeling capabilities as it was for Excel 2010 with or without the PowerPivot Add-in.

Using those applications, one can join different datasets from multiple sources to create a single PivotCache that feeds multiple PivotTables that can be linked by slicers. The key point of clarification is that those Pivots must still share the same Data Source (the PivotCache).


----------



## Migaspt

tscrishinag said:
			
		

> I was able to solve my problem by hit and trial... if anybody want help on how to do the same, just send across a message of reply on this thread



Hey tscrishinag, why don't you just write your solution here? I'm sure it would help many people!


----------



## deletedalien

I've been looking for a solution to this, has anyone actually succeeded on doing this? can you share your macro code?

Thanx in advance.


----------



## Legacy 227120

I am working with PivotTables and Slicers. However if I can first explain the scenario:


I am collating data from a table that is updated every month. I transfer the table into Excel 2010. The column titles on the monthly tables are always the same, it is the data that changes.
For example, I have the xls spreadsheets from August 2012 and September 2012. From these 2 tables I create a PivotTable for both months, structuring them exactly the same.
I then Place both PivotTables side by side on the same page.
I then inserted a Slicer for table A.
I would like to know if I can get the Slicer to control BOTH PivotTables at the same time, bearing in mind that both PT's are drawing their data from different sources.
Please help!


----------



## exceliz

great thread! -  i also am struggling   solution....


----------



## ratherbegliding

Hi,
As said previously, use Power Query to merge both data sets into one single table.
Then create two independent pivot tables from the merged table.
Only then will you be able to use a single slicer to filter both pivot tables simultaneously.

Good luck!


----------



## ashley8905

tscrishinag said:


> I was able to solve my problem by hit and trial... if anybody want help on how to do the same, just send across a message of reply on this thread




Hi I am stucked on this problem now..Wanted to share one slicer with two pivot tables from different source in the same sheet.
Can you help?
Thanks very much!
Awaiting your response


----------

