Advertisements

Use the Field List to arrange fields in a PivotTable

Applies to: Excel for Office 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.

After you produce a PivotTable, you’ll notice the Field List. You can modify the design of the PivotTable by inserting and organising its fields. If you want to sort or filter the columns of data displayed in the PivotTable, see Sort data in a PivotTable and Filter data in a PivotTable.

Windows

The Field List should emerge once you select anywhere in the PivotTable. If you press inside the PivotTable but don’t notice the Field List, access it by pressing anywhere in the PivotTable. Then, display the PivotTable Tools on the ribbon and press AnalyseField List.

Field List option on the ribbon

The Field List includes a field section whereby you select your relevant fields to present in your PivotTable, and the Areas section (at the bottom) with which you can order those fields based on your preferences.

Field List showing a field section and an areas section

Tip: If you want to change how sections are shown in the Field List, press the Tools button  Field List Tools button and then choose your desired layout.

Field List Tools menu

Add and rearrange fields in the Field List

Apply the field section of the Field List to create fields to your PivotTable, by checking the box beside field names to arrange those fields in the standard area of the Field List.

NOTE: Typically, nonnumeric fields are added to the Rows area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) date and time hierarchies are added to the Columns area.

Utilise the areas section (at the bottom) of the Field List to switch fields to your preferences by dragging them between the four areas.
 

Fields that you add in various areas are displayed in the PivotTable as follows:

  • Filters area fields are shown as top-level report filters above the PivotTable, like this:

    Field in Filters area
  • Columns area fields are presented as Column Labels at the top of the PivotTable, like this:

    Field in the Columns area

Based on the hierarchy of the fields, columns may be nested within columns that are higher in position.

  • Rows area fields are illustrated as Row Labels on the left side of the PivotTable, like this:

    Field in Rows area

Depending on the hierarchy of the fields, rows may be nested inside rows that are higher in position.

  • Values area fields are displayed as summarised numeric values in the PivotTable, like this:

    Field in the Values area

If you have several fields in an area, you can jumble the order by dragging the fields into your desirable exact position. To erase a field from the PivotTable, drag the field out of its areas section.

More about PivotTables

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Advertisements

Leave a Reply

%d bloggers like this: