MrExcel.com - Your One Stop for Excel Tips & Solutions

Create a Report for Every Customer

January 06, 2023 - by

Create a Report for Every Customer

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:

  • 1. Build a pivot table with the information you want to replicate for each customer.

  • 2. Add the Customer field as one of the Report Filter fields.

  • 3. Select PivotTable Tools Analyze, 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.

Don't click the Options button in the Ribbon. Instead, use the tiny drop-down arrow to the right of the button. The second choice is Show Report Filter Pages.
Figure 921. Set up the report and select Show Report Filter Pages.

  • 4. 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.

27 new sheets are inserted in the workbook, one for each customer. The customer name is on the worksheet tab and is selected from the filter in B1.
Figure 922. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Giorgio Trovato on Unsplash