Excel 2010 Bible

Part V: Analyzing Data with Excel

736

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.