Multiple Excel Pivot Tables

Pivot Table Slicers were first introduced in Excel 2010 for Windows and Excel 2016 for Mac.

They are a visual filter in the form of an interactive button.  There are several cool things that you can do with Slicers, like customize them, filter them and connect them to multiple Pivot Tables.

Here you can view our Ultimate Guide To Excel Pivot Table Slicers.

The coolest thing that you can do is to connect Slicers to multiple Excel Pivot Tables.  I explain how you can easily do this below…(scroll further down to watch our YouTube instructional video!)


 

Normally when you insert an Excel Slicer it is only connected to the Pivot Table that you are inserting it from.

What about if you had multiple Pivot Tables from the same data set and wanted to connect a Slicer to all of the Pivot Tables, so when you press a button all the Pivot Tables change?

Well this is possible with the Report Connections (Excel 2013 & 2016) / PivotTable Connections (Excel 2010) option within the Slicer.  This is how it is achieved:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Create 2 Pivot Tables by clicking in your data set and selecting Insert > Pivot Table > New Worksheet/Existing Worksheet

Setup Pivot Table #1:

Multiple Excel Pivot Tables

 

ROWS: Region

VALUES: Sum of Sales

Multiple Excel Pivot Tables

 

Setup Pivot Table #2:

Multiple Excel Pivot Tables

 

ROWS: Customer

VALUES: Sum of Sales

Multiple Excel Pivot Tables

 

STEP 2: Click in Pivot Table #1 and insert a MONTH Slicer by going to PivotTable Tools > Analyze/Options > Insert Slicer > Month > OK

Multiple Excel Pivot Tables

Multiple Excel Pivot Tables

 

STEP 3: Click in Pivot Table #2 and insert a YEAR Slicer by going to PivotTable Tools > Analyze/Options > Insert Slicer > Year > OK

Multiple Excel Pivot Tables

Multiple Excel Pivot Tables

 

 

STEP 4: Right Click on Slicer #1 and go to Report Connections(Excel 2013 & 2016)/PivotTable Connections (Excel 2010) > “check” the PivotTable2 box and press OK

Multiple Excel Pivot Tables

Multiple Excel Pivot Tables

 

STEP 5: Right Click on Slicer #2 and go to Report Connections(Excel 2013)/PivotTable Connections (Excel 2010) > “check” the PivotTable1 box and press OK

Multiple Excel Pivot Tables

Multiple Excel Pivot Tables

 

Now as you select each Slicer’s items, both Pivot Tables will change!

Have a look at the following image and the tutorial below that to see how this is achieved using Excel 2013:

Multiple Excel Pivot Tables

Multiple Excel Pivot Tables

VIEW THIS EXCEL PIVOT TABLE SLICER TUTORIAL ON OUR YOUTUBE CHANNEL: 

 

VIEW THE FREE PIVOT TABLE WEBINAR NOW >>

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Show Pivot Table Values As a Running Total In If you want to track your sales on a monthly basis and see their Year to Date totals, then you can easily do this with the Running Totals In calculation.You can activate this by Right Clicking on your Pivot Table values and choosing Show Values As and Running Total In.  You c...
Pivot Charts Pivot Charts in Excel are an extension of a Pivot Table and they show its values in a graphical representation.  So when you filter a Pivot Table, the Pivot Chart updates accordingly.To insert a Pivot Chart you need to click in your Pivot Table, go to the PivotTable Tools tab...
Pivot Table Conditional Formatting Inserting a Pivot Table is very easy to do in Microsoft Excel: Inserting a Pivot TableAdding some Conditional Formats to the Pivot Table allows a user to highlight key data in a split second.See how easy it is to add some color to your analysis.DOWNLOAD WORKBOOK...
50 Things You Can Do With Excel Pivot Tables Pivot Tables in Excel are one of the most powerful features within Microsoft Excel.   A Pivot Table allows you to analyze more than 1 million rows of data with just a few mouse clicks, show the results in an easy to read table, "pivot"/change the report layout with the ease of dr...