Excel -- Pivot Tables -- Field Settings

  1. Manually Hide or Show Subtotals
  2. Programmatically Hide Subtotals
  3. Show Items with No Data
  4. Show Top Items Only
  5. Sort All Fields in Ascending Order

Download the zipped sample file

Manually Hide or Show Subtotals

To manually hide subtotals for a field:

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. For Subtotals, select 'None'
  3. Click OK

To manually show subtotals for a field:

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. For Subtotals, select 'Custom'
  3. Select one of the functions from the list, e.g. 'Average'
  4. Click OK

Programmatically Hide Subtotals

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()
'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

 

 

Show Items with No Data

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

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. Check the 'Show items with no data' check box.
  3. Click OK

Show Top Items Only

Instead of showing all the items in a field, you can restrict the Pivot Table to show only the top (or bottom) items.

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. Click the Advanced button
  3. Under 'Top 10 AutoShow', select On.
  4. For 'Show', select Top or Bottom
  5. Click the Scroll buttons, or type, to enter the number of items to show.
  6. Click OK, click OK

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 pivot tables.

Sub SortAllFields()
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

 

 
1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing   
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache      
12. Pivot Tables -- Protection     
13. Pivot Tables -- Grand Totals 		 
   
 

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:47 PM