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.
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
Quick, clean, and to the point.