Analyze data with PivotTable reports

By Craig Stinson and Mark Dodge

A PivotTable report in Microsoft Office Excel 2003 is a special kind of table that helps you analyze information from selected fields of a data source. When you create a PivotTable report, you specify which fields from your data source that you're interested in, how you want the table organized, and what kinds of calculations you want the table to perform.

After you have built the PivotTable report, you can rearrange it to view your data from alternative perspectives. This ability to pivot the dimensions of your table — for example, to transpose column headings to row positions — gives the PivotTable tool its name and its unusual analytical power.

A simple example

The following partial Excel worksheet shows a data list of sales figures for a small publishing firm. The list is organized by year, quarter, catalog number, distribution channel, units sold, and sales receipts. The firm uses three distribution channels — domestic, international, and mail order.

Excel data list

It's difficult to see the bottom line for the publishing firm in a flat list like this. But with just a few keystrokes, you can turn this data list into a PivotTable report that provides a useful snapshot of your data.

PivotTable report summarizes information from Excel data list

In this PivotTable report example, the Year and Quarter fields have been positioned along the table's column axis, and the CatalogNo and Channel fields have taken up positions along the row axis. The body of the table displays the total sales numbers for each column-and-row intersection. Cell H6, for example, shows that a book with a catalog number of 23524 had total domestic sales of –$488 in the first quarter of the year — meaning that returns outnumbered sales for that title, in that time period, and in that distribution channel.

Excel displays a Field List dialog box along with the PivotTable report. The six fields of the original Excel data list appear in this dialog box. Rearranging the PivotTable report is as simple as dragging field headings from this window into new positions. For example, in the PivotTable report body, the Channel field was transposed from the row axis to the column axis and both the Year and Quarter fields were moved to the page axis. You can move these two fields to the page axis to focus on a particular section, or slice, of your data for a particular year and quarter.

A PivotTable report focusing a a particular slice of data

Creating a PivotTable report

In the example that follows, a PivotTable report is created from the original Excel data list shown in the previous section. Begin by selecting any cell in the list from which you want to create your table. On the toolbar, click Data, and then click PivotTable and PivotChart Report. This action displays the PivotTable and PivotChart Wizard, which prompts you to follow these steps:

Step 1: Specify the type of data source

On the first page of the PivotTable and PivotChart Wizard, specify the type of data source on which the table will be based and whether you want to create a PivotTable report or a PivotChart report.

  1. Under Where is the data that you want to analyze? select Microsoft Excel list or database.
  2. Under What kind of report do you want to create? select PivotTable. Click Next.

First page of PivotTable and PowerChart Wizard

Step 2: Indicate the location of your source data

On the second page of the wizard, indicate the location of your source data. If you're basing your PivotTable report on an Excel data list and you selected a cell in that list before invoking the wizard, the wizard already has the location of your data but merely prompts you to confirm the location. If your data source is an Excel data list that isn't currently open, click Browse to find it.

 Note   Your Excel data list must include a unique field name at the top of each column.

Second page of PivotTable and PivotChart Wizard asking for location of data source

Step 3: Indicate where you want to put your PivotTable report

On the third page of the wizard, indicate where you want your PivotTable report to appear. To place the report on a new worksheet (always a safe choice), select New Worksheet. Otherwise, select Existing Worksheet and supply a range reference or name in the text box. Click Finish to exit the wizard.

Third page of PivotTable and PowerChart Wizard asking where to locate PivotTable report

Laying out the PivotTable report

When you have completed following the instructions in the PivotTable and PivotChart Wizard, Excel displays a blank table similar to the one that follows. The only remaining task is to drag field headings from the Field List dialog box to the appropriate places on the PivotTable report layout.

Blank PivotTable report layout

For this example, from the Field List dialog box, the Channel and CatalogNo fields was moved to the row axis, the Quarter and Year headings to the column axis, and the Sales heading to the data area. You can put as many fields as you like in any of the areas of the layout. To remove a field, drag its heading off the layout.

 Note   If you find the Field List dialog box distracting, you can hide it by clicking its Close button. With the field list hidden, you can rearrange your PivotTable report by dragging field headings from their row, column, and page positions. To redisplay the Field List dialog box, on the PivotTable report toolbar, click Show Field List.

Pivoting a PivotTable report

To pivot, or rearrange, a PivotTable report, drag one or more field headings. For example, to move a field from the column axis to the row axis, all you have to do is drag its heading from the column area to the row area. In addition to transforming rows, you can change the order in which fields are displayed on the column or row axis. In this example, the Channel heading was dragged to the left of the Catalog No heading to produce the following PivotTable report.

Rearranged PivotTable report in which Channel heading is added to the left of CatalogNo heading

About the authors     Craig Stinson is a contributing editor for PC Magazine. Mark Dodge is a former senior technical writer for the Microsoft Office User Assistance group. Stinson and Dodge are the coauthors of Microsoft Office Excel 2003 Inside Out (Microsoft Press, 2004), from which this article is adapted.

More information

 
 
Applies to:
Excel 2003