Remove Sum Of From Pivot Table With Macro

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.

change pivot table heading with macro

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.

__________________________

This entry was posted in Layout. Bookmark the permalink.

Leave a Reply

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