About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store


Create a new Excel report for every department with one click

Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.

The tip in this show is from Pivot Table Data Crunching.

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.

If you are new to pivot tables, check out some of these past episodes: 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. For the BEST TV show on technology, check out Call for Help.

This tip was originally published on October 3, 2006 and aired on TechTV in Canada and Australia on October 3, 2006.

The permanent URL for this page is

If you are looking for show notes from another episode, visit my complete list of TechTV appearances. Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.