Rename a Pivot Table Data Field

When you add a field to the pivot table Data area, it automatically gets a custom name, such as Sum of Units or Count of Units. You can rename a pivot table data field, either manually or with a macro.

Instead of “Sum of Units”, you might want the name to show as “Units”, so it’s easier to read and the column is narrower.

Unfortunately, if you select the cell and type Units, you’ll see an error message: “PivotTable field name already exists.”

When you try to use a custom name that’s identical to a field name in the source data, you’ll see that error message. In this example, one of the fields in the source data is named Units, so you can’t use Units as a custom name in the pivot table.

Use a Slightly Different Custom Name

To avoid this problem, you can add a space character to the end of the custom name, and it will be accepted.

In the screen shot below, I’ve added a space after typing Units. When I press the Enter key, the name will be accepted, without an error message.

Use a Macro to Rename a Pivot Table Data Field

If you have lots of Data field names to change you could use a macro, to make the job easier. For example, the following macro will change all the Data field captions in the first pivot table on the active sheet.

Sub ChangeCaptions()
Dim pf As PivotField
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.DataFields
    pf.Caption = pf.SourceName & " "
Next pf
End Sub

There are instructions here for adding code to your workbook, and running it. This code would go onto a regular module.

There are more code samples on my Contextures website, for changing headings in:

• all pivot tables on the active worksheet
• all pivot tables in the active workbook.

___________________________