|
Home > Pivot > Layout > Headings Remove Pivot Table "Sum Of" HeadingsHow 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! |

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.”

If you want to delete "Sum Of" from those long default names in your pivot table, you can change the field headings.
Written instructions are below the video, as well as the full video transcript.
Video Timeline:
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
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
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.
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:
If you use an existing pivot field name, Excel will show the following error:

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:
In this example, instead of 'Quantity', you could change the name to:
On the worksheet,

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.
This method removes the "Sum of" text, and leaves a space character at the start of the name.
The following macros will change the field names to:
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.
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:

Use the following macros for Normal pivot tables. There are 3 macros in this section:
This macro removes the "Sum of" from Value field headings (captions) in the selected pivot table only.
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
This macro removes the "Sum of" from Value fields in all pivot tables on the active worksheet
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
This macro removes the "Sum of" from Value fields in all pivot tables on all worksheets in the active workbook
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
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
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
Use the following macros for OLAP pivot tables. There are 3 macros in this section:
This macro removes the "Sum of" from Value field headings (captions) in the selected pivot table only.
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
This macro removes the "Sum of" from Value fields in all pivot tables on the active worksheet
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
This macro removes the "Sum of" from Value fields in all pivot tables on all worksheets in the active workbook
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
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
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
Use the following macros for in workbooks where:
There are 3 macros in this section:
This macro removes the "Sum of" from Value field headings (captions) in the selected pivot table only.
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
This macro removes the "Sum of" from Value fields in all pivot tables on the active worksheet
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
This macro removes the "Sum of" from Value fields in all pivot tables on all worksheets in the active workbook
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
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
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:

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.
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Last updated: December 27, 2024 11:40 AM