Creating PivotCharts

PivotCharts, like PivotTables, summarize tabular information and allow for easy transposition of fields and axes. They’re a great way to study or present elements of your data set.

You can create a PivotChart directly from your source data by selecting a cell in the original data range, clicking the Insert tab, clicking the arrow beneath PivotTable in the Tables group, and then clicking PivotChart. After you specify or confirm your data source and indicate where you want the new PivotChart to reside (in a location either on the existing worksheet or on a new worksheet), Excel presents both a PivotTable layout and a blank chart canvas, along with a PivotChart Filter Pane (see Figure 15–25). Excel creates a PivotTable at the same time it creates a PivotChart-and hence you see a blank table layout. The PivotChart Filter Pane doesn’t really add any capability that isn’t available via the PivotTable Field List window, so you might want to close one or the other to make more room on the worksheet.

Figure 15–25: When you create a new PivotChart, Excel draws a blank chart canvas as well as a blank table layout. The program creates a PivotTable at the same time it creates the PivotChart.

Figure 15–26 shows a simple PivotChart created from this chapter’s Books table. Because charts are generally most effective when applied to a modest amount of data, we’ve used the Report Filter box to restrict the presentation to a single category (Children), and we’ve filtered the Channel field to show international and mail order sales only. We’ve also tidied up a bit by closing the PivotTable Field List window and dragging the PivotChart Filter Pane to a less obtrusive position.

Figure 15–26: We’ve used a PivotChart to plot two distribution channels for one book category.

As you can see, when you select a PivotChart, Excel adds a new set of tabs on the Ribbon, under PivotChart Tools. With these tabs, you can manipulate and format your PivotChart the same way you would an ordinary chart.

A PivotChart and its associated PivotTable are inextricably linked. You can manipulate fields and axes in either, and the other stays in step.

In Figures 15–25 and 15–26, we created a PivotChart directly from the source data. You can also create one from an existing PivotTable. Select any cell in the PivotTable, click the Options tab under PivotTable Tools, and then click PivotChart in the Tools group.