Home > Pivot > Layout > Field List
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
Add or Remove Pivot Fields
Change the Pivot Table Field List
Adjust the Field List Width
Move the Field List
Sort the Field List
Defer Layout Update
Pivot Chart Fields Pane
Get the Sample File
More Tutorials
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.
Show PivotTable Field List
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)
. 
Close PivotTable Field List
Sometimes, you might want to hide the PivotTable Field List, to discourage people from making changes to the pivot table layout.
Or, on some worksheet, you might need every inch of space visible, and the PivotTable Field List takes up too much space.
- To close the PivotTable Field List, click the X at the top right of the pane.
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 spreadsheet. 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 and later versions, 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 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 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 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 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, when working with large data sets:
- Add a check mark to the Defer Layout Update box in the PivotTable Fields Pane.
Make Pivot Table Changes
While the Defer Layout Update setting is turned on, use the following steps to make your pivot table changes:
- In the PivotTable Fields pane, move, remove, or add pivot fields, by dragging them to the field area boxes, or by clicking the field name check boxes
- Note: The pivot fields will not change automatically on the worksheet layout, while the setting is turned off
- At any point, you can click the Update button, to the right of the Defer Layout Update check box, to manually refresh the pivot table.
- When you’re finished changing the layout, click the Update button, to apply all of the layout changes on the worksheet, and to refresh the pivot table
- 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.
Pivot Chart Fields Pane
When a Pivot Chart is selected on the worksheet, a PivotChart Fields pane is shown, instead of a PivotTable Fields pane.
It is almost identical to the PivotTable Fields pane, but two of the boxes in the field areas have different headings:
- Legend (Series) instead of Columns
- Axis (Categories) instead of Rows
You can add, remove and move the chart fields in this PivotChart Fields Pane, exactly the same way as for the pivot table fields in the PivotTable Fields Pane.
TIP: But remember, any layout changes to the pivot chart will also be made to its related pivot table's layout.
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
List all Pivot Fields with Details
Clear Old Items in Pivot Table
FAQs
What is included in a pivot table fields list? ›
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. If you used a Recommended PivotTable layout, you will see the fields from that layout in those areas.
How do I add items to a field list in a pivot table? ›Select a cell in the Pivot table and click on “Options/Analyze” tab. Click on “Calculations/Formlas” in the “Tools” section and select “Calculated Field”. A small window will appear. Write name of the field and click “Add”.
How do I select all fields in a pivot table field list? ›On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.
How do I right click the pivot table field list? ›Right-click any cell in the pivot table and select Show Field List from the menu. This will make the field list visible again and restore it's normal behavior. The field list will disappear when a cell outside the pivot table is selected, and it will reappear again when a cell inside the pivot table is selected.
What do the pivot table fields mean? ›Fields represent the columns in your data – range or Excel table, and will have check boxes. The selected fields are displayed in the report. Areas represent the layout of the report and the calculations included in the report.
What are the four areas in PivotTable fields? ›- Filters.
- Columns.
- Rows.
- Values.
Select a field in the Values area for which you want to change the summary function of the PivotTable report. On the Options tab, in the Active Field group, click Active Field, and then click Field Settings. The Value Field Settings dialog box is displayed. The Source Name is the name of the field in the data source.
What does field list give access to? ›You can use the Field List in Access to easily add data fields to a data entry form. Most forms are connected to an underlying table or query from which they display and/or update the table data. In form design view, you can access the list of fields available to the form within the Field List.
How do I customize PivotTable fields? ›- Select the check box next to each field name in the field section. ...
- Right-click the field name and then select the appropriate command — Add to Report Filter, Add to Column Label, Add to Row Label, or Add to Values — to place the field in a specific area of the layout section.
Choosing multiple items from a filter
At the bottom of the Filters drop-down menu is a check box labeled Select Multiple Items. If you select it, Excel adds a check box next to each item in the drop-down menu. This enables you to select multiple items from the list.
How do I see all details in a PivotTable? ›
Click anywhere in the PivotTable. On the PivotTable tab, click Show Details. Right-click a field in the values area of the PivotTable, and then click Show Details.
How do I change the values fields in a PivotTable for multiple columns? ›If you have multiple fields in the Values area, double-click the heading for any value field. Choose any number in the Values area and click the Field Settings button in the PivotTable Analyze tab of the ribbon. In the PivotTable Fields list, open the drop-down menu for any item in the Values area.
What is the shortcut key for PivotTable field list? ›To quickly insert a new pivot table field, use the shortcut keys ALT + A + I. This will open the Insert Field dialog box, where you can choose which field to add to the pivot table. To quickly remove a pivot table field, use the shortcut keys ALT + A + R.
How do I use field buttons in pivot chart? ›With the pivot chart selected: On the Excel Ribbon, click the Analyze tab. Click the Field Buttons command, to hide/show the PivotChart Field buttons. OR, click the Field Buttons arrow, and select one of the display options.
Why is my PivotTable not showing all fields? ›This can happen for a few different reasons: The data source for the pivot table is empty or contains no rows. This could be because there is a problem with the connection to the data source, or because the data has been deleted or is not yet available.
What is field vs item in PivotTable? ›The key difference between calculated fields and calculated items is that: Calculated Fields are formulas that can refer to other fields in the pivot table. Calculated Items are formulas that can refer to other items within a specific pivot field.
Where is field source in PivotTable? ›On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab). In the Data group, click the top section of the Change Data Source command. The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box.
What is the basic data structure for a PivotTable? ›A pivot table usually consists of row, column, and data fields. In this example, the rows are organized by Month Name and City. The columns are organized by Campaign, and the data are the Resulting Sales and Profit figures for each campaign.
How do you structure a PivotTable? ›- Enter your data into a range of rows and columns.
- Sort your data by a specific attribute.
- Highlight your cells to create your pivot table.
- Drag and drop a field into the "Row Labels" area.
- Drag and drop a field into the "Values" area.
- Fine-tune your calculations.
A: Correct answer is Text box. Text box can be used to add to insert values.
How do you show field list? ›
The Field List should appear when you click anywhere in the PivotTable. If you click inside the PivotTable but don't see the Field List, open it by clicking anywhere in the PivotTable. Then, show the PivotTable Tools on the ribbon and click Analyze> Field List.
What is the difference between field and record? ›A record: Contains specific data, like information about a particular employee or a product. A field: Contains data about one aspect of the table subject, such as first name or e-mail address. A field value: Each record has a field value.
Can I add a custom field to a PivotTable? ›Add a calculated field
Click the PivotTable. This displays the PivotTable Tools, adding the Analyze and Design tabs. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field. In the Name box, type a name for the field.
- Click the field or item that you want to rename.
- Go to PivotTable Tools > Analyze, and in the Active Field group, click the Active Field text box. If you're using Excel 2007-2010, go to PivotTable Tools > Options.
- Type a new name.
- Press ENTER.
Group data
In the PivotTable, right-click a value and select Group. In the Grouping box, select Starting at and Ending at checkboxes, and edit the values if needed. Under By, select a time period. For numerical fields, enter a number that specifies the interval for each group.
- In the PivotTable Field List, in the PivotTable Fields box, click and hold a field, and then drag it to the Values area in the layout section.
- Repeat step 1 to create as many copies of that field that you want to display in the Value area.
On the Options tab, in the Data group, click Change Data Source, and then click Change Data Source. The Change PivotTable Data source dialog box is displayed. Do one of the following: To use a different Excel table or cell range, click Select a table or range, and then enter the first cell in the Table/Range text box.
How do you summarize a value field in a PivotTable? ›In the PivotTable, right-click the value field you want to change, and then click Summarize Values By. Click the summary function you want.
Can you use the same field twice in a PivotTable? ›Right-click the row or column label you want to repeat, and click Field Settings. Click the Layout & Print tab, and check the Repeat item labels box. Make sure Show item labels in tabular form is selected.
Which tool toggles the PivotTable field list on and off? ›The field list for Pivot Table can easily be toggled on and off using the ribbon menu. Follow the steps below to show/hide Pivot Table Field List using the ribbon menu: STEP 1: Click on any cell in the Pivot Table. STEP 2: Go to PivotTable Analyze > Field List.
What do the field buttons do? ›
When you create a pivot chart, you'll see something called "field buttons" sitting on top of the chart area. In the upper left, you'll see buttons for value fields, which correspond to values being plotted in the chart. You'll also see a button to filter axis or category values.
What is not included in a box in the pivot table fields list? ›A: Correct answer is Text box. Text box can be used to add to insert values.
What is the basic data structure for a pivot table? ›A pivot table usually consists of row, column, and data fields. In this example, the rows are organized by Month Name and City. The columns are organized by Campaign, and the data are the Resulting Sales and Profit figures for each campaign.
What are fields in Excel? ›A field is an element in which one piece of information is stored, such as the received field. Usually a column in a table contains the values of a single field. However, you can show several fields in a column by using a Formula or a Combination field.
Why is my pivot table not picking up all data? ›Show Missing Data
Refresh the pivot table, to update it with the new data. Right-click a cell in the Product field, and click Field Settings. On the Layout & Print tab, add a check mark in the 'Show items with no data' box. Click OK Go to Top.
In the PivotTable, select the field of interest. This displays the PivotTable Tools tab on the ribbon. On the Analyze or Options tab in the Active Field group, click Field Settings. The Field Settings dialog box displays labels and report filters; the Values Field Settings dialog box displays values.
How do I create a field in a pivot table? ›- Click the PivotTable. ...
- On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
- In the Name box, type a name for the field.
- In the Formula box, enter the formula for the field. ...
- Click Add.
- In Excel, click Power Pivot > Manage to open the Power Pivot window.
- View the tabs in the Power Pivot window. Each tab contains a table in your model. ...
- To view the origin of the table, click Table Properties.
- In the PivotTable, right-click a value and select Group.
- In the Grouping box, select Starting at and Ending at checkboxes, and edit the values if needed.
- Under By, select a time period. For numerical fields, enter a number that specifies the interval for each group.
- Select OK.
- Select the Data You Want to Analyze.
- Choose “Pivot Table” from the “Insert” Tab.
- Select the Data You Want to Add to Your Table.
- Open the New Worksheet Tab.
- Choose the Fields for Your Pivot Table.
- Drag the Fields to the Desired Area.
- Change the Value Field.
How do you use data fields in Excel? ›
- Select any cell in the Excel Table.
- Click on the Form icon in the Quick Access Toolbar.
- Enter the data in the form fields.
- Hit the Enter key (or click the New button) to enter the record in the table and get a blank form for next record.
On the Options tab, in the Active Field group, click Active Field, and then click Field Settings. The Value Field Settings dialog box is displayed. The Source Name is the name of the field in the data source.
What is field vs item in pivot table? ›The key difference between calculated fields and calculated items is that: Calculated Fields are formulas that can refer to other fields in the pivot table. Calculated Items are formulas that can refer to other items within a specific pivot field.