Analyzing Microsoft Excel 2010 Data with PivotTable Reports
- 8/31/2010
- Introducing PivotTables
- Creating a PivotTable
- Rearranging PivotTable Fields
- Refreshing a PivotTable
- Changing the Numeric Format of PivotTable Data
- Choosing Report Layout Options
- Formatting a PivotTable
- Displaying Totals and Subtotals
- Sorting PivotTable Fields
- Filtering PivotTable Fields
- Changing PivotTable Calculations
- Grouping and Ungrouping Data
- Displaying the Details Behind a Data Value
- Creating PivotCharts
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 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 23-27.) Excel creates a PivotTable at the same time it creates a PivotChart—and hence you see a blank table layout.
)
Figure 23-27 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 23-28 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 used the Report Filter box to restrict the presentation to a single category (Children), and we filtered the Channel field to show international and mail order sales only. We also tidied up a bit by closing the PivotTable Field List window and dragging the PivotChart Filter Pane to a less obtrusive position.
As you can see, when you select a PivotChart, Excel adds a new set of tabs to the ribbon, under PivotChart Tools. With these tabs, you can manipulate and format your PivotChart the same way you do an ordinary chart. (For details about working with charts, see Chapter 19, “Basic Charting Techniques,” and Chapter 21, “Advanced Charting Techniques.”) While the chart is selected, changes in the four boxes below the field list reflect the fact that you’re working with a chart instead of a table: the Row Labels box becomes the Axis Fields box, and the Column Labels box becomes the Legend Fields box. You can pivot the chart the same way you would pivot a PivotTable—by dragging field names from one box to another. You can also change the filter applied to the chart by manipulating the drop-down lists that appear alongside the chart axes and above the legend.
)
Figure 23-28 We used a PivotChart to plot two distribution channels for one book category.
A PivotChart and its associated PivotTable are inextricably linked. Changes to one are immediately reflected in the other.
In Figures 23-27 and 23-28, 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.