Suppose you have created an Excel PivotTable that summarizes sales data by quarter, by product line, and by item. Further, suppose that you have added a Report Filter to the PivotTable that allows you to filter by Region. Now, in anticipation of a staff meeting with all of the Regional managers, you would like to create a separate PivotTable for each Region so you will not find yourself constantly changing the Report Filter. Is there an easy way to quickly create many PivotTables from one? Of course – it goes by the funny name of Show Report Filter Pages.
Beginning with the section of the PivotTable shown in Figure 1, we see a PivotTable summarizing data as described above with four items in the Report Filter – Southeast, Midwest, West, and Northeast. Our desire is to end up with five PivotTables – the current PivotTable, plus a separate PivotTable for each of the four regions.
To achieve our objective, all we need to do is click the drop-down arrow next to Options on the PivotTable Tools, Options contextual tab and then click Show Report Filter Pages…, as shown in Figure 2.
Upon choosing Show Report Filter Pages…, the Show Report Filter Pages dialog box appears, as shown in Figure 3.
Simply click OK in the Show Report Filter Pages dialog box and Excel automatically creates four new PivotTables – one for each Region – on four separate new worksheets, as shown in Figure 4. Each of the newly created PivotTables is an exact replica of the original with the only difference being the application of the appropriate Report Filter for each Region. Further, each of the PivotTables uses the same PivotCache as its data source, meaning refreshing one of the PivotTables refreshes all of the PivotTables.
Using Excel’s Show Report Filter Pages function is yet another example of the extreme power built-in to PivotTables. Whether you are advanced PivotTable user or just now learning how to work with them, don’t miss this great feature.
For a video demonstration of this tip, please visit www.tinyurl.com/k2tips79.