Adding Multiple Parameters to our Pivot Table

In this lesson we want to create a new pivot table, but one that uses the same data as the Pivot Table from the previous lesson. To save time from creating a brand new Pivot Table from the same dataset, we can simply copy and paste our original Pivot Table.

Copy and Pasting a Pivot Table

To copy and paste a Pivot table, simply highlight the entire original Pivot Table and press Control + C to copy it. Find a new blank area with room for the original Pivot Table and then press Control + V to paste the new Pivot Table in place. Make sure when highlighting that any Filter fields above the Pivot Table are also highlighted.

Filtering the Information in a Pivot Table

Next we then look at how to change the amount of information being fed into the Pivot Table using whichever field is in the Filter quadrant of the Field List. Our original Pivot Table was filtering the dataset so that only rows that had 2016 in the ‘Year’ column were included. It’s easy enough to change this though if we want to bring in rows from other years as well. We simply need to go to the Filter cell, two cells above the left most Pivot Table cell, select the drop down menu and change the filters.

Removing Fields From Pivot Tables

After changing our filters, we look at removing fields from Pivot Tables. There’s a number of different ways to do this, and you can do as we do in the video. Simply left click on the field in the quadrant that you want to remove and then select ‘Remove Field’.

Alternatively, reverse the process of putting a field into a quadrant! You can go to the field that you would like to remove, left click on the field and hold it and drag the field back to the Field List.

Hierarchies

In the last lesson we only added a single field to each quadrant, but you can add more than one field to each quadrant. By doing so you create hierarchies of fields if you drag your fields to the Rows or Columns quadrant. So first off, how do you put more than one field in a quadrant?

Simple. Go to the Field List and identify the field that you want. Click on the field and then drag and drop it into either the Rows or Coluns quadrant where there is already another field.

You can choose to put your new field above or below the existing field within that quadrant. Where you choose to put it will then affect the layout of the Pivot Table.

So how does it affect the layout?

Let’s imagine that we have put two fields in the Rows quadrant, ‘Product’ and ‘Partner’, with ‘Partner’ below ‘Product’. We also have ‘Net Sales’ in the Values quadrant.

The Pivot Table calculates the sum of Net Sales, organising the calculations first by the Product field as that is the higher field in the Rows quadrant, and then subsequently by ‘Partner’. Think of it this way – with this setup, the Pivot table will show the Net Sales the total Net Sales by Product and then how each Partner performed for that Product. If the ‘Partner’ field were above ‘Product’ then the Pivot Table would show the Net Sales by Partner and then how each Partner had done with each Product.