Problem: I need to print a report for each of my customers. Using the Report Filter field is tedious: I spend my whole morning selecting a customer, clicking Print, selecting a customer, clicking Print, and so on.
Strategy: The feature you use to solve this problem-the Show Report Filter Pages command-is the most powerful feature of pivot tables. I don't know why Microsoft buries it so deeply in the menu system. You can use the Show Report Filter Pages command to make a report for every customer. Follow these steps:
- Build a pivot table with the information you want to replicate for each customer.
- Add the Customer field as one of the Report Filter fields.
- Select PivotTable Tools Options, Options dropdown, Show Report Filter Pages. Gotcha: Don't click on the big Options icon. Click on the tiny dropdown next to the Options icon.
- Set up the report and select Show Report Filter Pages.
- A dialog box will appear, asking you to show all Report Filter Pages and giving you a list of all the fields in the Report Filter. Even though this seems silly when you have only one field in the Report Filter area, choose Customer and click OK.
Results: In a matter of seconds, Excel will add a new worksheet for each customer. Each worksheet will be named after the customer, and the Customer dropdown will be changed to the particular customer. In a matter of seconds, you will have one worksheet for each customer.
- One report per customer.
Gotcha: Get the pivot table perfect before making hundreds of copies. Even though I thought I did a lot of formatting, I forgot to replace blanks with zeroes in the above figure. At this point, I would delete the customer worksheets, change the original pivot table, then use Show Pages again.
You can imagine that this feature could be useful if you need one report per department, one report per product, etc.