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]

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.