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.

PivotDataNames01

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.”

rename a pivot table data field

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.

PivotDataNames03

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.

___________________________

8 thoughts on “Rename a Pivot Table Data Field”

  1. 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!

    Thanks!!

    1. Hi,

      Thanks for the tip. Would you mind showing me the formula to update multiple pivot at once as I tried and it didn’t work.

      That’d be a real time saver!

      Thanks!
      Charly

    1. @Jesper, you could type over the captions, to show the original names again, e.g. “Sum of Units”
      Or, remove a field from the values area, then put it back again.
      To do it with a macro, you’d have to get the source name for each field, and the function, and combine those as a new caption.

  2. I found the line

    pf.Caption = pf.Caption & ” ”

    to be quite slow (in Excel 2016).

    Is this expected?

    Thanks,

    Anil

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.