When you put value fields into a pivot table, it automatically adds text to the headings, such as “Sum of Quantity”, if the field name is Quantity. You can manually change those headings, to remove the extra text, if there are only a few of them. If there are lots of fields to change, it’s quicker to remove that text with a macro.
Manually Change the Headings
To manually change the headings, select a heading cell, and type a new heading. The heading can’t be exactly the same as the original field name though, or Excel will show an error message.
To work around that limitation, add a space character at the beginning or end of the name.
You can see the steps in the video below, and get the written instructions on my website.
Remove Sum Of From Pivot Table With Macro
If you have more than a couple of pivot table headings to change, you can do the job quickly with a macro.
The example below will remove the extra text from the Value field headings, such as “Sum Of” or “Count Of”. It changes the heading so it shows the original field name, with a space character at the end.
Copy the code below, and store it in a regular code module in your workbook. There are instructions on my website, if you aren’t sure how to do that.
Sub ChangeCaptionsSelPT()
Dim pf As PivotField
Dim pt As PivotTable
On Error Resume Next
Set pt = ActiveCell.PivotTable
If Not pt Is Nothing Then
For Each pf In pt.DataFields
pf.Caption = pf.SourceName & " "
Next pf
Else
MsgBox "Please select a pivot table cell and try again."
End If
End Sub
All Pivot Tables on Worksheet or All in Workbook
If you want to clean up the headings in more than one pivot table, there are additional macros on my website.
One sample macro will clean the headings for all pivot tables on the active worksheet, and another macro will clean all headings in all pivot tables on all worksheets in the active workbook.
Also on my website, there is a sample file that you can download, which contains all the macros to change the field headings. The zipped file is in xlsm format, and you can enable the macros when you open the file.
__________________________