When you add fields to the Values area, they are renamed. For example, 'Units' becomes 'Sum of Units'. Instead of using these default names, you can change the field names to something shorter, or more descriptive, such as Units Sold.

However, the new name can't be the same as the original field name. For example, if the original field name is Quantity, you can't change 'Sum of Quantity' to 'Quantity'.

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', as shown in this video.

Watch this video to see the steps for renaming a single value field heading, or multiple headings.

Visit my Contextures website to download the sample file.

[ Ссылка ]

Video Timeline
0:00 Introduction
0:25 Change the heading
0:38 Error message
0:49 View Field Settings
1:11 Change the Custom Name
1:29 Change Quantity heading
1:46 Make columns narrower
2:07 Change multiple headings
2:44 Download the sample file

Video Transcript
When you add fields to the values area of a pivot table, it automatically puts either Sum of, or Count of, in front of the field name, and that can make those columns a little too wide.

You can change those headings and get rid of that Sum of, or Count of. There's a little trick you have to do though, so that the pivot table will allow you to make the change.

I'll try and change this heading;

• I'll click on that cell

• and in the formula bar, I'll select Sum of,

• and delete everything that's in front of that total price.

When I press enter, Excel shows a message that warns me, PivotTable field name already exists.

• So I'll click, OK

• Then I'll press escape to get out of that cell.

If I take a look at this field, I'll right-click and go to Value Field Settings

We'll see that there is a source name, which is the field name in the source data. And then a custom name, which is Sum of, and then the field name.

The custom name, can't be the same as any source name in the pivot table.

So instead of trying to delete everything, including that space, I'm just going to delete Sum of.

That leaves a space in front of total price.

And when I click OK, now it accepts that name because it's not exactly the same. It's got a space in front of it.

I could do the same thing over here.

I'll select Sum of, but you can see the space is still there.

When I press delete and enter it accepts that name, because again, it's not exactly the same as the source name.

So with that little trick, you can have narrower columns.

The pivot headings look the same as the field names, but with that space at the front, they're different enough that Excel will allow them.

Now I changed each of those individually, but I'm going to take them out of the pivot table and then put them back in. And they get those long names again.

If you want to change several at once:

• you can select them

• then press Ctrl + H to open the find and replace window

• We want to replace Sum of, but don't type a space. We just want to replace the words and leave that leading space.

• We want to replace that with nothing.

• So I'll click Replace All, OK, and Close

and it fixes all the headings at once.

Then you can
• select the columns

• double-click to resize them

and your pivot tables should be much narrower.

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com

свернуть