One of the nice things about slicers, compared to report filters, is that they can control multiple pivot tables or charts. This makes slicers 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 pivot 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 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 the grand totals in these two pivot tables match, and the slicer is controlling both. Remember that you can 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
