PivotTable reports 101

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002

Let's suppose you've compiled a large list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) of data—for example, sales figures for every product your company makes. You're now ready to extract some meaningful information from the data, and find answers to questions like:

  • What are the total sales for each product by region?
  • Which products are selling best over time?
  • Who is your highest-performing salesperson?

To answer these and other questions, you can create a PivotTable® report—an interactive table that automatically extracts, organizes, and summarizes your data. You can use this report to analyze the data, make comparisons, detect patterns and relationships, and discover trends.

For example, you can use the data in the following illustration...

Source data for the PivotTable reports in this article

...to create a PivotTable report that displays how each product is selling in each sales region, as shown in the next illustration.

PivotTable report that shows how products are selling by region

The following sections provide an overview and map out a sequential process for effectively working with PivotTable reports.

ShowOverview of working with PivotTable reports    

A PivotTable report can help you see the "big picture" by summarizing and analyzing your data. You can control how Excel summarizes the data—for example, by sum, average, or count—without entering a single formula. In a PivotTable report, you can quickly:

  • Add or remove data     Not quite enough information in your PivotTable report? Maybe you want to include data such as sales figures broken down by salesperson. In a PivotTable report, you can easily add or remove categories of data.

For example, you can add the Salesperson field (field: A category of information, such as last name or order amount, that is stored in a table. When Query displays a result set in its Data pane, a field is represented as a column.), as shown in the following illustration.

A PivotTable report with a new field, Salesperson, added to it

  • Rearrange the layout     Not satisfied with the layout of your PivotTable report? For example, you might prefer to display the salesperson information in rows instead of in columns. The interactive nature of your PivotTable report lets you literally turn the table: You can easily move (or pivot) the rows and columns to view different summaries of the data.

For example, you can move the Salesperson field so it appears down the rows instead of across the columns.

A PivotTable report with the Salesperson field moved so it appears down the rows instead of across the columns

For example, you can add a page field for the Year category. That way, you can view sales data for an individual year (such as 2003), or for all years combined.

A PivotTable report with a page field, Year, added

If your PivotTable report contains many fields, you can also use page fields to keep your report compact and readable. Or, if you're retrieving data from some types of large external databases, you can add and then set up page fields so that they limit the amount of data retrieved. That way, you can avoid long waits and memory issues.

  • Show the details you want     Want to zero in on specific details in a PivotTable report? You can display or hide items in a row or column field—for example, specific products or salespeople. You can also display or hide details about items in row or column fields.

For example, in the Type field, you can hide all product items except beverages.

A PivotTable report with a Type field that has all product items hidden except Beverages

You're probably eager to get started on your first PivotTable report. But before you begin, it's a good idea to make sure your data is well organized and ready to go, as described in the following section.

ShowStep 1: Prepare the source data    

Read this section to learn what kinds of data you can use in a PivotTable report and how to prepare it. After you prepare the data, you can use the PivotTable and PivotChart® Wizard to create a PivotTable report.

In the first part of the wizard, you choose the source data. You can choose data already entered in Excel or data from a database, called an external data source. If you choose an external source, the wizard starts a separate program called Microsoft Query, which you then use to connect to the source and select the data you want to display.

Click any of the following source data types to learn how they can be used:

ShowExcel list or database

You can use a series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command. For easy preparation of Excel data, you should consider doing any or all of the following:

 Note   Don't filter the data in place or use the AutoFilter command. This hides some of the data in the list, but the PivotTable report will include all the data.

ShowExternal data source

You can use a database file, text file, or a source on the Internet.

To prepare external data, you may want to do the following:

  • Install the necessary tools and drivers     You're probably all ready to go—just make sure you install the appropriate open database connectivity (ODBC) drivers or data source drivers you need. Microsoft Query, including its Help and drivers, is automatically installed on first use. Then, when you create a PivotTable report with the PivotTable and PivotChart Wizard, you'll use Microsoft Query to actually retrieve the external data.
  • Retrieve the data before you start the wizard, if needed     If the following list contains your data retrieval method, you won't be able to use Query from within the PivotTable and PivotChart Wizard to retrieve the data. Instead, before you start the wizard to select the worksheet range that contains the external data, follow these instructions to insert the data in an Excel workbook:
    • Office Data Connections     Office Data Connection, or .odc, files are the recommended method for retrieving external data for your reports. You can use them whenever you're retrieving data from a single database table or from an OLAP database. This means that you don't need to combine data from more than one table in the external database or filter the data to select specific records before you create the report.
    • Query files and report templates     To use a query file (.dqy, .oqy, or .rqy) to retrieve the data, open the query file in Excel. To use a report template (.xlt) that doesn't already include a PivotTable report, open the template.
    • Parameter queries     To use a parameter query to retrieve the data, you must first create the parameter query in Microsoft Query so that it returns the data to Excel. Note that you can't create a parameter query to retrieve source data from OLAP databases. For information about creating parameter queries, see Help in Microsoft Query.
    • Web queries     To use a Web query to retrieve data over the Internet, you must first create a Web query and retrieve the data into Excel. To create a Web query, point to Import External Data on the Data menu, and then click New Web Query.

ShowMultiple consolidation ranges

You can combine and summarize data from several different Excel lists.

To prepare data from multiple Excel lists, you may want to do the following:

ShowAnother PivotTable report

If you plan to use the same data to create multiple PivotTable reports, you can save memory and disk space by reusing an existing PivotTable report to create a new PivotTable report. This action links the original and the new PivotTable reports. (If you refresh the data in the original PivotTable report, the data in the new report is also refreshed, and vice versa.)

To prepare data from another PivotTable report, you may want to do the following:

  • Make sure both PivotTable reports are in the same workbook     If the original PivotTable report is in a different workbook, copy the original report to the workbook where you want the new report to appear.
  • Check the page field settings     In the original PivotTable report, you may have changed the page field settings so they retrieve external data for each page individually. In this case, you need to reset the page fields so they retrieve external data for all items at once. To check the settings, double-click each page field, and then click Advanced.

 Note   To learn more about page fields, see "Anatomy of a PivotTable report," later in this article.

ShowStep 2: Create a PivotTable report    

To create a PivotTable report, you first use the PivotTable and PivotChart Wizard. The wizard helps you specify the data you want to use and creates the report framework. Then you can use the PivotTable toolbar to arrange your data within that framework.

For example, you can choose the categories of data to include (shaded in blue) and the corresponding data values to summarize (shaded in yellow).

Source data that contains the categories of data to include, and the data values to summarize, in a PivotTable report

Then, tell Excel how to arrange the data categories in the rows and columns of the PivotTable report. When Excel creates the finished report, it automatically summarizes and totals the data values.

A finished PivotTable report, with categories of data arranged in rows and columns, and summarized data values

To create a PivotTable report, follow these steps:

ShowStart the PivotTable and PivotChart Wizard

  1. Open the workbook where you want to create the PivotTable report. If you're basing the report on an Excel list or database, click a cell in the list or database.
  2. On the Data menu, click PivotTable and PivotChart Report.
  3. In step 1 of the PivotTable and PivotChart Wizard, choose the type of data you will use, and then click PivotTable under What kind of report do you want to create?
  4. In step 2 of the wizard, follow the instructions for locating your data. If you're retrieving external data for the PivotTable report, you can get more information about using Microsoft Query or the Query Wizard from either Excel Help or Query Help.
  5. In step 3 of the wizard, click Finish.

ShowDefine the layout of the PivotTable report

  1. From the PivotTable Field List window, drag field buttons to the labeled areas on the PivotTable diagram.

To see a demonstration of how to drag the field buttons, click the Start button in the following demo.

Play Demo

  1. To rearrange the fields, drag them from one area to another. To remove a field, drag it outside the PivotTable report.

 Note   In some cases, you may not want to define the layout of the PivotTable report manually by dragging fields directly on a worksheet. For example, if your source data is from a large external database, it may be too time-consuming to define the layout manually. In this case, you can use the wizard to define the layout. Click Layout in step 3 of the PivotTable and PivotChart Wizard to lay out the PivotTable report.

Anatomy of a PivotTable report

You may have noticed that Microsoft Excel uses specific terms to identify the parts of a PivotTable report. If you're not familiar with these terms, read this section for a quick primer.

A PivotTable report that contains all the PivotTable elements

1  Page field     A field from the source data that you assign to a page (or filter) orientation in a PivotTable report. For example, Year is a page field. You can use the Year field to display summarized data for only 2003, only 2004, and so on.

2  Data field     A field from the source data that contains values to be summarized. For example, Sum of Sales is a data field.

For most types of source data you can choose how to summarize data (for example, by sum, average, or count). A data field usually summarizes numbers, but it can also summarize text. For example, you can count the number of times a specific text entry (such as Yes or No) appears in a field.

3  Column field     A field from the source data that you assign to a column orientation in a PivotTable report. For example, Type is a column field.

4  Item     A subcategory of a row, column, or page field. For example, the Type field contains the following items: Beverages, Dairy, and Meat. The Salesperson field contains these items: Buchanan, Davolio, Dodsworth, and Suyama.

5  Row field     A field from the source data that you assign to a row orientation in a PivotTable report. For example, Region and Salesperson are row fields.

6  Data area     The cells in a PivotTable report that contain summarized data. For example, the value in cell C5 summarizes Buchanan's beverage sales for the East region in 2003. In other words, it's a summary of the sales figures for every row in the source data that contains the items Buchanan, Beverage, East, and 2003.

ShowStep 3: Customize a PivotTable report    

You've now created a PivotTable report, but that's only the beginning. Remember all the questions you had about your data? A PivotTable report is more than just a static summary; you can use it to interactively explore the data's significance. Zero in on the details or pull back for an overview. View sales by region or over time. You decide the type of customizations you want. To customize your PivotTable report, do one or more of the following:

ShowAdd or remove data

Do you want to view more or fewer categories of data in your PivotTable report? You can easily add or remove a field—including a row, column, or page field.

To add a field, drag a field from the PivotTable Field List window to the appropriate area on the PivotTable report. To remove a field, drag a field outside the PivotTable report.

To see a demonstration, click Play Demo, and then click the Start button.

Play Demo

 Note   You can't directly edit the data in a PivotTable report. Instead, you need to edit the source data and then refresh the PivotTable report.

ShowQuickly rearrange the layout

Do you want to reorganize the PivotTable report to get a different perspective on the data? You can quickly "pivot" the report by moving a field or reordering the items within a field.

To move a field, drag the field to the appropriate area on the PivotTable report. To reorder items within a field, drag an item to a new location in the field.

To see a demonstration, click Play Demo, and then click the Start button.

Play Demo

ShowUse page fields to view a subset of the data

Did you include a page field in your PivotTable report? If so, you now have a three-dimensional report that consists of multiple "pages." Each page contains a subset of the data that's summarized in the report.

To view a different page, click the arrow next to the page field item, and then click an item in the list.

Selecting items in a PivotTable page field

ShowShow the details you want

In a PivotTable report, you can drill down on the details or hide information for a big picture view. You can quickly:

  • Show or hide items in a row or column     Click the arrow in a field, and then select or clear the check boxes you want.

For example, in the Type field, you can hide all product items except Beverages.

A PivotTable report with a Type field that has hidden all product items except Beverages

  • Show or hide specific details for an item     Click an item in a field, and then click the Show Detail or Hide Detail button on the PivotTable toolbar. If prompted, click the field with the detail you want to show.

For example, for the Beverages item in the Type field, you can include more detail by displaying the sales per year.

A PivotTable report that contains Year detail for the Beverages item

  • Display the underlying source data for a specific data cell     Just double-click a cell to see its details.

For example, double-click the subtotal for Beverages in the East region to see the source data records used to calculate the cell.

A PivotTable report and the underlying detail records for one of its cells

ShowStep 4: Print a PivotTable report    

Do you plan to print your PivotTable report? If it's a multipage report, you can set print options to control the report's printed appearance, as follows:

  • To make it easier to follow the PivotTable report from one page to the next, you can automatically reprint labels at the top of each page.

For example, on the printed page, you can repeat the labels from rows 1 and 2 and also repeat the outer row field item Apr.

Printed version of a PivotTable report

  • To ensure that a logical chunk of the PivotTable report is printed on each page, you can specify where you want Excel to insert page breaks.

You might choose to include a page break at a specific location, such as before each new Month field to avoid separating the items for each month. For example, in the following illustration, an automatic page break is inserted between rows 41 and 42, separating items for the month of April. To keep the items for April together, you can specify that the page break be placed directly below the Mar Total in row 38.

PivotTable report with automatic page break

Before you print, follow these steps to specify the print area for the report:

  1. Click inside the PivotTable.
  2. On the PivotTable toolbar, click PivotTable, point to Select, and then click Entire Table.
  3. On the File menu, point to Print Area, and then click Set Print Area.

To set print options for your PivotTable report, use any or all of the following procedures:

ShowRepeat row labels on each printed page

  1. On the File menu, click Page Setup, and then click the Sheet tab. Make sure the Rows to repeat at top and Columns to repeat at left boxes are blank, and then click OK.
  2. Click inside the PivotTable.
  3. On the PivotTable toolbar, click PivotTable, and then click Table Options.
  4. Under Format options, select the Set print titles check box.

ShowRepeat the outer row field items on each printed page

  1. Click inside the PivotTable.
  2. On the PivotTable toolbar, click PivotTable, and then click Table Options.
  3. Under Format options, select the Repeat item labels on each printed page check box.

ShowPrint sections of a PivotTable report on separate pages

  1. Double-click the heading on the outer row field that has the items you want to print on separate pages. For example, to insert an automatic page break between each type of product, double-click the heading for the Type row.
  2. In the PivotTable Field dialog box, click Layout.

 Note   If there's only one item in the outer row field, Layout will be unavailable.

  1. Under Print Option, select the Insert page break after each item check box, and then click OK twice.
  2. On the View menu, click Page Break Preview, and make any adjustments you want to the automatic page breaks. You can move a page break by dragging it or delete a page break by pressing DELETE.

 Tip   To make your printed PivotTable report easier to scan, click the Format Report button on the PivotTable toolbar to quickly switch to an indented format. In this format, the data for each row field is indented (similar to a text outline), and all the summary figures for a data field are displayed in a single column.

After you set print options, you can click Print Preview on the File menu to preview the printed report. When you're ready to print the report, click Print.