Making Regular Charts from Pivot Tables

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 (alleviated to some extent in Excel 2007)
• Pivot chart formatting may be lost when the pivot table is changed

When pivot charts were first introduced in Excel 2000, I was excited by the possibilities, yet discouraged by their limitations. I rarely used pivot charts because I felt the limitations far outweighed their capabilities. The formatting capabilities in Excel 2007 are much less constricting than in Excel 2003, so I am more likely to use pivot charts in 2007. While I strongly dislike working with Excel 2007 charts, Excel 2007 pivot charts are greatly improved. The restrictions on pivot chart data sources are still limiting.

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 in Excel 2003, 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.

In 2007, select a cell in the data range, and choose PivotTable from the Insert ribbon tab. By default, manipulation of the fields is done in a Pivot Table Field List pane, not in the worksheet. You can override this setting if you’re willing to trade the new pivot capabilities introduced in 2007 for the convenient but apparently old-fashioned on-sheet button manipulation. In the PivotTable Field List, check the boxes in front of the fields you want in the pivot table, then drag them to the appropriate areas at the bottom of the pane. It’s basically the same as in 2003, just within the task pane.

The resulting pivot table looks like this:

To make a pivot chart, select any part of the pivot table and insert a chart. Excel 2007 places the pivot chart on the active worksheet.

In Excel 2003 and earlier, by default the pivot table is created on its own chart sheet. This is rather inconvenient, since I always move the pivot chart to the same worksheet as the pivot table anyway. To avoid this sheet switching, I select a blank cell that isn’t touching the pivot table, and insert a chart without specifying any data. I then select the blank 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 Excel 2003, 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, or exclude data from the pivot table (though you could format a series to make it invisible).

In Excel 2003, you cannot change the size of the plot area or the position of the legend, chart title, or axis titles (other than deleting them). You can remove the pivot field buttons (Pivot Table toolbar > PivotChart dropdown > Hide PivotChart Field Buttons) to gain a little space, but you still can’t move or resize the chart elements.

Excel 2007 doesn’t have the Field Buttons cluttering up the chart. Instead there is a floating PivotChart Filter Pane that, together with the PivotTable Field List, serves the same function less intrusively. Excel 2007 also allows you to move or resize the individual chart elements. But the other inflexible features of pivot charts remain.

So you decide you need a regular chart to regain your formatting and source data flexibility.

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, go to Insert menu > Chart, and select a chart type and subtype in step 1 of the Chart Wizard, and click Finish. In Excel 2007, simply select a chart type from the Insert tab, then choose the desired subtype.
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, nor can it use a date-scale axis in a line chart.

Related Posts:

• Grouping by Date in a Pivot Table
• Pivot Table Conditional Formatting with VBA
• Explore Your Data With Pivot Tables
• Update Regular Chart when Pivot Table Updates
• Dynamic Chart using Pivot Table and VBA
• Referencing Pivot Table Ranges in VBA