Renaming a 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.

PivotSumUnits

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

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”
  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 than the original field name, and will be accepted.

PivotSumOfReplace

____________________

You may also like...

5 Responses

  1. iesmatauw says:

    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. No, unfortunately you can’t change the default headings before they’re created.

  3. derek says:

    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?

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

  5. derek says:

    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 to iesmatauw Cancel reply

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