Sometimes it’s desirable to make a regular chart from a pivot table, but Excel makes it difficult. If your active cell is in a pivot table, inserting a chart automatically inserts a pivot chart. Defining a source data range that intersects a pivot table automatically converts the chart into a pivot table. And once a chart becomes a pivot chart, it cannot be converted back into a regular chart.

In a moment I’ll show how to make a regular chart from a pivot table’s data. But first let’s review some of the strengths and weaknesses of pivot charts.

Pivot charts have some advantages over regular charts:

• Pivot charts are dynamic
• Pivot charts are interactive
• Pivot charts can be pivoted

However, the same infrastructure that conveys these advantages also results in some shortcomings:

• Some chart types (XY) are not available to pivot charts
• The date scale axis is not available in pivot charts
• All data from the pivot table must be included in the pivot chart
• Data from outside the pivot table cannot be included in the pivot chart
• Pivot chart formatting options are limited
• Pivot chart formatting may be lost when the pivot table is changed

Microsoft hosts a number of decent Pivot Table examples. I will use one of them for this tutorial. Go to the Microsoft example 25 easy PivotTable reports, which has a link to Excel 2002 Sample: PivotTable Reports. From this page you can download a self-extracting executable named Reports.exe, which contains four workbooks. The top few records of the Source Data worksheet of the SampleSalespersonReports.xls workbook looks like this:

To create a pivot table, select one cell within this data range, and choose Pivot Table and Pivot Chart Report from the Data menu. To keep things uncomplicated, place the pivot table onto a new worksheet. Drag the Order Date field to the rows area, the Country field to the Columns area, and the Order Amount field to the Data area. Group the Order Date field by month and year as described in Grouping by Date in a Pivot Table. The resulting pivot table looks like this:

To make a pivot chart, select any part of the pivot table and insert a chart. In Excel 2003 and earlier, by default the pivot table is created on its own chart sheet. I always move the pivot chart to the same worksheet as the pivot table, so I select a blank cell that isn’t touching the pivot table, and insert a chart without specifying any data. I then select the chart, invoke the Source Data command, and click in the pivot table, which instantly turns the chart into a pivot chart associated permanently with this pivot table.

The pivot chart is linked to the pivot table, so any changes to the pivot table are reflected in the chart. In fact, the same dragging around of field buttons to pivot the table can also be accomplished in the chart, and the pivot table keeps up. A regular chart is not dynamic in this way. You could write VBA procedures that detect a pivot table change and adjust the chart’s series data accordingly, but that’s probably beyond the scope of any blog post.

However, a pivot chart doesn’t let you add data from outside the pivot table. You cannot change the size of the plot area or the position of the legend, chart title, or axis titles (other than deleting them). So you decide you want a regular chart.

Making a regular chart is actually easier than you think, as long as you are careful about setting the chart series data. You cannot set the overall source data range for the chart. You have to start with a blank chart, and add your data one series at a time.

Here is the protocol for creating a regular chart similar to the pivot chart above, using the pivot table’s data.

1. Select a blank cell which is not touching the pivot table.
2. Insert a chart. In Excel 2003, select a chart type in step 1 of the Chart Wizard, and click Finish. In Excel 2007, simply select a chart type.
3. Right click the chart, choose Source Data or Select Data. In Excel 2003, click on the Series tab.
4. Click the Add button to add a new series.
5. Click in the Series Name box, and then select the cell with the series name (e.g., the country label UK).
6. Click in the Values, Y Values, or Series Values box, then select the range containing the Y values (the Order Amount data under the UK label(.
7. In Excel 2003, click in the Category (X) Axis Labels or X Values box, then select the range containing the category labels; In Excel 2007, click Edit under Horizontal (Category) Axis Labels, then select the range containing these labels. In both cases, this is the two-column range between the Years and Order Date field buttons at the top and the Grand Total label at the bottom.
8. Repeat steps 4 through 7 as required for all series, including data inside and outside the pivot table.

If I have other data for a country not included in the pivot table, I can simply add it to a regular chart per the protocol above. A pivot chart allows no outside data.

If I want an XY chart, I can create a regular XY chart using the protocol above. A pivot chart cannot use the XY chart type, not can it use a date-scale axis in a line chart.