Using PivotCharts: Graphical Pivot

The new user interface also makes it easier to create PivotCharts. All filter enhancements for PivotTables are also available for PivotCharts. There are special PivotChart tools and shortcut menus you can use to create a PivotChart to analyze the data within a chart.

You can change the layout and the format of charts or the chart elements in the same way you make changes for Pivot Tables. Unlike in previous Excel versions, in Excel 2007 and Excel 2010, the chart format is maintained if you change the PivotChart.

Creating a PivotChart

Creating a chart for a PivotTable takes only seconds. Use the previous PivotTable example to practice. Do the following:

1. Click in the PivotTable, and select the PivotTable Tools contextual tab above the default tab (see Figure 1-60).

   

   Figure 1-60. Working with PivotTable tools.

2. In the Tools group, click the PivotChart button (see Figure 1-61). The Insert Chart dialog box opens. The first layout under Column is selected (see Figure 1-62).

   

   Figure 1-61. Creating a chart from PivotTable data.

   

   Figure 1-62. Selecting the chart format.

3. Keep this setting and click OK. The chart and a PivotChart filter range are displayed (see Figure 1-63).

   

   Figure 1-63. The PivotChart is created.

4. As soon as you change the filter, the chart also changes. In the Country list, select Germany and click OK (see Figure 1-64).

   

   Figure 1-64. Selecting a filter.

   The chart changes automatically, and the corresponding values are displayed (see Figure 1-65).

Figure 1-65. The chart changes depending on the Pivot data.

Changing the Original Data

PivotTables and PivotCharts change dynamically: If a value changes in the original data, the PivotTable and the associated chart also change. Try it out:

1. In the original data, increase the order quantity for gloves in Germany in any row (see Figure 1-66).

   

   Figure 1-66. Changing the original data.

2. Go back to the PivotTable and open the PivotTable Tools contextual tab.

3. On the Analyze tab, in the Data group, click the Refresh button (see Figure 1-67).

   

   Figure 1-67. Applying changes to the original data by clicking Refresh.

   The PivotTable as well as the PivotChart are automatically updated (see Figure 1-68).

Figure 1-68. The values are updated.

Note

You can change additional settings for PivotCharts: Select a chart element and open the shortcut menu (see Figure 1-69).

Figure 1-69. The shortcut menu allows quick access to the settings.