thewindata

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

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

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

In my last PivotTable post, I showed how to hide PivotTable Field Captions. However, that hides the Filter Arrows as well.

What if you want to hide the Field Captions, but display the filter arrows?

I change the font color of the Field Captions to match the Range Interior ColorIndex

Sub PTFieldCaptionsChangeFontColor()

    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim pt As PivotTable
        Dim pf As PivotField
        Dim rng As Range
        Dim lngRangeColor As Long

    'Excel environment - speed things up
        Application.ScreenUpdating = False

    'Initialize variables
        Set wb = ThisWorkbook

    'Loop all PivotTables in all worksheets in the workbook
    'Set Font Color of Field Captions to same color as Cell Interior ColorIndex
        For Each ws In wb.Worksheets
            For Each pt In ws.PivotTables
                
                'Get the interior fill color of the Column Range of the Pivot Table
                    Set rng = pt.ColumnRange
                    lngRangeColor = GetRangeColor(rng:=rng)
                    
                'Set the Font Color of the Field Captions to the same color as the Range Interior Color
                    Call ChangeFontColor(rng:=rng, _
                                         lngColor:=lngRangeColor)
    
            Next pt
        Next ws

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

        'Restore Excel environment
            Application.ScreenUpdating = True

End Sub
'-----------------------------------------------------------------
Private Function GetRangeColor(rng As Range) As Long

    Dim lngColor As Long
    lngColor = rng.Interior.ColorIndex
    GetRangeColor = lngColor
    
End Function
'-----------------------------------------------------------------
Private Sub ChangeFontColor(rng As Range, _
                            lngColor As Long)
                            
    Dim rngRow As Range
    Dim rngColumn As Range
    Dim rngBig As Range

    Set rngRow = rng.Offset(1, -1).Resize(rng.Rows.Count - 1, 1)
    Set rngColumn = rng.Resize(rng.Rows.Count - 1, 1)
    Set rngBig = Union(rngRow, rngColumn)
    
    rngBig.Font.Color = lngColor
    
    Set rngBig = Nothing
    Set rngColumn = Nothing
    Set rngRow = Nothing

End Sub

Other PivotTable Posts At dataprose.org

1. PivotTable Hide Field Captions
2. PivotTable Conditional Formatting
3. PivotTable Cell Borders

Additional Resources

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

Very good post regarding SQL Joins by Kosta Hristov on his blog, “Developing The Future”. Check it out.

I don’t like PivotTable Field Captions

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”

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

No more Field Captions :-D

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

I wanted to loop through a large workbook with lots of PivotTables to set a common conditional format on the DataBodyRange of each PivotTable.

Here’s my initial PivotTable with no conditional formatting:

I would like change the text to red for any value that is less than 0.97 :

Option Explicit

Sub PTConditionalFormatting()

    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim pt As PivotTable
        Dim rng As Range
        Dim dblLow As Double
        Dim dblHigh As Double
    
    'Excel environment - speed things up
        Application.ScreenUpdating = False
        
    'Initialize variables
        Set wb = ThisWorkbook
        dblLow = 0
        dblHigh = 0.97
        
    'Loop all PivotTables in all worksheets in the workbook
    'Set conditional formatting
        For Each ws In wb.Worksheets
            For Each pt In ws.PivotTables
                Set rng = pt.DataBodyRange
                Call FormatRange(rng:=rng, _
                                 dblValueHigh:=dblHigh, _
                                 dblValueLow:=dblLow)
            Next pt
        Next ws
        
    'Tidy up
        'Destroy objects
            Set wb = Nothing
            
        'Restore Excel environment
            Application.ScreenUpdating = True    
End Sub
'----------------------------------------------------------------------------
Private Sub FormatRange(rng As Range, _
                        dblValueHigh As Double, _
                        dblValueLow As Double)

    With rng
        .FormatConditions.Delete
        .FormatConditions.Add(Type:=xlCellValue, _
                              Operator:=xlBetween, _
                              Formula1:=dblValueHigh, _
                              Formula2:=dblValueLow).Font.Color = vbRed

    End With
End Sub

Very helpful if you have a lot of PivotTables to loop through. The Sub() takes a Range Object as one of the arguments. This means you could use it and pass it any Range Object – not just from PivotTables – Worksheet Range, ListObject Range, other PivotTable Range.

Additional PivotTable Resources

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