PivotFlashRoman2

I don’t like PivotTable Field Captions

PTwFieldCaptions

You may turn them off manually:

  1. Click on a PivotTable
  2. The PivotTable Tools Tab is activated slightly above the Ribbon
  3. Click on the tab, “Analyze”
  4. In the PivotTable Group, click on the Options drop-down
  5. Click on options
  6. In the PivotTable Options Dialog, click on the Display tab
  7. Clear the tick mark on, “Display field captions and filter drop downs”

PTOptionsDialogFinal

But what if you have many PivotTables, or what if sometimes you want the Field Captions on and sometimes off? Let’s look at some VBA:

Option Explicit

Sub PTDisplayFieldCptions()

    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim pt As PivotTable
    
    'Excel environment - speed things up
        Application.ScreenUpdating = False
        
    'Initialize variables
        Set wb = ThisWorkbook
        
    'Loop all PivotTables in all worksheets in the workbook
    'Turn off Field Captions
        For Each ws In wb.Worksheets
            For Each pt In ws.PivotTables
                pt.DisplayFieldCaptions = False
            Next pt
        Next ws
        
    'Tidy up
        'Destroy objects
            Set wb = Nothing
            
        'Restore Excel environment
            Application.ScreenUpdating = True
   
End Sub

Or, maybe you would like to toggle the display so if off, tune on and vice versa:

Option Explicit

Sub PTDisplayFieldCptionsToggle()

    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim pt As PivotTable
    
    'Excel environment - speed things up
        Application.ScreenUpdating = False
        
    'Initialize variables
        Set wb = ThisWorkbook
        
    'Loop all PivotTables in all worksheets in the workbook
    'Turn off Field Captions
        For Each ws In wb.Worksheets
            For Each pt In ws.PivotTables
                If pt.DisplayFieldCaptions = False Then
                    pt.DisplayFieldCaptions = True
                Else
                    pt.DisplayFieldCaptions = False
                End If
            Next pt
        Next ws
        
    'Tidy up
        'Destroy objects
            Set wb = Nothing
            
        'Restore Excel environment
            Application.ScreenUpdating = True

End Sub

PTwFieldCaptionsOff

No more Field Captions

Other PivotTable Posts At dataprose.org

  1. PivotTable Conditional Formatting
  2. PivotTable Cell Borders

Additional Resources

  1. Contextures
  2. Peltier Technical Services, Inc.
  3. Chandoo
, ,
Trackback

4 comments untill now

  1. Alfred Vachris @ 2014-02-23 06:21

    nice work
    Alfred

  2. Thanks Alfred.

  3. […] Snyder shares his code for turning the field captions on and off in a pivot table. Tip: If it’s just the headings, “Row Labels” and “Column Labels” that […]

  4. […] my last PivotTable post, I showed how to hide PivotTable Field Captions. However, that hides the Filter Arrows as […]

Add your comment now