Pivot data in a PivotTable or PivotChart report

In Microsoft Excel 2010, you can pivot data in a PivotTable or PivotChart report by changing the field layout of the data. By using the PivotTable Field List, you can add, rearrange, or remove fields to show data in a PivotTable or PivotChart exactly the way that you want.

By default, changes you make in the PivotTable Field List are automatically updated in the report layout. To improve performance when you are accessing a large amount of external data, you can temporarily switch to manual updating.

In this article


Working with the PivotTable Field List

When you create a PivotTable, Excel displays the PivotTable Field List so that you can add fields to the PivotTable, rearrange and reposition them as needed, or remove them from the PivotTable. By default, the PivotTable Field List displays two sections:

  • A field section at the top for adding fields to and removing fields from the PivotTable
  • A layout section at the bottom for rearranging and repositioning fields

PivotTable Field list

You can dock the PivotTable Field List to either side of the Excel window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally.

If you don't see the PivotTable Field List, make sure that you click anywhere in the PivotTable.

If you close the PivotTable Field List, you can display it again. Right-click the PivotTable, and then click Show Field List. You can also click Field List on the Ribbon (PivotTable Tools, Options tab, Show group for a PivotTable; PivotChart Tools, Analyze tab, Show/Hide group for a PivotChart).

If you don't see the fields that you want to use in the PivotTable Field List, refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) the PivotTable or PivotChart report to display any new fields, calculated fields (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.), measures, calculated measures, or dimensions that you have added since the last operation (PivotTable Tools, Options tab, Data group).

How the PivotTable Field List works

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 create the field layout of a PivotTable or PivotChart report.

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 in the PivotTable Field List

To create a field layout, use the following guidelines for moving Value, Name, and Dimension fields from the field section to the four report areas in the layout section.

You can move only hierarchies, attributes, and named sets to the Row Labels, Column Labels, and Report Filters areas.

You can move only measures, calculated measures, and Key Performance Indicators (KPIs) to the Values area.

Changing the PivotTable Field List view

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

  1. To change the view, click the Field List views button at the top of the PivotTable Field List.

PivotTable Field List View button

  1. In the list, select one of the following views:
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 Section 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 Stacked and Fields Section and Areas Section Side-By-Side views, you can adjust the width and height of each section by resting the pointer on 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 clicking the double arrow or pressing ENTER.

Top of Page Top of Page

Add fields to a PivotTable or PivotChart

After you create a PivotTable or PivotChart report, you are ready to add the fields that contain the data you want to display in the report. You typically select one field for each area in the layout section. However, to see different values for a specific field, you can also add multiple copies of a field to the Values area.

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.

Add the fields you want to display

In the PivotTable Field List, you can add fields to the PivotTable or PivotChart by doing one or more of the following:

  • To place a field in a designated area of the layout section, in the Choose fields to add to report box, select the check box of the field that you want. You can then rearrange fields later if you want.

Note    By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) date and time hierarchies are added to the Column Labels area.

  • To place a field in a specific area of the layout section, in the Choose fields to add to report box, right-click the field name, and then click Add to Report Filter, Add to Column Label, Add to Row Label, or Add to Values.
  • To drag a field to an area in the layout section, in the Choose fields to add to report box, click and hold a field, and then drag it from the field section to the layout section that you want.

Add multiple copies of a field in the Values area

In a PivotTable report that is based on data in an Excel worksheet or external data from a non-Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) 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. In the PivotTable Field List, in the Choose fields to add to report box, click and hold a field, and then drag it to the Values area in the layout section.
  2. Repeat step 1 to create as many copies of that field that you want to display in the Value area.
  3. In the PivotTable, change the summary function or custom calculation the way you want in each copy of the field.

Tip    In the PivotTable, you can also change the name of the copied fields as needed.

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 in the Values area. You can even move the Values Column Label to the Column Labels area or Row Labels areas. However, you cannot 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.

Filter data before you add fields

  • In the PivotTable Field List, in the Choose fields to add to report box, rest the pointer on a field name, and then click the filter drop-down arrow next to the field name.

On the Filter menu, select the filter options that you want.

Top of Page Top of Page

Rearrange fields in the PivotTable or PivotChart

In the PivotTable Field List, you can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:

PivotTable Description
Values Use to display summary numeric data.
Row Labels Use to display summary numeric data.
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.
Report Filter Use to filter the entire report based on the selected item in the report filter.
PivotChart Description

Values

Use to display summary numeric data.
Axis Field (Categories) Use to display fields as an axis in the chart.
Legend Fields (Series) 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.

To rearrange fields in a PivotTable report, 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 from the PivotTable or PivotChart

To remove a field, in the PivotTable Field List, do one of the following:

  • In the Choose fields to add to report box, clear the check box of the field you want to remove.

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

  • In a layout area, click the field that you want to remove, and then click Remove Field.
  • In a layout area, click and hold the field that you want to remove, and then drag it outside the PivotTable Field List.

Top of Page Top of Page

Switch between automatic and manual updating of the report layout

By default, changes you make in the PivotTable Field List are automatically updated in the report layout. To improve performance when you are accessing a large amount of external data, you can temporarily switch to manual updating mode. Manual updating mode allows you to quickly add, move, and remove fields in the PivotTable Field List. However, you cannot use the report until you switch back to automatic updating mode.

  1. To enable manual updating of the PivotTable report, at the bottom of the PivotTable Field List, select the Defer layout update check box.

Caution    After 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.

  1. In the PivotTable Field List, make the field layout changes, and then click Update to manually update the layout in the PivotTable report.
  2. To return to automatic updating after you finish changing the report layout in the Field List, clear the Defer layout update check box.

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

Top of Page Top of Page

 
 
Applies to:
Excel 2010