Pivot Table Field List Tips

Tips for working with the PivotTable Field List. Move it, change its layout, sort the fields, to save time and work efficiently. Also see: List all Pivot Fields with Details

Author: Debra Dalgleish

Pivot Table Field List

When you create a pivot table, and select a cell in it, by default, a pivot table field list should appear, at the right of the Microsoft Excel window. You can use the field list to select fields for the pivot table layout, and to move pivot table fields to a specific area in the layout.

To see the PivotTable Field List:

• Click any cell in the pivot table layout.
• The PivotTable Field List pane should appear at the right of the Excel window, when a pivot cell is selected.

If the PivotTable Field List task pane does not appear automatically, follow these steps to enable it:

• Click any cell in the pivot table layout.
• On the Excel Ribbon, click the PivotTable Analyze tab
• Then in the Show group (at the far right), click the Field List command.

Note: If the full Show group is not visible, click Show, Field List (from the drop down list of commands)

.

Add or Remove Pivot Fields

The main purpose of the PivotTable Field List is to add or remove pivot fields from the pivot table layout on the worksheet. There are three main sections sections in the PivotTable Field List:

--Fields

--Search Box

--Layout Areas

In the field list shown below, the Region field has a check mark, and the Region field appears in the Rows area.

There are no fields in the Filters area, Columns area, or Values area yet.

Note: Your field list will have different fields checked, or no fields.

Fields

Near the top of the PivotTable Field List pane is a list of the column headings from your Excel table; they appear in the same order as in the Excel table. In the pivot table, these are called fields.

If you used a Recommended PivotTable layout, you will see a check mark beside the fields that are in the pivot table.

Search

In Excel 2016, there is a Search box above the list of fields. This can help you quickly find a field name in a long list. Click in that box and start typing. The field list will be filtered automatically, to only show the field names that contain the string of letters that you typed.

Layout Areas

At the bottom of the PivotTable Field List pane are the four areas of the pivot table: Report Filter, Column Labels, Row Labels, and Values.

You can drag the fields into these areas, and they’ll appear in the matching area of the pivot table layout on the worksheet

If you used a Recommended PivotTable layout, you will see the fields from that layout in those areas.

Add, Move and Remove Pivot Fields

For steps on how to add, move and remove pivot fields, using the PivotTable Field List, go to the How to Set Up an Excel Pivot Table page.

For example, drag fields from one area in the Layout section to a different area. In the screen shot below, one of the Value Fields, Sum of Year, is being moved to the Rows area.

Change the Pivot Table Field List

To see the steps for adjusting the pivot table field list, please watch this short video tutorial. The written instructions are below the video. Download the sample file from this video, to follow along.

Change the Pivot Table Field List Layout

By default, the field list shows a list of the fields at the top, and the four pivot table areas in a square at the bottom. You can change that layout, by using a command on the field list.

To change the layout:

• In the field list, click the arrow for Tools, near the top right
• The default layout is at the top of the list -- Fields Section and Areas Section Stacked
• Select one of the other layouts, such as Fields Section and Areas Section Side-by-Side

Which layout to select:

• The Fields Section and Areas Section Stacked layout is best if the pivot table has long field names, or a short list of fields
• The Fields Section and Areas Section Side-by-Side layout is better when field names are short, or there is a long list of fields.
• I don't use the other layouts -- Fields Section Only, Areas Section Only (2 by 2), Area Section Only (1 by 4)

Adjust the Field List Width

To adjust the width of the field list:

• Point to the border between the field list and the worksheet
• When the pointer changes to a two-headed arrow, drag left or right, to make the field list wider or narrower

Move the Field List

The field list can be locked in place at the right or left of the worksheet, or it can float over the worksheet.

To move the field list:

• Point to the Title area of the field list
• When the pointer changes to a four-headed arrow, drag the field list to a new position

To lock the floating field list into its previous position:

• Point to the Title area of the field list
• When the pointer changes to an arrow, double-click, to lock the field list at the side of the worksheet.
• It will go to the side where it was last locked.

Sort the Field List

The fields in the field list can be sorted alphabetically, or in the same order that the fields are arranged in the source data.

Sort the fields in Excel 2013

To change the sort order for fields listed in the PivotTable Field list, follow these steps:

• Click the Tools arrow, at the top right of the Field List
• In the Sort options, click Sort A to Z or click Sort in Data Source Order

To sort the fields in Excel 2010:

• Right-click a cell in the Pivot Table, and click PivotTable Options
• Click the Display tab
• In the Field List section, click Sort A to Z or click Sort in Data Source Order

Defer Layout Update

If your pivot table is based on a large data source, it might respond very slowly when you add fields or move fields to a different area of the pivot table.

To improve the pivot table performance, if you plan to add or move more than one field, you can use the Defer Layout Update option.

When this feature is enabled, the fields are all added or moved, and then the pivot table is recalculated once. If this box is not checked, the pivot table is recalculated after each field is added or moved.

Turn On Defer Layout Update

To defer the layout updates:

• Add a check mark to the Defer Layout Update box in the PivotTable Field List.

Make Pivot Table Changes

While the Defer Layout Update setting is turned on, make your pivot table changes:

• Move or all all the fields that you want to adjust
• Click the Update button, to the right of the Defer Layout Update check box.
• When you’re finished changing the layout, remove the check mark from the Defer Layout Update check box.

Note: Some features, such as filtering and grouping, are not available when Defer Layout Update is activated.

Get Sample File

Get the sample file that was used in the Pivot Table Field List video. The zipped file is in xlsx format, and does not contain any macros.

More Pivot Table Resources

FAQs - Pivot Tables

Pivot Table Introduction

Excel Pivot Table Setup

List all Pivot Fields with Details

Remove Pivot Fields with Macros

Grouping Data

Summary Functions

Clear Old Items in Pivot Table