Remove Sum Of in Pivot Table Headings

Remove "Sum Of" in #Excel pivot table headings; avoid error msg https://www.pivot-table.com/

When you add fields to the Values area, they are set as either Sum or Count, and the field is automatically renamed. For example, Quantity becomes “Sum of Quantity”, or “Count of Quantity”. These long field names  can make the columns wider than necessary, but if you try to change those names, Excel might show an error message — “PivotTable field name already exists.”

Change the Pivot Table Headings

Instead of leaving those long default names in your pivot table, you can change the field headings. Watch this short video, to see the steps, and there are written instructions below the video.

Name Restrictions

When you change the field name in a pivot table, the new name can’t be the same as the original field name. For example, if the original field name is Quantity, you’ll see an error message if you try to change the heading from ‘Sum of Quantity’ to ‘Quantity’.

valueheadingrename03

Avoid the Error Message

To avoid an error message, you can type the original field name, and add a space character at the end, e.g. ‘Quantity ‘ or at the beginning — ‘ Quantity’. The new heading will look like the original field name, but the space character will make it different, so Excel will allow the name.

valueheadingrename01

In the screen shot below, I have highlighted the space character at the start of the Quantity name.

valueheadingrename04

Change Multiple Headings

If you have several headings to change, you can select all the headings, then use the Find and Replace window, to change all of them at once. Or, if you would prefer to use a macro to change the headings, there are sample macros on my website.

To use Find and Replace:

  1. Select all the captions that you want to change
  2. Press Ctrl + H to open the Find and Replace Window
  3. In the Find What box, type “Sum of” (do not add a space at the end)
  4. Leave the Replace With box empty
  5. Click Replace All, to change all the headings.

Pivot Table Tools

If you have a copy of my Pivot Power Premium add-in, it has a command to change the headings for all the value fields in a pivot table, or just the selected fields.

pivot power premium value headings

_____________________________

Remove "Sum Of" in #Excel pivot table headings; avoid error msg https://www.pivot-table.com/

 

17 thoughts on “Remove Sum Of in Pivot Table Headings”

  1. In hind sight, it seems obvious to put a space before or after ‘Quantity’ to retain to name, but I never thought of that before. thank you for your post.

  2. I spent over an hour trying to figure out why I couldn’t get rid of “Sum of.” Every other search was frustrating because it didn’t address the issue. This was simple and effective–perfect, in other words.
    Thank you!

  3. This is an awesome trick! 🙂
    I was very upset because the functionality of Excel. I wonder, when will be this a pure checkbox in the software… 🙂

  4. I found that if you just go up to the formula bar and add a space, it works the same. But never would have figured that out unless of this video. Thanks.

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.