Home > Pivot Tables > Pivot Fields

Excel Pivot Table Field Settings

How to use Pivot Table field settings, to show or hide pivot table subtotals, show items with no data, show top items and sort fields, in Microsoft Excel

Pivot Field Settings - Introduction

When you right-click a pivot table cell, a pop-up menu appears. This menu is contextual - it shows commands that apply to the type of cell that you right-clicked.

• Some commands, such as Refresh, and PivotTable Options, appear in all of the pop-up menus.
  • You can use those commands no matter which part of the pivot table the cell is in.
• Most cells will also have a command that lets you change the settings for a pivot field.

In most of the pop-up menus, you'll see one of these two different field setting commands, depending on the cell type:

1. Field Settings - For pivot fields in the Row, Column or Filter areas
2. Value Field Settings - For pivot fields in the Values area

1) Field Settings

The examples on this page show how to make changes to pivot fields in the Row, Column and Filter areas.

When you right-click a cell in one of those areas, The Fields Settings command is in the pop-up menu, near the bottom of the list. Click that command, to open the Field Settings dialog box.

The examples on this page show how to use the settings on the two tabs:

• Subtotals & Filters
• Layout & Print

Field Settings on Excel Ribbon

Instead of using the right-click pop-up menu, you can open the Field Settings dialog box by using a command on the Excel Ribbon.

To use the Ribbon command, follow these steps:

• Select any cell in the pivot table's Row, Column or Filter area
• On the Ribbon, click the PivotTable Analyze tab
• At the left end of the Ribbon, in the Active Field group, you'll see the name of the active cell's pivot field
• Below that field name, click the Field Settings button, to open the Field Settings dialog box

2) Value Field Settings

If you right-click a cell in the Values area of a pivot table, the pop-up menu has a Value Field Settings command, instead of a Field Settings command.

• Note: Value Field Settings are not shown on this page, so use the links below, to find the information that you need.

In the Value Field Settings dialog box, you can do the following:

1. Change the way that the data is summarized in a value field. (Summary Functions).
2. Select a calculation type for the field (Show Values As)
3. Change the field's number formatting (Number Format)

Follow those links, to learn more about the Value Field Settings.

Manually Hide or Show Subtotals

After you add a pivot field to the Row or Columns area, it might show subtotals automatically. You can manually turn those subtotals on or off, as needed.

To manually hide or show subtotals for a pivot field:

1. Right-click the field heading, or one of the field items
2. Click on the Subtotal [field name] command, to toggle the setting
   • If the setting currently has a check mark, subtotals are on
   • If the setting currently does not have a check mark, subtotals are off
3. After you click on the command, the setting changes

Use Macro to Hide Subtotals

You can use a VBA macro in your Excel workbook to programmatically hide subtotals in a PivotTable. This example uses the PivotField property, to hide all the subtotals.

Note: Instead of using pt.PivotFields in the code, you can make the following changes:

• To hide only the Row field subtotals, use the pt.RowFields property.
• To hide only the Column field subtotals, use the pt.ColumnFields property.

Sub NoSubtotals()
'pivot table tutorial by contextures.com
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
For Each pt In ActiveSheet.PivotTables
  For Each pf In pt.PivotFields
    'Set index 1 (Automatic) to True,
    'so all other values are set to False
    pf.Subtotals(1) = True
    pf.Subtotals(1) = False
  Next pf
Next pt
End Sub   

Quickly Remove a Pivot Field

After you create a pivot table, you might want to remove a field from the layout. You don't need to go to the field list, find that field and remove its check mark, or drag the pivot field out of the Row Labels area in the field list.

NOTE: You can also use macros to remove pivot fields -- Remove Pivot Fields with Macros

To quickly remove a pivot field from the layout:

1. Right-click on an item in the pivot field. In this example, a cell in the Product field was right-clicked.
2. Click Remove [field name]

   

The pivot field is immediately removed from the layout

To see the steps for quickly removing a pivot field, you can watch this short video tutorial.

Add All Remaining Fields to Layout

In the Pivot Table Field List, you can check a field name to add it to the pivot table layout. You have to do these one at a time though -- there isn't a "Select All" checkbox.

With the following code, you can add all the unchecked fields to either the Row Labels area or to the Values area of the layout.

Put this code in a regular code module. Then select a cell in the pivot table that you want to update, and run the macro.

Add All Remaining Fields to Row Labels

If you have a long list of fields, you could manually add a few to the Values area, or the Report Filter area. Then use the following code to add the remaining fields to the Row Labels area.

Sub AddAllFieldsRow()
Dim pt As PivotTable
Dim iCol As Long
Dim iColEnd As Long

Set pt = ActiveSheet.PivotTables(1)

With pt
    
   iCol = 1
   iColEnd = .PivotFields.Count

    For iCol = 1 To iColEnd
        With .PivotFields(iCol)
          If .Orientation = 0 Then
              .Orientation = xlRowField
          End If
        End With
    Next iCol

End With

End Sub

Add All Remaining Fields to Values Area

If you have a long list of fields, you could manually add a few to the Row Labels area. Then use the following code to add the remaining fields to the Values area.

Sub AddAllFieldsValues()
Dim pt As PivotTable
Dim iCol As Long
Dim iColEnd As Long

Set pt = ActiveSheet.PivotTables(1)

With pt
    
    iCol = 1
   iColEnd = .PivotFields.Count

    For iCol = 1 To iColEnd
        With .PivotFields(iCol)
          If .Orientation = 0 Then
              .Orientation = xlDataField
          End If
        End With
    Next iCol

End With

End Sub

Show Items with No Data

By default, the Pivot Table shows only the items for which there is data. In the example shown below, not all colours were sold to each customer. You may wish to see all the items for each customer, even those with no data.

To see the steps for showing all the data in a pivot field, watch this short video tutorial. The written instructions are below the video.

Show Items with No Data

When you create a Pivot Table, by default, it only shows the items for which there is data.

For example, in the pivot table shown below, not all products were sold in every city. There were no pretzel orders in New York.

Even though this is the default behaviour for a pivot field, you can change a pivot field setting, to see all the items from the source data, even the items have no records for some pivot field combinations.

Show all the data in a Pivot Field

Make the following change for each field in which you want to see all the items, even those with no data:

1. Right-click an item in the pivot table field, and in the pop-up menu, click Field Settings
2. In the Field Settings dialog box, click the Layout & Print tab.
3. In the Layout section, check the box for 'Show items with no data' .
4. Click the OK button, to close the dialog box, and apply the revised setting.

Missing Data in Pivot Table

To see items with no data in a pivot table, you can change the pivot table settings, as described in the section above.

However, that setting only displays items that are included in the source data at least once. For example, if you recently started selling a new product, but have no sales yet, it won't appear in your pivot table, even if you turn on the "Show Items With No Data" setting.

Show Missing Data

To show missing data, such as new products, you can add one or more dummy records to the pivot table, to force the items to appear.

For example, to include a new product -- Paper -- in the pivot table, even if it has not yet been sold:

1. In the source data, add a record with Paper as the product, and 0 as the quantity
2. Refresh the pivot table, to update it with the new data
3. Right-click a cell in the Product field, and click Field Settings.
4. On the Layout & Print tab, add a check mark in the 'Show items with no data' box.
5. Click OK   Go to Top

Count Missing Data as Zero

In addition to simply showing the names of missing data, you might need to show a count of that data in the pivot table report. Get the sample file for this example in the download section below. Thanks to AlexJ for sharing his solution to this problem.

On my Contextures blog, there is a Health and Safety example, which creates a quarterly report on safely incidents. Two of the six departments did not have incidents, and are not listed in the data. However, we'd like the report to show a count of zero incidents for those departments, instead of omitting them.

Here is the pivot table with the four departments that had incident counts.

To include the missing departments, add dummy records to the source data, and add an "X" in the numeric field that will be counted (ID field in this example).

If the field used the default Count function, it will show a 1 for the missing departments, because it is counting text entries, as well as numeric entries. Instead, use the Count Numbers summary function, to show those values as Zeros.

To change that setting, right-click one of the numbers, and click Summarize Values By, then click More Options. In the list of functions, select Count Numbers.

With that function selected, the pivot table shows the missing departments, with zeros as the count of incidents.

Show Top Items Only

Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum.

There are written steps on the Pivot Table Top 10 Filters page.

Include New Items in Manual Filter

If you click on the arrow in a pivot table heading, you can filter the field's items, by using the check boxes. In the screen shot below, only two of the technician names have been selected, and the others will be hidden.

However, if you add new records in the source data, or update the existing records, new names might be added to the source data.

Then, if you refresh the pivot table, those new names can appear in the filtered pivot table, even though they were not originally selected. Here, Smith appears in the pivot table, after that name was added to the source data.

To prevent new items from appearing after a manual filter has been applied, you can change a setting for the pivot field:

1. Right-click one of the items in the pivot field, and click Field Settings
2. On the Subtotals and Filters tab, in the Filter section, remove the check mark for 'Include New Items in Manual Filter'
3. Click OK

NOTE: If any new items were included before you change the setting, go back to the manual filter and remove the check marks for those items.

To see the steps for changing the pivot field setting, please watch this short video tutorial.

Repeat Item Labels

To see the steps for repeating labels in all fields, or a single field, please watch this short Excel pivot table video tutorial. The written instructions are below the video.

Repeat Item Labels

In Excel 2010, and later versions, you change a field setting so that the item labels are repeated in each row.

This feature does not work if the pivot table is in Compact Layout, so change to Outline form or Tabular form, if necessary, before following the rest of the steps.

To change the report layout:

1. Select a cell in the pivot table
2. On the Ribbon, click the Design tab, and click Report Layout
3. Click Show in Outline Form, or click Show in Tablular Form

To show the item labels in every row, for all pivot fields:

1. Select a cell in the pivot table
2. On the Ribbon, click the Design tab, and click Report Layout
3. Click Repeat All Item Labels

To show the item labels in every row, for a specific pivot field:

1. Right-click an item in the pivot field
2. In the Field Settings dialog box, click the Layout & Print tab
3. Add a check mark to Repeat item labels, then click OK

Insert Blank Lines

To make a complex pivot table easier to read, add a blank line after each item in the main row fields. In the screen shot below, the outer field is "Category", and I'd like a blank row after the Revenue Total.

• Right-click on one of the items in the Category field
• Click Field Settings, and click the Layout & Print tab
• Add a check mark to "Insert blank line after each item label"
• Click OK, to close the Field Settings.

However, don't do this for every field, or the pivot table will be too spread out -- use this "blank line" setting on one or two of the outer fields.

NOTE: You can also use PivotTable Styles to make the data easier to read. There are many built-in styles, or create custom styles, using your own colour and formatting preferences.

Sort All Fields in Ascending Order

After adding new records to your data, new items may appear at the end of the existing data, instead of being listed alphabetically. The following code will sort all fields in all Excel Pivot Tables.

Sub SortAllFields()
'pivot table tutorial by contextures.com
On Error Resume Next
Application.ScreenUpdating = False

Dim pt As PivotTable
Dim ws As Worksheet
Dim pf As PivotField

For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.ManualUpdate = True
    
    For Each pf In pt.PivotFields
        pf.AutoSort xlAscending, pf.Name
    Next pf
    pt.ManualUpdate = False
  Next pt
Next ws

Application.ScreenUpdating = True

End Sub

Get the Sample Workbooks

Missing Data: Download the sample file with health and safety data, and add dummy records, to show missing data with a count of zero. The zipped file is in xlsx format, and does not contain any macros.

Pivot Field Macros: To see the code, and test the macros, download the Pivot Table Field Settings file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the code.

More Pivot Table Resources

Remove Pivot Fields with Macros

Pivot Table Article Index

Pivot Table Video Index