Create Multiple Copies of Excel Pivot Table

In an Excel pivot table, you can put fields in the Report Filter area, like the City and YrMth fields in the pivot table shown below.

If you need to print a report for a specific city, you can select that city’s name from the drop down list.

Create a Pivot Table for Each City

If you want to print a copy of the pivot table for each city in the drop down list, it might take a while to do the task manually. Instead, you can use a built-in pivot table feature, to automatically create a separate pivot table for each city. Each pivot table will be on its own worksheet, and will have all the formatting that you applied to the original pivot table.

To create a pivot table for each item in a pivot table’s Report Filter:

1. In the City filter, select "(All)", or select specific cities from the list. If a city is not selected, a pivot table copy will not be created for it.
2. Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click
   the Options tab.
3. At the left end of the Ribbon, click the drop down arrow for Options.
4. Click Show Report Filter Pages
•  
5. In the Show Report Filter Pages dialog box, click on City, to select that field.
• 
6. Click OK, to create the pivot table pages.

A worksheet is created for each city, with the city’s name on the sheet tab. On each worksheet
is a copy of the original pivot table, with the city’s name selected in the Report Type filter.

Print the City Pivot Tables

After you create the City pivot table pages, you can select all the City worksheets, and print them.

Then, while the City sheets are still selected, delete them, so only the original pivot table is left in the workbook.

More Pivot Table Tips

Please visit the Contextures website for more Excel pivot table tips and tutorials.

________________________