Use one slicer for two pivot tables | MyExcelOnline

I had a scenario wherein I wanted to use one slicer for two pivot tables as that would look so cool! You can select items in the slicer and the data will get filtered in all the Pivot Tables.

Let me show you this trick so that it will only take a couple of steps to set this up!

  • Create the first Pivot Table.
  • Copy-paste the first Pivot Table to create the second Pivot Table.
  • Insert a slicer
  • Link the slicer to both the tables.

Don’t forget to download the Exercise Workbook below and follow along with us!

DOWNLOAD EXCEL WORKBOOK


Here is our current Pivot Table setup:

Use one slicer for two pivot tables | MyExcelOnline

 

STEP 1: Click anywhere on the data table.

Use one slicer for two pivot tables | MyExcelOnline

STEP 2: Go to Insert > PivotTable.

Use one slicer for two pivot tables | MyExcelOnline

STEP 3: Place the Pivot Table in a New or Existing Worksheet and click OK.

Use one slicer for two pivot tables | MyExcelOnline

STEP 4: Drag and drop the following:

  • Financial Year and Sales Region in Rows field
  • Sum of Sales in Values field

Use one slicer for two pivot tables | MyExcelOnline

STEP 5: Select any cell inside the Pivot Table and go to PivotTable Analyze > Select > Entire PivotTable

Use one slicer for two pivot tables | MyExcelOnline

STEP 6: Right-click and select Copy

Use one slicer for two pivot tables | MyExcelOnline

STEP 7: Pick an empty space and select Home > Clipboard > Paste

Use one slicer for two pivot tables | MyExcelOnline

STEP 8: Now set up the second Pivot Table by placing inside Rows: SALES PERSON and SALES QTR

Use one slicer for two pivot tables | MyExcelOnline

STEP 9: Click anywhere on the PivotTable, go to PivotTable Analyze > Insert Slicer.

Use one slicer for two pivot tables | MyExcelOnline

STEP 10: Select Sales Month and click OK.

Use one slicer for two pivot tables | MyExcelOnline

STEP 11: Right-click on the slicer and select Report Connections

Use one slicer for two pivot tables | MyExcelOnline

STEP 12: Select both the Pivot Tables and click OK.

Use one slicer for two pivot tables | MyExcelOnline

 

Now try it out! Click on March in your slicer and both pivot tables are updated at the same time!

Use one slicer for two pivot tables | MyExcelOnline

You can choose the Report Connections options to link slicer to multiple pivot tables at once and use a single slicer to operate the pivot tables. This will make your report hassle-free and not messy.

Click here to learn more about Pivot Table Slicers!

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

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