Search Contextures Sites

Excel Pivot Table Tutorial -- 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 pivot table file for this pivot table tutorial
     

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

 

 

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 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
Download the zipped sample pivot table file for this pivot table tutorial

 

 

 

Pivot Table Tutorial List

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

Learn how to create Excel dashboards.

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Last updated: February 6, 2010 1:39 AM