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.
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.
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.
Download Sample File
Happy Excelling 🙂