Remove Sum Of in Pivot Table Headings

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 http://www.pivot-table.com/

 

This entry was posted in Layout. Bookmark the permalink.

14 Responses to Remove Sum Of in Pivot Table Headings

  1. Trouttrap2 says:

    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. Pingback: Excel Roundup 20140825 « Contextures Blog

  3. Neil Reynolds says:

    Great tip! I knew you could rename pivot headings but had not considered using the space. Thanks again.

  4. Vrunda says:

    Very very useful tip . Thanks for d same

  5. Tom B says:

    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!

  6. Matt says:

    Awesome! Just solved my problem!

  7. donna says:

    Perfect – problem solved! thanks so much

  8. Cherie says:

    I have used this as well. But how do you then eliminate the duplicate listing in the field list?

  9. christina says:

    Really helped..Thanks!

  10. maria says:

    the change multiple headings doesnt work

  11. Gábor says:

    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… 🙂

  12. Marilena Petrec says:

    Thank you. Very useful.

Leave a Reply

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