Search Contextures Sites

 

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

 

 
Pivot Tables -- Introduction 
Pivot Tables -- Create a Pivot Table in Excel 2007 
Pivot Tables -- Data Field Layout
 Pivot Tables -- Show and Hide Items
Pivot Tables -- Clear Old Items
Pivot Tables -- Field Settings
Pivot Tables -- GetPivotData
Pivot Tables -- Grouping Data
Pivot Tables -- Multiple Consolidation Ranges
Pivot Tables -- Printing   
Pivot Tables -- Custom Calculations 
Pivot Tables -- Pivot Cache     
Pivot Tables -- Protection  
Pivot Tables -- Grand Totals
Pivot Tables -- Running Totals  
Pivot Tables -- Filter Source Data   		 
   

Learn how to create Excel dashboards.
       Home     Excel Tips     Excel Files      Blog    Contact

RSS Feed

Privacy Policy

 

 

The Excel Store

 

Last updated: February 20, 2009 11:27 PM