Creating a Pivot Chart

Data tables aren't the only things that Excel knows how to pivot. In addition to creating a pivot table that summarizes information in a data list, you can also have the program create a pivot chart as well. When you elect to create a pivot chart along with a pivot table by selecting the PivotChart Report (with PivotTable Report) option button in the Step 1 of 3 PivotTable and PivotChart Wizard dialog box, Excel always places the pivot chart on a new chart sheet. This happens regardless of whether you choose to place the associated pivot table on a new worksheet or somewhere on the worksheet that's current when you open the PivotTable and PivotChart Wizard.

Note that when you choose to generate a pivot chart with a pivot table, you actually generate the pivot table by building the chart on its chart sheet. Building a pivot chart is very similar to building a new pivot table: You assign fields from the data source (an Excel data list in this example) shown in the PivotTable Field List task pane.

As with the pivot table, you can assign fields to the pivot chart either by dragging them to the designated areas in the chart (Drop Page Fields Here, Drop Data Items Here, Drop Series Fields Here, or Drop Category Fields Here) or by selecting the field name in the Task Pane Field List, and then selecting the name of the chart area to which to assign the field in the drop-down list and, finally, clicking the Add To button.

Although Excel always chooses the Stacked Column chart as the basic chart type for each new pivot chart that you generate, you can select another chart type for the pivot chart. To do so, simply click the Chart Wizard button on the PivotTable toolbar or on the Standard toolbar to open the Chart Wizard - Step 1 of 4 dialog box.

Here, you can select a new chart type from among the types displayed on the Standard Types or the Custom Types tab. When selecting a new chart type on the Standard Types tab, be sure that you select the Press and Hold to View Sample button so that you can see exactly how your pivot chart appears in the selected type. (You may be surprised to see how Excel has to "pivot" the chart's fields to accommodate the chart type you selected.) You can also use the Chart Wizard and Chart pull-down menu to enhance and further format your pivot chart.