Home > Pivot > Layout > Headings

Remove Pivot Table "Sum Of" Headings

How to remove "Sum of" or "Count of" from pivot table headings. Change the headings manually, and avoid the error message, "PivotTable field name already exists." Or use one of my "Sum of" macros, to quickly change lots of headings!

remove "sum of" from pivot table value headings

1) "Sum Of" in Pivot Table Headings

When you add fields to a pivot table's Values area, the fields are automatically set as either Sum or Count.

Also, each field is automatically renamed. For example, Quantity becomes “Sum of Quantity”, or “Count of Quantity”.

Later, if you try to change those names, Excel might show an error message, “PivotTable field name already exists.”

error message PivotTable field name already exists

2) Video: Remove "Sum Of" in Pivot Headings

If you want to delete "Sum Of" from those long default names in your pivot table, you can change the field headings.

  • There's a little trick to it though, so watch my short video below, to see the steps

Written instructions are below the video, as well as the full video transcript.

  • Tip: If you have several fields to rename, you can use a macro instead of manually changing the headings.

Video Timeline:

  • 0:00 Introduction
  • 0:25 Change the heading
  • 1:11 Change Custom Name
  • 2:07 Change multiple headings

2.1) Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

Video Transcript: Remove Sum Of in Pivot Headings

Here is the full transcript for the Remove Sum Of in Pivot Headings video.

When you add fields to the values area of a pivot table, it automatically puts either Sum of, or Count of, in front of the field name, and that can make those columns a little too wide.

You can change those headings and get rid of that Sum of, or Count of. There's a little trick you have to do though, so that the pivot table will allow you to make the change.

I'll try and change this heading;

• I'll click on that cell

• and in the formula bar, I'll select Sum of,

• and delete everything that's in front of that total price.

When I press enter, Excel shows a message that warns me, PivotTable field name already exists.

• So I'll click, OK

• Then I'll press escape to get out of that cell.

If I take a look at this field, I'll right-click and go to Value Field Settings

We'll see that there is a source name, which is the field name in the source data. And then a custom name, which is Sum of, and then the field name.

The custom name, can't be the same as any source name in the pivot table.

So instead of trying to delete everything, including that space, I'm just going to delete Sum of.

That leaves a space in front of total price.

And when I click OK, now it accepts that name because it's not exactly the same. It's got a space in front of it.

I could do the same thing over here.

I'll select Sum of, but you can see the space is still there.

When I press delete and enter it accepts that name, because again, it's not exactly the same as the source name.

So with that little trick, you can have narrower columns.

The pivot headings look the same as the field names, but with that space at the front, they're different enough that Excel will allow them.

Now I changed each of those individually, but I'm going to take them out of the pivot table and then put them back in. And they get those long names again.

If you want to change several at once:

• you can select them

• then press Ctrl + H to open the find and replace window

• We want to replace Sum of, but don't type a space. We just want to replace the words and leave that leading space.

• We want to replace that with nothing.

• So I'll click Replace All, OK, and Close

and it fixes all the headings at once.

Then you can
• select the columns

• double-click to resize them

and your pivot tables should be much narrower.

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com

3) Pivot Heading Name Rule

When you change the field name in a pivot table, there is an important Excel rule that you need to follow, for the new pivot field caption.

3.1) Avoid Source Data Field Names

When you type a new pivot field heading, it cannot be the same as any of the original field names, in the pivot table source data.

For example, if the original field name is Quantity:

  • You cannot change 'Sum of Quantity' to 'Quantity'.

If you use an existing pivot field name, Excel will show the following error:

  • "PivotTable field name already exists."

error message PivotTable field name already exists

3.2) Change the Name Slightly

So, you can't use an existing pivot field name, but Excel will allow a field name that is even one character different from an existing name.

For example:

  • Type the field name with a space character at the beginning, or at the end
  • Or, type a shortened version of the pivot field name

In this example, instead of 'Quantity', you could change the name to:

  • 'Quantity ' or ' Quantity', or 'Qty'

3.3) Looks the Same on Worksheet

On the worksheet,

  • headings with an extra space charactere will look like the original field name
  • space character makes it different from the original field, so Excel will allow the name

rename pivot fields

4) How to Remove "Sum Of" from Heading

If you only have a few headings to fix, you can do the steps manually.

Follow the steps below, to remove "Sum of" from a single pivot table heading, or from multiple headings, all at once.

4.1) Single Heading

  • First, select the heading cell in the Pivot Table.
  • Type a new heading that is different from the field name in the source data
    • Tip: Add a space at the beginning or end of the field name, to make it slightly different from the original pivot field name
  • Press the Enter key, to complete the renaming.

4.2) Multiple Headings

This method removes the "Sum of" text, and leaves a space character at the start of the name.

  1. First, select all the captions that you want to change
  2. Press Ctrl + H to open the Replace Window
  3. In the Find What box, type "Sum of" (do not add a space at the end)
  4. Leave the Replace With box empty
  5. Click Replace All.

5) Macros to Remove "Sum Of" in Heading

The following macros will change the field names to:

  • the field's source name or source caption
  • with a space character added at the end.

You can copy the Excel VBA code from the sections below, or go to the Download section, and get the Excel sample files, which contain pivot tables for testing, and the macros from this page.

  • Note: There are macros for Normal pivot tables, Data Model (OLAP) pivot tables, and both types. Go to the Pivot Types section, for more details.

5.1) How to Use These Macros

First, before using these macros, make a backup copy of your workbook. Like other Excel macros, these macros cannot be undone.

Next, to use these macros, follow the steps below:

  1. First, copy the macro code to a regular code module in your Excel workbook.
  2. Next, on the Ribbon's View tab, click the top part of the Macro button, to open the Macro window
  3. In the list of macros, click on the macro that you want to run
  4. Click the Run button  Go to Top

6) - Normal Pivot Tables

Use the following macros for Normal pivot tables. There are 3 macros in this section:

  1. Selected Pivot Table - Normal
  2. All Pivot Table on Sheet - Normal
  3. All Pivot Tables in Workbook - Normal
  4. Change Captions - Normal

6.1) Selected Pivot Table - Normal

This macro removes the "Sum of" from Value field headings (captions) in the selected pivot table only.

  • Before you run the macro, select a cell in the pivot table that you want to change.
  • This macro is for Normal pivot tables only.  Go to Top
Sub ValueCaptionsSelPT_Normal()
'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_Normal pt

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

errHandler:
  Resume exitHandler

End Sub

6.2) All Pivot Tables on Sheet - Normal

This macro removes the "Sum of" from Value fields in all pivot tables on the active worksheet

  • This macro is for Normal pivot tables only.   Go to Top
Sub ValueCaptionsWs_Normal()
'remove "Sum of" etc.
'from all value field headings
'for OLAP and Normal pivot tables
'all PTs, active worksheet

Dim pt As PivotTable
Dim ws As Worksheet

On Error Resume Next
Set ws = ActiveSheet
On Error GoTo exitHandler

If ws.PivotTables.Count = 0 Then
  MsgBox "There are no pivot tables" _
    & vbCrLf & "on the active sheet"
End If
  
Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pt In ws.PivotTables
    ChangeHead_Normal pt
Next pt

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

errHandler:
  Resume exitHandler

End Sub

6.3) All Pivot Tables in Workbook - Normal

This macro removes the "Sum of" from Value fields in all pivot tables on all worksheets in the active workbook

  • This macro is for Normal pivot tables only.   Go to Top
Sub ValueCaptionsWbk_Normal()
'remove "Sum of" etc.
'from all value field headings
'for OLAP and Normal pivot tables
'all PTs, all sheets, active workbook

Dim pt As PivotTable
Dim ws As Worksheet
On Error GoTo exitHandler

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Sheets
  For Each pt In ws.PivotTables
      ChangeHead_Normal pt
  Next pt
Next ws

Application.ScreenUpdating = True
MsgBox "Done!"

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

errHandler:
  Resume exitHandler

End Sub

6.4) Change Captions - Normal

This macro is called by the ValueCaptions macros above. This macro can be stored on the same module as the other macros, or on a different module. This macro is for Normal pivot tables only

  • The code changes the pivot table to manual update
  • For each field in the Values area (datafields), the caption is changed to the field's source name, followed by a space character
  • The pivot table is refreshed
  • The pivot table has manual update turned off..   Go to Top
Sub ChangeHead_Normal _
  (ByRef myPT As PivotTable)
''Called in ValueCaptions macros
Dim pf As PivotField

myPT.ManualUpdate = True

For Each pf In myPT.DataFields
  pf.Caption = pf.SourceName & " "
Next pf

myPT.RefreshTable
myPT.ManualUpdate = False

Set pf = Nothing

End Sub

7) Macros - OLAP Pivot Tables (Data Model)

Use the following macros for OLAP pivot tables. There are 3 macros in this section:

  1. Selected Pivot Table - OLAP
  2. All Pivot Table on Sheet - OLAP
  3. All Pivot Tables in Workbook - OLAP
  4. Change Captions - OLAP

7.1) Selected Pivot Table - OLAP

This macro removes the "Sum of" from Value field headings (captions) in the selected pivot table only.

  • Before you run the macro, select a cell in the pivot table that you want to change.
  • This macro is for OLAP pivot tables only.  Go to Top
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

7.2) All Pivot Tables on Sheet - OLAP

This macro removes the "Sum of" from Value fields in all pivot tables on the active worksheet

  • This macro is for OLAP pivot tables only.   Go to Top
Sub ValueCaptionsWs_OLAP()
'remove "Sum of" etc.
'from all value field headings
'for OLAP and Normal pivot tables
'all PTs, active worksheet

Dim pt As PivotTable
Dim ws As Worksheet

On Error Resume Next
Set ws = ActiveSheet
On Error GoTo exitHandler

If ws.PivotTables.Count = 0 Then
  MsgBox "There are no pivot tables" _
    & vbCrLf & "on the active sheet"
End If
  
Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pt In ws.PivotTables
    ChangeHead_OLAP pt
Next pt

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

errHandler:
  Resume exitHandler

End Sub

7.3) All Pivot Tables in Workbook - OLAP

This macro removes the "Sum of" from Value fields in all pivot tables on all worksheets in the active workbook

  • This macro is for OLAP pivot tables only.   Go to Top
Sub ValueCaptionsWbk_OLAP()
'remove "Sum of" etc.
'from all value field headings
'for OLAP and Normal pivot tables
'all PTs, all sheets, active workbook

Dim pt As PivotTable
Dim ws As Worksheet
On Error GoTo exitHandler

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Sheets
  For Each pt In ws.PivotTables
      ChangeHead_OLAP pt
  Next pt
Next ws

Application.ScreenUpdating = True
MsgBox "Done!"

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

errHandler:
  Resume exitHandler

End Sub

7.4) Change Captions - OLAP

This macro is called by the ValueCaptions macros above. This macro can be stored on the same module as the other macros, or on a different module. This macro is for OLAP pivot tables only

  • The code changes the pivot table to manual update
  • For each field in the Values area (datafields), the caption is changed to the field's source name, followed by a space character
  • The pivot table is refreshed
  • The pivot table has manual update turned off.   Go to Top
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

8) Macros - Dual Pivot Types

Use the following macros for in workbooks where:

  • you have a mixture of Normal and OLAP pivot tables, or
  • you aren't sure which type of pivot tables are in the workbook.

There are 3 macros in this section:

  1. Selected Pivot Table - Dual
  2. All Pivot Table on Sheet - Dual
  3. All Pivot Tables in Workbook - Dual
  4. Change Captions - Dual

8.1) Selected Pivot Table - Dual

This macro removes the "Sum of" from Value field headings (captions) in the selected pivot table only.

  • Before you run the macro, select a cell in the pivot table that you want to change.
  • This macro is for both Normal and OLAP pivot tables.  Go to Top
Sub ValueCaptionsSelPT_Dual()
'remove "Sum of" etc.
'from all value field headings
'for OLAP and Normal pivot tables

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

If pt.PivotCache.OLAP Then
  ChangeHead_OLAP pt
Else
  ChangeHead_Normal pt
End If

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

errHandler:
  Resume exitHandler

End Sub

8.2) All Pivot Tables on Sheet - Dual

This macro removes the "Sum of" from Value fields in all pivot tables on the active worksheet

  • This is macro is for both Normal and OLAP pivot tables.   Go to Top
Sub ValueCaptionsWs_Dual()
'remove "Sum of" etc.
'from all value field headings
'for OLAP and Normal pivot tables
'all PTs, active worksheet

Dim pt As PivotTable
Dim ws As Worksheet

On Error Resume Next
Set ws = ActiveSheet
On Error GoTo exitHandler

If ws.PivotTables.Count = 0 Then
  MsgBox "There are no pivot tables" _
    & vbCrLf & "on the active sheet"
End If
  
Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pt In ws.PivotTables
  If pt.PivotCache.OLAP Then
    ChangeHead_OLAP pt
  Else
    ChangeHead_Normal pt
  End If
Next pt

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

errHandler:
  Resume exitHandler

End Sub

8.3) All Pivot Tables in Workbook - Dual

This macro removes the "Sum of" from Value fields in all pivot tables on all worksheets in the active workbook

  • This is macro is for both Normal and OLAP pivot tables.   Go to Top
Sub ValueCaptionsWbk_Dual()
'remove "Sum of" etc.
'from all value field headings
'for OLAP and Normal pivot tables
'all PTs, all sheets, active workbook

Dim pt As PivotTable
Dim ws As Worksheet
On Error GoTo exitHandler

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Sheets
  For Each pt In ws.PivotTables
    If pt.PivotCache.OLAP Then
      ChangeHead_OLAP pt
    Else
      ChangeHead_Normal pt
    End If
  Next pt
Next ws

Application.ScreenUpdating = True
MsgBox "Done!"

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

errHandler:
  Resume exitHandler
  
End Sub

8.4) Change Captions - Dual

The Dual macros (above) call both the change macros:

Those macros are shown in the previous sections, and both macros are contained in the Dual sample file.

asdasf

9) Pivot Table Types

There are three types of macros, in the sections above. Choose a macro that is designed for the pivot table that you want to change:

  1. Normal: If your workbook has normal pivot tables, use a macro from the Normal section. In those macros, the pivot field's SourceName property is used.
  2. OLAP-based: If your workbook has pivot tables that use the Data Model, use a macro from the OLAP section. In those macros, the pivot field's SourceCaption property is used.
    • add to data model
  3. Dual: If your workbook has a mixture of normal pivot tables and OLAP-based pivot tables, or if you aren't sure which type of pivot tables are in the workbook, use a macro from the Dual section. In those macros, the pivot table type is checked, and the appropriate property is used -- SourceName or SourceCaption.

9.1) Source Name Differences

The SourceName property looks different in normal pivot tables and OLAP-based pivot tables. That is why different macros are used, for each type of pivot table.

9.1.1) SourceName Property

  • In a normal pivot table, the pivot field's SourceName property shows the heading from the source data field column. It can be easily used as part of the new caption:
    • QtySold
  • The SourceName property for an OLAP value field has a different structure, with square brackets. It's not as easy to use this text in a new caption:
    • [Measures].[Sum of QtySold]

9.1.2) SourceCaption Property

  • The OLAP macros use SouceCaption to create the new caption for OLAP-based pivot tables. This property has a simpler structure, without the brackets. It's easier to extract the field name from this property, to use as a new caption:
    • Sum of QtySold
  • However, the SourceCaption property is available for OLAP-based pivot tables only. It cannot be used for Normal pivot tables.
  • NOTE: The OLAP macros will not work correctly if the Value field captions have already been changed to something different from the default "Sum of" style headings.

10) Download the Sample Files

  1. Normal: Download the Normal workbook, if your workbook only has normal pivot tables. The zipped file is in xlsm format, and contains the Normal macros.
  2. OLAP: Download the OLAP workbook, if your workbook only has OLAP pivot tables. The zipped file is in xlsm format, and contains the OLAP macros.
  3. Dual: Download the Dual workbook, if your workbook has both OLAP and normal pivot tables, or if you are not sure which type of pivot tables are in your workbook. The zipped file is in xlsm format, and contains the Dual macros.

More Pivot Table Tutorials

List all Pivot Fields with Details

Text Values in Pivot Table

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

 

 

 

Last updated: July 7, 2024 10:42 AM