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:
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 http://www.mrexcel.com/tip135.shtml. If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com 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.