|
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.
|
Rename Value Field Headings
When you add fields to the Values area, the headings automatically have "Sum of" or "Count of" added to the field name.
- To manually remove the "Sum of" or "Count of" from one or more of the pivot table value headings, go to Pivot Table Layout page for detailed steps and a short video.
- Or, use one of the macros on this page, to quickly remove that text from multiple headings.
Value Heading Captions
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.
No Original Field Names
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.
No Duplicate Names
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
Pivot Table Types
These macros are designed for two different types of pivot tables:
- Normal Pivot Tables - The original type of pivot table, with source data that has NOT been added to the workbook's Data Model.
- OLAP-Based Pivot Tables: When you create a pivot table, if you check the box to "Add this data to the Data Model", an OLAP-based pivot table is created.
Macro Types
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:
- 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.
- 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.
- 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. Go to Top
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.
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]
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.
How to Use These Macros
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:
- Copy the macro code to a regular code module in your workbook.
- Then, on the Ribbon's View tab, click the top part of the Macro button, to open the Macro window
- In the list of macros, click on the macro that you want to run
- Click the Run button Go to Top
Macros - Normal
Use the following macros for Normal pivot tables. There are 3 macros in this section:
- Selected Pivot Table - Normal
- All Pivot Table on Sheet - Normal
- All Pivot Tables in Workbook - Normal
- Change Captions - Normal
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
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
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
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
Macros - OLAP
Use the following macros for OLAP pivot tables. There are 3 macros in this section:
- Selected Pivot Table - OLAP
- All Pivot Table on Sheet - OLAP
- All Pivot Tables in Workbook - OLAP
- Change Captions - OLAP
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
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
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
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
Macros - Dual
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:
- Selected Pivot Table - Dual
- All Pivot Table on Sheet - Dual
- All Pivot Tables in Workbook - Dual
- Change Captions - Dual
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
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
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
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.
Download the Sample Files
- 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.
- 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.
- 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
Last updated: February 22, 2023 7:29 PM