Learn how to edit a Pivot Table
Editing a Pivot Table
Rarely is a Pivot Table considered finished or complete after it’s first created. Over time, people realize they need to add a field, change the order of stacked, or tiered fields, or maybe remove a field that’s not providing useful information. Or maybe you want to change the function that’s applied to a numeric field, say from a SUM to an AVERAGE. You may also want to know that when you open your Pivot Table, you’re seeing all of your latest records included, whether you’ve added, removed, or changed the data within the list to which your Pivot Table refers.
In any case, whatever it is you want to edit, Excel makes it easy.
First, to make sure you’ve got all of your latest data, just go to the Pivot Table Analyze tab once you’ve got your Pivot Table open, and click the Refresh button. That goes back to the data range your Pivot Table refers to, and grabs the latest and greatest version of that data.
After that, editing your Pivot Table is just a matter of making sure the Pivot Table Fields panel is showing – it should appear as soon as you click within the Pivot Table itself. If it doesn’t appear, click the Field List button on the Pivot Table Analyze tab.
If you don’t see the Pivot Table Analyze tab, make sure you’re actually in the Pivot Table itself – you can’t just be on any cell in the worksheet that contains the Pivot Table, you have to be within the Pivot Table. See the difference here when I click near and then within the Pivot Table.
Once you’re safely inside your Pivot Table, all of the tools on the Pivot Table Analyze tab are at your disposal. You can use the Field List panel to add or remove fields from the Pivot Table, choose which sections of the Pivot Table they appear in, and change the order fields appear hierarchically within the Pivot Table.
You can also change where your Pivot Table lives, using the Move Pivot Table button, which gives you a dialog box from which you can choose a new location – in another worksheet or create a new worksheet just for your Pivot Table.
Parts of your Pivot Table can also be renamed – especially useful if your field names are abbreviations or just aren’t that clear. For example, I can change Category to Food Type – so people know immediately what to expect when they click that drop list in the Filter section.
The headings Row Labels and Sum of Total Sales (which is just the name of the function you chose for your Values box field, plus that field name) can also be edited – do so directly in the cells that contain them in the Pivot Table.
Bear in mind, however, that if you change the labels/headings, they will revert back to their defaults – derived from the source data – if you make any further changes. So put this sort of editing off until you’re 99% sure that the Pivot Table is in its final form.