Pivot data in a PivotTable or PivotChart report

After you create a PivotTable or PivotChart report, use the PivotTable Field List to add fields. If you want to change a PivotTable or PivotChart report, use the Field List to rearrange and remove fields. By default, the PivotTable Field List displays two sections: a field section at the top for adding and removing fields, and a layout section at the bottom for rearranging and repositioning fields. You can dock the PivotTable Field List to either side of the window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally.

 Notes 

  • If you don't see the PivotTable Field List, make sure that you click the PivotTable or PivotChart report. If you still don't see the PivotTable Field List, for a PivotTable report, on the Options tab, in the Show/Hide group, click Field List, and for a PivotChart report, on the Analyze tab, in the Data group, click Field List.
  • If you don't see the fields in the Field List that you want to use, refresh the PivotTable or PivotChart report to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation.
What do you want to do?


Learn about the PivotTable Field List

It's important to understand how the PivotTable field List works and the ways that you can arrange different types of fields so that you can achieve the results that you want when you lay out a PivotTable or PivotChart report.

How the PivotTable Field List works


How the PivotTable Field List works

Callout 1 An external data source contains structured data organized as one or more fields (also called columns) that are displayed in the Field List.
Callout 2 Move a field to the Report Filter area in the Field List, which simultaneously moves the field to the Report Filter area in the PivotTable report.
Callout 3 Move a field to the Column Label area in the Field List, which simultaneously moves the field to the Column Label area in the PivotTable report.
Callout 4 Move a field to the Row Label area in the Field List, which simultaneously moves the field to the Row Label area in the PivotTable report.
Callout 5 Move a field to the Values area in the Field List, which simultaneously moves the field to the Values area in the PivotTable report.

Guidelines for moving fields to the four report areas

The following are guidelines for moving Value, Name, and Dimension fields from the field section to the the four areas in the layout section.

Value fields    

  • If you just select a check box for a numeric field, the default area it is moved to is the Values area.

Row and Column fields    

  • If a field is already in a Row Labels, Column Labels, or Report Filters area, and it is added to one of those areas again, it changes position when it is moved to the same area, but changes orientation when it is moved to a different area.

Measures    

  • In an Online Analytical Processing (OLAP) data source, there are often many fields (or measures) organized in a hierarchy of different dimensions, hierarchies, and levels. Click the expand Plus box and collapse Minus box buttons until you find the fields that you want.
  • You can only move hierarchies, attributes, and named sets to the Row Labels, Column Labels, and Report Filters areas.
  • You can only move measures, calculated measures, and Key Performance Indicators (KPIs) to the Values area.

Top of Page Top of Page

Add fields

  • To add fields to the report, do one or more of the following:
  • Select the check box next to each field name in the field section. The field is placed in a default area of the layout section, but you can rearrange the fields if you want.

By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, and OLAP date and time hierarchies are added to the Column Labels area.

  • Right-click the field name and then select the appropriate command, Add to Report Filter, Add to Column Label, Add to Row Label, and Add to Values, to place the field in a specific area of the layout section.

 Tip   You can also click and hold a field name, and then drag the field between the field section and an area in the layout section.

Top of Page Top of Page

Copy fields

In a PivotTable report that is based on data in an Excel worksheet or external data from a non-OLAP data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.

  1. Click and hold a field name in the field section, and then drag the field to the Values area in the layout section.
  2. Repeat step 1 as many times as you want to copy the field.
  3. In each copied field, change the summary function or custom calculation the way you want.

 Notes 

  • When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area. You can use this field to move the field positions up and down within the Values area. You can even move the Values Column label to the Column labels area or Row labels areas. However, you can’t move the Values Column label to the Report Filters area.
  • You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the Row Labels and the Column labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
  • Another way to add the same field to the Values area is by using a formula (also called a calculated column) that uses that same field in the formula.
  • You cannot add the same field more than once in a PivotTable report based on an OLAP data source.

Top of Page Top of Page

Rearrange fields

You can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:

PivotTable report Description PivotChart Description
Values     Use to display summary numeric data. Values     Use to display summary numeric data.
Row Labels     Use to display fields as rows on the side of the report. A row lower in position is nested within another row immediately above it. Axis Field (Categories)     Use to display fields as an axis in the chart.
Column Labels     Use to display fields as columns at the top of the report. A column lower in position is nested within another column immediately above it. Legend Fields (Series) Labels     Use to display fields in the legend of the chart.
Report Filter     Use to filter the entire report based on the selected item in the report filter. Report Filter     Use to filter the entire report based on the selected item in the report filter.
  • To rearrange fields, click the field name in one of the areas, and then select one of the following commands:
Move Up     Moves the field up one position in the area.
Move Down     Moves the field down position in the area.
Move to Beginning     Moves the field to the beginning of the area.
Move to End      Moves the field to the end of the area.
Move to Report Filter     Moves the field to the Report Filter area.
Move to Row Labels     Moves the field to the Row Labels area.
Move to Column Labels     Moves the field to the Column Labels area.
Move to Values     Moves the field to the Values area.
Value Field Settings, Field Settings     Displays the Field Settings or Value Field Settings dialog boxes. For more information about each setting, click the Help button Button image at the top of the dialog box.

 Tip   You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas.

Top of Page Top of Page

Remove fields

  • To remove a field, click the field name in one of the layout areas, and then click Remove Field, or clear the check box next to each field name in the field section.

 Tip   You can also click and hold a field name in the layout section, and then drag it outside the PivotTable Field List.

 Note   Clearing a check box in the field section removes all instances of the field from the report.

Top of Page Top of Page

Change the PivotTable Field List view

The PivotTable Field List has five different views that are designed and optimized for different types of PivotTable tasks.

  • To change the view, click the View button at the top of the PivotTable Field List and select one of the following:
Fields Section and Areas Section Stacked     This is the default view, and it is designed for a small number of fields.
Fields Section and Areas Section Side-By-Side     This view is designed for adding and removing fields when you have more than four fields in each area.
Fields Only     This view is designed for just adding and removing many fields.
Areas Section Only (2 by 2)     This view is designed for just rearranging many fields.
Areas Section Only (1 by 4)      This view is designed for just rearranging many fields.

 Tip   In the Fields Section and Areas Section Stackedand Fields Section and Areas Section Side-By-Side views, you can adjust the width and height of each section by hovering over the section divider until the pointer changes to a vertical double arrow Vertical double arrow or horizontal double arrow Horizontal double arrow, by dragging the double arrow up or down or left or right to where you want it, and then either by clicking the double arrow or by pressing ENTER.

Top of Page Top of Page

Filter data before adding it to the report

If the PivotTable report is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report.

  • Hover over the field name in the field section, and then click the filter drop-down arrow next to the field name.

The Filter menu is displayed. For more information, see Filter data in a PivotTable report or PivotChart report.

Top of Page Top of Page

Switch between automatic or manual updating of the report layout

By default, each time that you make a change in the PivotTable Field List, the report layout is automatically updated. To improve performance when you are accessing a large amount of external data, you can switch to manual updating. When you switch to manual updating, you cannot use the report until you switch back to automatic updating. However, you can quickly add, move, and remove fields from the field section to the layout section, and then switch back to automatic updating to see your results.

If you set the report layout to manual updating, closing the PivotTable Field List, changing to Fields only view, or exiting Excel discards all layout changes that you have made to the PivotTable report without confirmation.

  • To enable or disable manual updating of the PivotTable report, select or clear the Defer layout update check box at the bottom of the PivotTable Field List.
  • When you finish changing the report layout in the Field List, click Update to see the layout in the PivotTable report.

 Note   A PivotTable report starts with automatic updating each time that you open the workbook.

Top of Page Top of Page

 
 
Applies to:
Excel 2007