Contextures

Pivot Table Value Heading Macros

Use 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. To change the 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 remove the "Sum of" or "Count of" from a single value heading, see the instructions here, to manually change the heading.
  • To quickly remove that text from multiple headings, use one of the macros on this page.

rename pivot fields

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.
  • add to data model

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 backets. 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:

  1. Copy the macro code to a regular code module in your workbook.
  2. Then, 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

Macros - Normal

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

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

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

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

  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 Sual macros.

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

  • copy the formatting from one pivot table, and apply it to another pivot table.
  • change all the values from Count to Sum
  • remove the "Sum of" from all the headings

and much more!

More Pivot Table Tutorials

List all Pivot Fields with Details

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

 

DVMSP

 

 

Free pivot table tools

 

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: July 17, 2018 4:32 PM