Creating Your First Pivot Chart

With all the complexity behind the make up of a pivot chart, you may get the impression that it is difficult to create one. The reality is that it's quite a simple task.

To demonstrate how simple it is to create a pivot chart, look at the pivot table shown in Figure 7.1.

Figure 7.1. This basic pivot table shows revenue by region and allows for filtering by line of business.

This pivot table provides for a simple view of revenue by region. The Line of Business field in the page area lets you parse out revenue by line of business.

The data in this pivot table would be easier to absorb if it were represented in a pie chart. Creating a pivot chart would allow you to chart this data without losing the ability to filter out line of business.

To start this process, simply right-click anywhere in the pivot table and select PivotChart.

You have just created your first pivot chart. However, as you can see in Figure 7.2, the resulting pivot chart is not a pie chart. You will have to carry out some additional formatting to get the effect you need.

Figure 7.2. Although you have created your first pivot chart, you will now have to format your chart in order to get the effect you are looking for.

To transform this chart into a pie chart, simply right-click anywhere inside the chart and select Chart Type to display the dialog box shown in Figure 7.3.

Figure 7.3. Right-clicking on the chart and selecting Chart Type will allow you to change the type of chart your pivot data is shown through.

NOTE

Out of the box, Excel's default chart type is a stacked column chart. This is why when you create a chart automatically by pressing F11 or by creating a pivot chart, your chart is generated in a stacked column. You can change this setting to any chart type you would like.

In the example in Figure 7.3, if you wanted to set a pie chart as your default chart type, simply click the Set as Default Chart button you see on the lower-left corner of the dialog box. After your new default is set, all subsequent pivot charts you create will be created as a pie chart.

As shown in Figure 7.4, you now have an interactive pie chart that allows you not only to see revenue by region, but to filter on line of business as well.

Figure 7.4. Your final pie chart allows you to filter on line of business and to refresh your data, just as you would in your pivot table.

Pivot Chart Embedded on a Worksheet

You will notice that, by default, Excel created your pivot chart on its own chart sheet. For a more interesting effect where your pivot chart is on the same worksheet as your pivot table, follow these steps:

1.	Place your cursor in a cell outside your pivot table.
2.	Launch Excel's Chart Wizard.
3.	In step 1 of the wizard, select the Pie Chart option.
4.	In step 2 of the wizard, click on your pivot table and then click Finish.
5.	Inside your newly created chart, you will see field buttons. Right-click on any of the field buttons and select Hide PivotChart Field Buttons.

Your reward is a pie chart on the same sheet as your pivot table. As shown in Figure 7.5, this new chart responds to filtering and other actions performed through your pivot table.

Figure 7.5. By following the steps, you have created a chart adjacent to the pivot table that will respond to changes in the pivot table.

As you wrap up the walkthrough in the preceding sidebar, don't lose sight of the real power behind pivot charts. Pivot charts use the same pivot cache and pivot layout as their corresponding pivot table.

This means that if you add a new line of business to the pivot table shown earlier in Figure 7.1, your newly created pie chart will automatically capture it. If you add or remove data from your data source and refresh your pivot table, your pie chart will show the same data that your pivot table shows. This, in effect, eliminates the need to manage two sets of dataone that feeds your pivot table and one that feeds your pivot chart. As you can imagine, this fundamental functionality can save you headaches galore when trying to transform your data into visual reports.