PTFlash4

In my last post, I showed how to hide the Field Captions of a Pivot Table by changing the font color to match the Interior ColorIndex of the Range. Shane replied on one of the LinkedIn Groups and recommended using the Custom Number Format, “;;;” instead. Let’s give it a try:

PTFieldCaptions1

Select the 3 cells that you want to remove the Captions. Click on the first cell, hold down the Ctrl key and click the other 2 cells:

PTSelectFieldCaptions

Click [Ctrl]+[1] on the keyboard to invoke the Format Cells Dialog.

FormatCellsDialog

Click on “Custom” in the Category Pane

FCD_CustomArrow

In the Type: InputBox, enter 3 semicolons, “;;;” and click, “OK”

FCD_CustomInputF

Field Captions are gone!!

FieldCaptionsManual

VBA

What we do manually, we should try to do with VBA. What if we have many PivotTables on many Worksheets? VBA to the rescue (Assume I restored the Captions so I don’t have to post another screen shot )

Sub PTHideFieldCaptionsCustomNumberFormat()

    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim pt As PivotTable
        Dim rng As Range
        
    'Excel environment - speed things up
        Application.ScreenUpdating = False

    'Initialize variables
        Set wb = ThisWorkbook

    'Loop all PivotTables in all worksheets in the workbook
    'Set the Number Format of Field Captions so they will not display
        For Each ws In wb.Worksheets
            For Each pt In ws.PivotTables
                Set rng = pt.ColumnRange

                'Set the Number Format of the Field Captions to that nothing is displayed
                    Call SetRangeNumberFormat(rng:=rng)
    
            Next pt
        Next ws

    'Tidy up
        'Destroy objects
            Set rng = Nothing
            Set wb = Nothing

        'Restore Excel environment
            Application.ScreenUpdating = True

End Sub

'--------------------------------------------------------------------
Private Sub SetRangeNumberFormat(rng As Range)
                            
    Dim rngRow As Range
    Dim rngColumn As Range
    Dim rngBig As Range
    Const strNumberFormat As String = ";;;"

    Set rngRow = rng.Offset(1, -1).Resize(rng.Rows.Count - 1, 1)
    Set rngColumn = rng.Offset(0, -1).Resize(1, 2)
    Set rngBig = Union(rngRow, rngColumn)
    
    rngBig.NumberFormat = strNumberFormat
    
    Set rngBig = Nothing
    Set rngColumn = Nothing
    Set rngRow = Nothing

End Sub

FieldCaptionsVBA

Great tip, Shane – thanks!

Other PivotTable Posts At dataprose.org

  1. PivotTable Hide Field Captions – Change Font Color Option
  2. PivotTable Hide Field Captions
  3. PivotTable Conditional Formatting
  4. PivotTable Cell Borders

Additional Resources – PivotTables

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

Additional Resources – Custom Number Formats

  1. A comprehensive guide to Number Formats in Excel – Jon von der Heyden
, ,
Trackback

only 1 comment untill now

  1. Jeff Weir @ 2014-08-09 03:03

    Oh THAT number format hack is a very cool trick!

Add your comment now