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.


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 Fix Names

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.


For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.


Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Formatting. Bookmark the permalink.

One Response to Rename a Pivot Table Data Field

  1. GCM says:

    So simple! Yet not at all obvious. Tried for hours to figure it out. Decided to Bing it, and < a minute later, had the answer.

    To update multiple pivottables on one sheet in the same macro, just Dim a pt variable and nest the given For Each in this solution inside another one for the pt variable.
    Changed 9 pts with 50+ fields each in about 15 seconds!


Leave a Reply

Your email address will not be published. Required fields are marked *