How to Modify an Excel Pivot Table

After you have created your pivot table, it is easy to change it. For example, you can add more summary information by using the PivotTable Fields task pane. Say, we have the following pivot table we have built in using our Bank-accounts.xlsx file. Download Bank-accounts.xlsx.

The following figure shows a pivot table, we have made using the above excel file.

Modifying the pivot table.

A pivot table created with Bank-accounts.xlsx file.

We want to add a new field and remove an existing field from this pivot table. It is really simple. Say we want to add the OpenedBy field to the Rows section in the PivotTable Fields task pane. At the same time, we want to remove the Branch field from the Rows area in the task pane. The following figure shows the previous pivot table after I have dragged the OpenedBy field in the Rows section and removed the Branch field from the Rows area.

Modifying the pivot tables.

Previous Pivot Table after adding OpenedBy field and removing Branch field.

Here are some tips to modify your pivot tables in different ways.

  • You can remove a field from the pivot table in two ways. First one is: select the field in the bottom part of the PivotTable Fields task pane and then drag it away. Another one is: click on the field in the bottom part of the PivotTable Fields task pane, a shortcut menu bar will appear. Choose Remove Field from the options.
  • If an area in the bottom part of the PivotTable Fields task pane has more than one field, you can change the order of the fields. To change the order, you have to click the field name, hold your mouse and drag to its desired position. This is how nesting occurs. Changing the order of field names affect the appearance dramatically. Examine it by yourself.
  • To temporarily remove a field from the pivot table, you can remove the check mark from the field name in the top part of the PivotTable Fields task pane. The pivot table is displayed now without that field. Place the check mark back on the field name, and it will appear in its previous section and pivot table will get its previous position.
  • When you add a field to the Filters section, the field items appear in a drop-down list. Clicking on this drop-down list, you can display data choosing whatever items you like. Our pivot tale shows a filtering according to OpenedBy. When you will click this drop-down list, you will find All, New Accts and Teller-these three options. If you want to see the accounts opened by Teller, choose Teller. Our following image shows the pivot table when we have selected Teller from the options.
Modifying the pivot tables

This is the pivot table filtered by the teller. You can change it to New accts or all.

Download Sample File

Bank-accounts4.xlsx

Happy Excelling 🙂


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply