
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
- PivotTable Hide Field Captions – Change Font Color Option
- PivotTable Hide Field Captions
- PivotTable Conditional Formatting
- PivotTable Cell Borders
Additional Resources – PivotTables
- Contextures
- Peltier Technical Services, Inc.
- Chandoo
Additional Resources – Custom Number Formats
- A comprehensive guide to Number Formats in Excel – Jon von der Heyden
Excel, PivotTables, VBA

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
- PivotTable Hide Field Captions
- PivotTable Conditional Formatting
- PivotTable Cell Borders
Additional Resources
- Contextures
- Peltier Technical Services, Inc.
- Chandoo
Excel, PivotTables, VBA
Very good post regarding SQL Joins by Kosta Hristov on his blog, “Developing The Future”. Check it out.
SQL

I don’t like PivotTable Field Captions

You may turn them off manually:
- Click on a PivotTable
- The PivotTable Tools Tab is activated slightly above the Ribbon
- Click on the tab, “Analyze”
- In the PivotTable Group, click on the Options drop-down
- Click on options
- In the PivotTable Options Dialog, click on the Display tab
- 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
- PivotTable Conditional Formatting
- PivotTable Cell Borders
Additional Resources
- Contextures
- Peltier Technical Services, Inc.
- Chandoo
Excel, PivotTables, VBA

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
- Contextures
- Peltier Technical Services, Inc.
- Chandoo
Excel, PivotTables, Range Object, VBA