Chapter 35: Analyzing Data with Pivot Tables

FIGURE 35.23

After expanding the pivot table, formulas that use the GETPIVOTDATA function continue to display the correct result.

Creating Pivot Charts

A pivot chart is a graphical representation of a data summary displayed in a pivot table. A pivot chart is always based on a pivot table. Although Excel lets you create a pivot table and a pivot chart at the same time, you can’t create a pivot chart without a pivot table.

If you’re familiar with creating charts in Excel, you’ll have no problem creating and customizing pivot charts. All Excel charting features are available in a pivot chart.

Cross-Reference

I cover charting in Chapters 18 and 19. n

Excel provides two ways to create a pivot chart:

•Select any cell in an existing pivot table and then choose PivotTable Tools Options Tools PivotChart.

•Choose Insert Tables Pivot Table Pivot Chart. Excel creates a pivot table and a pivot chart.

735

Part V: Analyzing Data with Excel

A pivot chart example

Figure 35.24 shows part of a table that tracks daily sales by region. The Date field contains dates for the entire year (excluding weekends), the Region field contains the region name (Eastern, Southern, or Western), and the Sales field contains the sales amount.

On the CD

This workbook, named sales by region pivot chart.xlsx, is available on the companion CD-ROM. n

FIGURE 35.24

This data will be used to create a pivot chart.

The first step is to create a pivot table to summarize the data. Figure 35.25 shows the pivot table. The Date field is in the Row Labels area, and the daily dates have been grouped into months. The Region field is in the Column Labels area. The Sales field is in the Values area.

The pivot table is certainly easier to interpret than the raw data, but the trends would be easier to spot in a chart.

To create a pivot chart, select any cell in the pivot table and choose PivotTable Tools Options Tools PivotChart. Excel displays its Create Chart dialog box, from which you can choose a chart type. For this example, select a standard line chart and then click OK. Excel creates the pivot chart shown in Figure 35.26.

The chart makes it easy to see an upward sales trend for the Western division, a downward trend for the Southern division, and relatively flat sales for the Eastern division.

736

Chapter 35: Analyzing Data with Pivot Tables

A pivot chart includes field buttons that let you filter the chart’s data. To remove the field buttons, right-click a button and choose the Hide command from the shortcut menu.

When you select a pivot chart, the Ribbon displays a new contextual tab: PivotChart Tools. The commands are virtually identical to those for a standard Excel chart, so you can manipulate the pivot chart any way you like.

FIGURE 35.25

This pivot table summarizes sales by region and by month.

FIGURE 35.26

The pivot chart uses the data displayed in the pivot table.

737

Part V: Analyzing Data with Excel

If you modify the underlying pivot table, the chart adjusts automatically to display the new summary data. Figure 35.27 shows the pivot chart after I changed the Date group to quarters.

FIGURE 35.27

If you modify the pivot table, the pivot chart is also changed.

More about pivot charts

Keep in mind these points when using pivot charts:

•A pivot table and a pivot chart are joined in a two-way link. If you make structural or filtering changes to one, the other is also changed.

•The field buttons in a pivot chart contain the same controls as the pivot chart’s field headers. These controls allow you to filter the data that’s displayed in the pivot table (and pivot chart). If you make changes to the chart using these buttons, those changes are also reflected in the pivot table.

•If you have a pivot chart and you delete the underlying pivot table, the pivot chart remains. The chart’s Series formulas contain the original data, stored in arrays.

•By default, pivot charts are embedded in the sheet that contains the pivot table. To move the pivot chart to a different worksheet (or to a Chart sheet), choose PivotChart Tools Tools Design Location Move Chart.

•You can create multiple pivot charts from a pivot table, and you can manipulate and format the charts separately. However, all the charts display the same data.

•Slicers also work with pivot charts. See the example in the earlier previous section, “Filtering Pivot Tables with Slicers.”

•Don’t forget about themes. You can choose Page Layout Themes Themes to change the workbook theme, and your pivot table and pivot chart will both reflect the new theme.

738