Fixing Excel’s Pivot Table headings

Pivot Tables are incredibly powerful and easy to use. Unfortunately their headings can include the terms “Sum of” or “Count of”. This is not always what you’d like to present to users. A macro to the rescue.

The following macro will remove those terms from the current Pivot Table.  The macro at the bottom of the post can fix all the Pivot Tables in a file.

You can download a file containing both macros at the bottom of the post.

These macros add a space to the field name – you can’t use the actual field name in a Pivot Table  – Excel won’t let you.

Warning: Macros can’t be undone so you should save your file before running this macro. If the result isn’t what you expected then you can close the file without saving.

Select any cell in the Pivot Table and then run the macro.

Note: the green lines are comments only and are not required for the macro to work.

Sub RenamePTFields()

Dim pt As PivotTable
Dim pf As PivotField

'some of the commands may cause VBA errors in some situations
'- these will be ignored as they are not important
On Error Resume Next

'capture the selected pivot table
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

  'if the activecell is not in a pivot table let the user know
  'and stop the macro
  If pt Is Nothing Then
   MsgBox "Please select a cell in a pivot table."
   Exit Sub
  End If

  'go thru all the data fields in the pivot table
  For Each pf In pt.DataFields
    'add a space to the data field name because Excel won't
    'allow the use of a data field name within the pivot table
    pf.Caption = pf.SourceName & " "
  Next pf

'reset error handling
On Error GoTo 0

'clear object variables
Set pt = Nothing
Set pf = Nothing

End Sub

If you wanted to apply this to all the Pivot Tables in a file you can use the following macro.

Sub RenamePTFieldsAll()

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet

'some of the commands will cause VBA errors in some situations
'- these will be ignored
On Error Resume Next

'go through all the sheets
For Each ws In Worksheets

  For Each pt In ws.PivotTables

  'go through all the data fields in the pivot table
    For Each pf In pt.DataFields
      'add a space to the data field name because Excel won't
      'allow the use of a data field name within the pivot table
      pf.Caption = pf.SourceName & " "
    Next pf

  Next pt

Next ws

'reset error handling
On Error GoTo 0

'clear object variables
Set pt = Nothing
Set pf = Nothing
Set ws = Nothing

End Sub

Example File Link below.

Fix_PT_Field_Names Excel File

Click here to see how you can run a Macro off a Quick Access Toolbar icon.