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.
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
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:
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:
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.
In the Value Field Settings dialog box, you can do the following:
Follow those links, to learn more about the Value Field Settings.
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:
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:
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
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:
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.
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.
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
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
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:
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:
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.
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.
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:
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.
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:
To show the item labels in every row, for all pivot fields:
To show the item labels in every row, for a specific pivot field:
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.
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.
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
Last updated: August 31, 2022 3:02 PM