Home > Pivot > Macros > Headings Pivot Table Value Heading MacrosUse these pivot table value heading macros, to quickly remove "Sum of", "Count of", or other function prefixes, from all the fields in the Values area of a pivot table. Tip: To change the pivot table headings manually, see the instructions here. |
When you add fields to the Values area, the headings automatically have "Sum of" or "Count of" added to the field name.
The macros on this page change the Value Heading (caption) to the field's source name or source caption, with a space character added at the end.
A space character is added because the value heading can't be the same as the original field name.
So, if the original field name is Quantity, you can't change 'Sum of Quantity' to 'Quantity'. However, Excel will allow the name with a space character at the end, e.g. 'Quantity ' or any other variation that is different from the original field name.
Also, a value heading cannot be the same as another heading. So, if you have a Sum of Quantity field, and a Count of Quantity field, the macros cannot change them both to "Quantity ". The first heading for that field will be changed, and any others for the same field will be left as is.
If you have duplicate fields in the Value area, use the macro that numbers duplicate headings. Go to Top
These macros are designed for two different types of pivot tables:
There are three types of macros, in the sections below. 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.
NOTE: Before using these macros, make a backup copy of your workbook first. Like other macros, these macros cannot be undone.
To use these macros, follow these steps:
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 you have a mixture of Normal and OLAP pivot tables, or if you aren't sure which type of pivot tables are in the workbook. 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.
Last updated: February 22, 2023 7:29 PM