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. The 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 it to its desired position. This is how nesting occurs. Changing the order of field names affects the appearance dramatically. Examine it by yourself.
  • To temporarily remove a field from the pivot table, you can remove the checkmark 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 checkmark back on the field name, and it will appear in its previous section and the 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 by choosing whatever items you like. Our pivot table shows 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 🙂

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo