How to show or hide pivot table subtotals, show items with no data, show top items and sort fields
To manually hide or show subtotals for a pivot field:
You can use a macro to hide subtotals in a PivotTable. This example
uses the PivotField property, to hide all the subtotals.
To hide only the Row field subtotals, use the RowFields property.
To hide only the Column field subtotals, use the 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
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.
When you create a Pivot Table, it only shows the items for which there is data. In the pivot table shown below, not all colours were sold to each customer. You can change a pivot table setting, to see all the items for each customer, even the items with no data.
Make the following change for each field in which you want to see all the data:
Make the following change for each field in which you want to see all the 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.
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:
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.
Instead of showing all the items in a field, you can restrict the Pivot Table to show only the top (or bottom) items.
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.
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:
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
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.
Last updated: July 10, 2021 3:22 PM