Create a New Excel Report for Every Department with One Click

October 03, 2006

Pivot tables are a powerful feature in Excel. While I've discussed pivot tables in many previous episodes, I recently learned a new trick with pivot tables that will allow you to quickly create a report for every customer or every department, etc.

To start today's trick, create any useful pivot table. Add the customer field as a page field. Here is such a pivot table.

Normally, if you wanted to produce a report for each customer, you would use the Customer dropdown in B1. You can imagine it would be tedious to select each customer and then click print.

Instead, use the Pivot Table dropdown on the PivotTable toolbar. At the bottom of the dropdown, choose Show Pages.

Excel will show you a list of all the page fields. In this case, there is only customer, so choose the page field and click OK.

Excel will quickly replicate the pivot table, adding a new worksheet for each customer in the page field dropdown.

The Show Pages feature for pivot tables is a fantastic way to quickly produce reports for every customer, department, etc.