Rename Excel Pivot Table Value Field

When you add a field to the pivot table Values area, it’s automatically given a custom name, such as Sum of Units.

Default pivot table value field names
Default pivot table value field names

Use a Shorter Name

You might want to change the custom name to Units, so it’s easier to read and makes the column narrower.

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

Use a Slightly Different Name

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

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

PivotUnitsChange

Use Replace All

Or, if you have multiple value fields in the pivot table, select all the captions, and use the Replace All command to fix all the captions at once.

  1. In the Find What box, type “Sum of” (do NOTadd a space at the end)
  2. Leave the Replace With box empty
  3. Click Replace All.

This will leave a space at the start of the caption, so it’s different from the original field name, and will be accepted.

Find and Replace dialog box
Find and Replace dialog box

____________________

0 thoughts on “Rename Excel Pivot Table Value Field”

  1. are there any options to change the default text “sum of”?

    eg: everytime we place Unit fields to the value area it will become “Unit_”

  2. How do I select just the data area of a pivot table for formatting? I can select other elements, but when I select “Count of X”, it selects the entire table instead of just the data area. When I click on the “Select” menu, all options are grayed out except for “Entire Table”.

    Are there any add-ins that make pivot table formatting easier?

  3. Derek, click Select>Entire Table, then click Select>Data.

    To format, you can use the Format>AutoFormat command. I don’t know of any add-ins that do other formatting.

  4. Debra, thanks! That works, and my format design is now stable against refreshes. But what a strange system: why should I have to select before I can select?

    I’ve never been impressed with most of the Autoformat designs. I can make better formats manually and, with proper selection of the different areas and fields, the formats are persistent across refresh without the need for macros. But it’s a weary process, and something like PivotTable Helper for formatting would be a nice labor-saver.

Leave a Reply

Your email address will not be published.

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