Last month's article How to create an effective, user-friendly slicer in Excel offered an introduction to Excel's slicer feature. This graphic tool lets users, with no specialized skill, filter data in a meaningful way. This month, we'll continue our discussion of slicers with a more advanced topic: using a single slicer to update two or more PivotTables. Doing so is helpful when you want to focus on data in the same data source in different ways.
I'm using cheap office 2016 on a Windows 64-bit system, but the feature is available in Excel 2010 and 2013. In Excel 2010, slicers work only with PivotTables. Beginning with Excel 2013, you can add a slicer to a Table. They even work in a browser using Excel Web App. For your convenience, you can download the .xlsx demonstration file. (This file also contains the example data and slicer from last month's article.)
We'll be working with the data shown in Figure A to create two PivotTables and then link the same slicer to both. The first PivotTable will sum the Value field by region; the second will sum the Commission field by personnel. The second PivotTable won't even display the region, but the slicer will still filter it by region.
We'll begin by generating the first PivotTable. If you're using Excel 2016, Excel does almost everything for you. To generate the first one, do the following:
1.Click anywhere inside the Table (the demonstration file's sheet name is Multiple PivotTable Table).
2.Click the Insert tab and then choose Recommended PivotTables in the Tables group.
3.Select the first option (Figure B), Sum of Value by Region, and click OK. Excel creates the selected PivotTable (Figure C) on a new sheet—I named the new sheet Multiple PivotTables, but doing so isn't necessary.