PivotTable reports 101, part 2

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.

 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, 2004, and so on.

 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.

 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.

 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 the following items: Buchanan, Davolio, Dodsworth, and Suyama.

 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.

 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.

Step 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:

• Add or remove data.
• Quickly rearrange the layout.
• View a subset of the data with page fields.
• Show just the details you want.

Add 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.

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.

Quickly 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.

Play Demo

Use 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.

Show just the details you want

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

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.

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.

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.

Step 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:

• 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.

• To ensure that a logical chunk of the PivotTable report is printed on each page, you can insert automatic page breaks.

For example, a page break might fall between items in the Month field.

The following procedures describe how to set print options for your PivotTable report. After you set the 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.

Repeat row labels on each printed page

1. Make sure the PivotTable report is the only report in the print area. (To do this, click the PivotTable report, click PivotTable on the PivotTable toolbar, point to Select, and then click Entire Table. On the File menu, point to Print Area, and then click Set Print Area.)
2. On the File menu, click Page Setup, click the Sheet tab, and then make sure the Rows to repeat at top and Columns to repeat at left boxes are blank. Click OK.
3. Click the PivotTable report.
4. On the PivotTable toolbar, click PivotTable, and then click Table Options.
5. Under Format options, select the Set print titles check box.

Repeat outer row field items on each printed page

1. Click the PivotTable report.
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.

Print sections of a PivotTable report on separate pages

1. Double-click the outer row field that has the items you want to print on separate pages. For example, if you want to automatically insert a page break between each type of product, double-click the Type row field.
2. Click Layout.
3. Under Print Option, select the Insert page break after each item check box, and then click OK twice.
4. 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 the DELETE key.

 Tip   Want 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 which the data for each row field is indented (similar to a text outline). Also, all the summary figures for a data field are displayed in a single column.