Creating a Chart for Every Customer in Excel 2007:-


The Show Report Filters Pages functionality allows you to copy a pivot table for every value in a Report Filter field. This would be a fantastic bit of functionality to have available for pivot charts. However, when you use this feature, Excel copies the pivot table but not the pivot chart.

You have a couple of choices in solving this problem. First, you could set up a simple looping macro in VBA to print the chart for each value in the report filter. Or, you could follow these basic steps:

1. Set up a pivot chart that has the proper formatting. Make sure the Customer field is in the Report Filter area.

2. Select the pivot chart. Choose Design, Save as Template. Define that template as the default chart type.

3. Select a cell in your pivot table. The PivotTable Tools ribbon tabs appears.

4. Look for the Options button on the far left of the Options tab. Don’t click the Options button. Instead, click the drop-down at the right side of this button.

5. Select Show Report Filter Pages. Look into the Figure (given below).



(You can choose Show Report Filter Pages.)

6. In the Show Report Filter Pages dialog that appears, choose Customer and then click OK. Excel copies the current worksheet once for each customer.

7. Select the worksheet for the first customer. Select a cell in the pivot table. Press Alt+F1. Excel creates a pivot chart on the worksheet.

Repeat step 7 for each additional customer. Every time you press Alt+F1, you can wonder why the fine folks at Microsoft could not have allowed pivot charts to work with the Show Report Filter Pages feature.