Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to use a slicer for multiple pivot tables

Tags 
Video Transcript 

In this video, we'll look at how to configure a slicer to control more than one pivot table.

One of the nice things about slicers, compared to report filters, is that they can control multiple pivot tables or charts.

This makes them very useful for building dashboards that are based on pivot tables.

In this worksheet, we have a simple pivot table that shows total sales by Product.

I'm going to duplicate the pivot table, and set the second table to show sales by City.

Next, I'll add a slicer to filter by Region.

Notice that, at this point, the slicer controls only one pivot table.

In many cases, if you present more than one pivot table, with one slicer, you'll want the slicer to control the pivot tables that are based on the same data.

All slicers have a setting that links them to pivot tables, called "Pivot Table Connections".

You can access this setting using the button on the Slicer Tools tab of the ribbon. Or, you can right-click and select from the context menu.

Once you have the Pivot Table Connections dialog open, you'll see a list of the pivot tables in the workbook. The pivot table currently controlled by the slicer will be checked.

Just check the checkbox for each pivot table you want to control and click OK.

Now you can see that see that grand totals in these two pivot tables match, and the slicer is controlling both.

Remember that you name your pivot tables to be more descriptive

In the case of slicers, this can make it easier to set and confirm Pivot Table Connections.

For example, if I give these pivot tables new names, we'll see those names when we check the connections.

 

Author 
Dave Bruns
I have been on your emailing list and your tips have made sense to me so I thought that would take the next step and really try and up skill myself with excel. - Maara
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course