Pivot Tables, Pivot Charts, and Real Charts

Introduction

One of the most powerful features of Microsoft Excel is the Pivot Table. Pivot tables allow rapid, dynamic, flexible data analysis. Pivot charts add to the flexibility of pivot tables, allowing the same rapid analysis of displayed data, while sacrificing substantial flexibility of normal Excel charts. This article describes how to create pivot tables and pivot charts, and how to create normal charts from pivot tables.

Pivot Table Database

The source data range for a pivot table must be arranged in a list, which has each record (observation) in a single row, each field (variable) in a single column, a header row with names of the fields, and no blank rows or columns. The following sample data shows monthly sales figures for Reps 1 and 2 in the North region and Reps 3 and 4 in the South region.

Sample Pivot Table Database

Making a Pivot Table

Note: The precise steps to making a pivot table vary slightly from version to version of Excel. These instructions were written based on Excel 2000, but the actual procedure is substantially the same in all versions.

To make a pivot table, select either the entire source data range, or a single cell within this range, and choose Pivot Table and Pivot Chart Report... from the Data menu. The various parts of the Pivot Table Wizard verify that the data is from an Excel database and ask whether to create a pivot table or pivot chart, then ask whether the pivot table shall be created on a new worksheet or on an existing sheet. I find it easier to create the pivot table alone, and add a pivot chart (or regular chart) later. If I'm making a relatively small pivot table from a small database, I usually place the pivot table onto the same sheet as the database; otherwise I'll put the pivot table onto its own sheet.

The most important part of a pivot table is its layout. The layout can be designed by clicking the Layout button on the last step of the Pivot Table Wizard, or directly within the worksheet. Drag the Pivot Field buttons into the appropriate area (Page, Row, Column, or Data) of the pivot table.

Pivot Table Wizard's Layout Dialog

Pivot Table Wizard's Layout Dialog

A sample pivot table is shown below: the Month and Region fields are in the Row area of the pivot table, the Rep field is in the column area, and the Amount field is in the data area. There are no page fields identified in this example. The data area of the pivot table allows several options for the fields it contains, including Sum, Average, Min, Max, and Count; this example keeps the default Sum of Amount.

Note that when a cell within a pivot table is selected, the different areas of the pivot table are highlighted with a thick blue border.

Sample Pivot Table

The default pivot table has row and column grand totals, and intermediate subtotals for the Months. The Pivot Table menu on the Pivot Table toolbar has some useful items: Wizard which reruns the Pivot Table Wizard on the active pivot table, Table Options which includes whole-table settings, such as row and column totals, and Field Settings which includes formatting and subtotal settings for the individual pivot fields. Double clicking on a pivot field button also invokes the Field Settings dialog. I like to simplify my pivot tables by removing subtotals and grand totals, as shown in the following example.

Pivot Table without Subtotals or Grand Totals

The dropdown arrows on the pivot field buttons display a list of pivot items belonging to that field. You can change whether a pivot item is visible using the checkboxes in the list.

Dropdown Showing Pivot Items within a Pivot Field

The layout of an existing pivot table can be changed by rerunning the Pivot Table Wizard, or by dragging the pivot field buttons within the pivot table. The following arrangement was made by dragging the Rep field button to the row area of the pivot table.

Realigned Pivot Table

The best way to become familiar and comfortable with pivot tables is to practice on a few samples, experimenting with the different options.

Making a Pivot Chart

There are a number of ways to create a Pivot Chart from a pivot table.

• Click the Chart Wizard button on the Standard Toolbar
• Click the Chart Wizard button on the Pivot Table Toolbar
• Select the Chart... item on the Insert menu
• Select the PivotChart item on the Pivot Table menu (on the Pivot Table toolbar)

A pivot chart is by default created on its own chart sheet. You can move it to a worksheet using the Location... item on the Chart menu, choosing As object in:, and selecting a sheet from the dropdown list. I generally place the pivot chart onto the same sheet as the pivot table.

The first simplified pivot table is recreated below with its associated pivot chart.

Simplified Pivot Table and Associated Pivot Chart 1

Notice that the pivot chart has the same pivot field buttons as the pivot table. These field buttons can be dragged or double clicked with the same functionality as those in the pivot table. The dropdown arrows allow selection of specific pivot items as in the pivot table. When the items in the pivot chart are changed, the associated pivot table changes the same way. Realigning the pivot fields of the pivot chart changes the pivot table and pivot chart as shown below (a few formatting changes have also been made).

Realigned Pivot Table and Associated Pivot Chart 2

Notice a few "features" of pivot charts:

• There is a lot of white space around the chart, but moving and resizing of the plot area are disabled.
• Moving and resizing of the legend are disabled, except for positioning in the five default legend positions.
• Certain chart types—scatter, bubble, and stock charts—are not allowed in pivot charts.
• All data (except subtotals and totals) in the pivot table must appear in the pivot chart.
• No data from outside the pivot table may be added to the pivot chart.
• The pivot chart always plots series by column; plotting by row is not possible.
• When a pivot table and chart are rearranged or refreshed, the default series formatting is reapplied, wiping out any custom formatting applied by the user.

Making a Regular Chart using Pivot Table Data

It is possible to make a regular chart from pivot table data, even though Excel makes it difficult. A regular chart will not properly update the way a pivot chart does if the shape of a pivot table changes when it is updated, but a regular chart overcomes all of the drawbacks of pivot charts listed above. In most cases I prefer to use a regular chart, redrawing it or redefining its data ranges when the pivot table changes. When creating charts with VBA, the inconvenience of rebuilding a regular chart is minuscule.

To create a regular chart from a pivot table, select a blank cell that doesn't touch the pivot table, and start the chart wizard. In step 1 of the wizard, choose a chart type. In step 2 of the wizard, click on the Series tab, and add each series and its data ranges individually. If you select a range using the Data Range tab, your chart will revert to a pivot chart. Complete the chart wizard as usual.

An alternative technique is to start with a regular chart based on other data, select Source Data from the Chart menu, and click on the Series tab to change the source data to ranges within the pivot table.

A third alternative is to copy the pivot table, select a cell in a blank range, and use Paste Special from the Edit menu, and use the Values option. Delete the contents of the cells corresponding to the pivot field buttons. Now select the table and use the chart wizard to create your chart.

Pivot charts 1 and 2 from earlier in this article are reproduced below next to their regular chart equivalents. The regular charts are able to portray the information more clearly in a smaller space. Some of the greater space within a pivot chart is required to contain the pivot field buttons, but some is due to the excessively wide, unchangeable margins around the chart elements.

Pivot Chart 1		Regular Chart 1 from Pivot Table Data
Pivot Chart 2		Regular Chart 2 from Pivot Table Data

Jon Peltier is president of Peltier Technical Services, Inc., which provides custom Excel solutions to help clients in engineering, finance, marketing, and other fields analyze, visualize, and understand information more quickly and more thoroughly.

Peltier Technical Services uses the power of Excel's VBA programming environment to integrate Excel's worksheet functionality and charting capabilities with the presentation and reporting abilities of PowerPoint, Word, and other applications. The company web site also houses one of the most extensive collections of information about Excel charting, including tutorials, tips, special techniques, and sample VBA code for generating customized charts and charting features.