Remove Sum of in Pivot Table Headings

When you’re building a pivot table, if you add fields to the Values area, Excel automatically adds “Sum of” or “Count of” to the start of the field name. You can manually remove that text, or use macros to quickly change the headings. There is one macro example here, and more on my Contextures website.

Automatic Heading Text

This screen shot shows a couple of fields in the Values area of a pivot table.

• The Quantity heading has be revised, to remove the “Sum of” text
• The TotalPrice heading has not be changed, and still shows the “Sum of” text.

Manually Change a Heading

To remove the “Sum of” or “Count of” from a single value heading, just type over the existing heading, to fix it.

NOTE: Excel won’t allow you to use the exact name of the field, such as “Quantity”. Add a space character at the beginning or end of the text, and Excel with accept that as a valid heading.

See the Steps for Manual Change

To see the steps for renaming the headings manually, watch this short video.

Remove Sum Of With a Macro

If you want to quickly change multiple headings, to remove “Sum of”, or other automatic text, use a macro.

Previously, I’ve posted macros to fix the headings on Normal pivot tables. However, those macros might not work correctly in newer versions of Excel – depending on how you build your pivot tables.

Pivot Table Types

if you create a pivot table in newer versions of Excel, there is a check box for “Add this data to the Data Model”.

• If you check that box, Excel creates an OLAP-based pivot table, instead of a Normal pivot table.
• If you DO NOT check that box, Excel creates an Normal pivot table

SourceName Property

In the original macros, which work nicely with Normal pivot tables, the code uses the SourceName property for each Value field, and adds a space character at the end.

If the column heading in the source data is “QtySold”, Excel returns “QtySold” for the SourceName property.

For OLAP-based pivot tables, the SourceName property returns a structured name for the Value field, similar to this:

• [Measures].[Sum of QtySold]

That would take extra work to clean up, when running a macro.

SourceCaption Property

For OLAP-based pivot tables, I’ve use the SourceCaption property. It returns the heading from the Value field in the pivot table layout. For example, “Sum of QtySold”

That’s easier to clean up, because it doesn’t have the brackets and periods that are in the SourceName property.

Macro to Remove Sum Of

Here is one of my new macros to remove Sum Of from the Value Headings. It is designed for OLAP-based pivot tables, and fixes the pivot table for the selected cell on the worksheet.

Before you run the macro:

• make a backup copy of your workbook, just to be safe.
• then, select a pivot table cell, and run the macro.

The Macro Code – OLAP

There are two procedures listed below. The first macro, ValueCaptionsSelPT_OLAP, calls the second one, ChangeHead_OLAP.

Be sure to copy both procedures into your workbook.

Sub ValueCaptionsSelPT_OLAP()
'for normal pivot tables
'removes the "Sum of"
'   from Value field headings
'   in selected pivot table only
Dim pt As PivotTable
On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo exitHandler

If pt Is Nothing Then
  MsgBox "Select a pivot table cell" _
      & vbCrLf & "and try again"
  GoTo exitHandler
End If
  
Application.EnableEvents = False
Application.ScreenUpdating = False
  
  ChangeHead_OLAP pt

exitHandler:
  Set pt = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

errHandler:
  Resume exitHandler

End Sub

Copy this procedure too.

Sub ChangeHead_OLAP _
  (ByRef myPT As PivotTable)
''Called in ValueCaptions macros

Dim pf As PivotField
Dim lNum As Long
Dim lFind As Long
Dim strFind As String
Dim strHead01 As String
Dim strHead02 As String
strFind = " of "

myPT.ManualUpdate = True

For Each pf In myPT.DataFields
  strHead01 = pf.SourceCaption
  lFind = InStrRev(strHead01, strFind) _
          + Len(strFind) - 1
  strHead02 = Replace(strHead01, _
    Left(strHead01, lFind), "")
  pf.Caption = strHead02 & " "
Next pf

myPT.RefreshTable
myPT.ManualUpdate = False

Set pf = Nothing

End Sub

Get All the Remove Sum Of Macros

To get the rest of the macros that remove Sum Of from the Value headings, go to the Pivot Table Value Heading Macros page.

You can copy the code from that page, and add it to your workbook. Or, download the sample files, to get the code, and pivot tables for testing.

_______________________