Macro 67: Adjust All Pivot Data Field Titles
When you create a PivotTable, Excel tries to help you out by prefacing each data field header with Sum
of, Count of, or whichever operation you use. Often, this is not conducive to your reporting needs.
You want clean titles that match your data source as closely as possible. Although it’s true that you can
manually adjust the titles for you data fields (one at a time), this macro fixes them all in one go.
How it works
Ideally, the name of the each data item matches the field name from your source data set (the origi-
nal source data used to create the PivotTable). Unfortunately, PivotTables won’t allow you to name a
data field the exact name as the source data field. The workaround for this is to add a space to the
end of the field name. Excel considers the field name (with a space) to be different from the source
data field name, so it allows it. Cosmetically, the readers of your spreadsheet don’t notice the space
after the name.
This macro utilizes this workaround to rename your data fields. It loops through each data field in the
PivotTable, and then resets each header to match its respective field in the source data plus a space
character.
Sub Macro67()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the active cell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a
PivotTable.” Exit Sub
End If
‘Step 4: Loop through all pivot fields adjust titles
For Each pf In pt.DataFields
pf.Caption = pf.SourceName & Chr(160)
Next pf
End Sub
1. Step 1 declares two object variables. It uses pt as the memory container for our PivotTable
and pf as a memory container for the data fields. This allows the macro to loop through all
the data fields in the PivotTable.
2. This macro is designed so that we infer the active PivotTable based on the active cell. In
other words, the active cell must be inside a PivotTable for this macro to run. We assume that
when the cursor is inside a particular PivotTable, we want to perform the macro action on
that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found.
We do this by using the ActiveCell.PivotTable.Name property to get the name of
the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why we use the On
Error Resume Next statement. This tells Excel to continue with the macro if there is an
error.
3. In Step 3, we check to see if the pt variable is filled with a PivotTable object. If the pt vari-
able is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be
assigned to the variable. If this is the case, we tell the user in a message box, and then we exit
the procedure.
4. If the macro reaches Step 4, it has successfully pointed to a PivotTable. The macro uses a For
Each statement to iterate through each data field. Each time a new pivot field is selected,
the macro changes the field name by setting the Caption property to match the field’s
SourceName. The SourceName property returns the name of the matching field in the
original source data.
To that name, the macro concatenates a non-breaking space character: Chr(160).
Every character has an underlying ASCII code, similar to a serial number. For instance, the
lowercase letter a has an ASCII code of 97. The lowercase letter c has an ASCII code of 99.
Likewise, invisible characters such as the space have a code. You can use invisible characters
in your macro by passing their code through the CHR function.
After the name has been changed, the macro moves to the next data field. After all the data
fields have been evaluated, the macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
