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:


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:


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


Click on “Custom” in the Category Pane


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


Field Captions are gone!!



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


Great tip, Shane – thanks!

Other PivotTable Posts At

  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
, ,

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