Section 21.6. Pivot Charts

21.6. Pivot Charts

Excel lets you create charts based on the data in a pivot table. These charts work more or less the same as ordinary Excel charts (Chapter 17). However, pivot charts are typically very dense, so they warrant a couple of extra considerations:

• Because pivot charts are so dense, many of the specialized chart types don't work well. Instead, stick with simple chart types like column charts and pie charts. One popular choice is the stacked column chart, which helps you see the breakdown of your various groups.

• Before you create a pivot chart, it's often useful to limit the amount of information in your pivot table. Too much information can lead to a chart that's hard to read. Avoid using too many levels of grouping, and use filtering (as described in the previous section) to cut down on the total amount of information that's shown in the pivot table.

Figure 21-24 shows a sample pivot chart.

21.6.1. Creating a Pivot Chart

To create a pivot chart, follow these steps:

1. Choose PivotTable Tools Options Tools PivotChart .

   The Insert Chart dialog box appears.

2. Choose the type of chart you want, and then click OK to generate it .

   Your pivot chart appears on the worksheet.

Figure 21-24. In this example, a stacked column chart shows the breakdown of orders by product category and country. Each bar represents a different category and is subdivided into colorcoded sections that represent a country (as detailed in the legend). A pivot chart doesn't necessarily show all the data, because you can use filtering to show just a subset of the total information.

---

Tip: When you create a new pivot chart, Excel places it in a floating box. If you want to move it to a separate worksheet, select it, and then choose PivotChart Tools Design Location Move Chart. When the Move Chart dialog box appears, choose "New sheet," pick a sheet, and then click OK.

---

21.6.2. Manipulating a Pivot Chart

When you select a pivot chart, you'll notice several changes:

• Excel adds several new tabs to the ribbon under a PivotChart Tools heading. These tabs are very similar to the charting tabs you learned about in Chapter 18. They let you change the formatting and layout of the chart, and configure chart elements like titles, axes, and gridlines.

• Excel changes the names of the sections in the PivotTable Field List to help you understand how the different parts of your pivot table are used to create the chart. The Row Labels section becomes Axis Fields. Excel uses these fields to create the different chart categories. The Column Labels section becomes Legend Fields, and Excel uses them to create different series for the chart.

• Excel shows a floating box named the PivotChart Filter Pane. You can use this box to apply filter options directly from your chart.

Like pivot tables, pivot charts are interactive . That means you can rearrange your pivot chart on the fly, simply by dragging the fields in the PivotTable Field List from one section to another. As you make your changes, Excel regenerates the pivot table and your pivot chart.

You can also use the PivotChart Filter pane to change your filter settings (or apply new ones). Just click the drop down arrow to pop up the familiar list of filter options (Figure 21-25). It makes no difference whether you adjust filter settings on the chart using the PivotChart Filter pane or on the original pivot table in the worksheet using the PivotTable Field List pane.

Figure 21-25. You can use the PivotChart Filter Pane to adjust filter settings.

Overall, pivot charts are a specialized tool. You're likely to use pivot tables much more frequently than pivot charts. However, if you need a way to quickly visualize the numbers and get a feel for the most important groups in a summary, a pivot chart can help you out.