Fixing Excel’s Pivot Table headings

Say goodbye to Sum of

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.

Related Posts

Posted on
Categories: Data, Excel Blog, Macros

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 thoughts on “Fixing Excel’s Pivot Table headings”

  1. Hello,
    Thanks very much for the helpful code. Just a heads up – both versions contain (what seems to be) an error caused by “&amp”. I’m guessing due to copying and pasting.
    Cheers,
    -Daniel

    Reply

  3. Thanks a lot 2 times!!
    First, for solving my Problem with header names.
    Second, for such an efficient code. You wrote much less lines than most proposals I have researched on the web (I’ve been researching how to change from “sum” to “count” and other things), you added solutions to possible errors (not having selected a Pivot table, programming bugs…) and you even left the kitchen clean after dinner with “Set pt = Nothing” and “Set pf = Nothing”.
    NICE WORK!!!!

    Reply