2. Creating More Complex Pivot Tables

Formtting your Pivot Table

Pivot Charts

In the previous lesson, we covered how to create our first basic pivot table. We were introduced to the Field List, the four quadrants of the Field list, being Columns, Rows, Filters and Values as well as actually making a pivot table. However we only added a single field to each of the four quadrants.

In the last lesson we only added a single field to each of the four quadrants; Columns, Rows, Filters and Values. In this lesson we start looking at making more complex Pivot Tables.

With a Pivot Table you can add multiple fields or parameters to each quadrant creating hierarchies in the pivot table and we look at how to do that in this lesson.

We also look at the positioning of the field in each quadrant and how that affects the layout of the Pivot Table.

Play Video

- 01:05 How can I copy an existing pivot table?
- 01:46- How can I change the field in the Filter quadrant to show different info?
- 02:40 - How can I remove a field from a quadrant?
- 03:22 - How can I easily reverse changes made to pivot tables?
- 03:35 - How can I add multiple parameters into a quadrant?
- 04:13 - How does the positioning of a field in a quadrant affect the layout of a pivot table?
- 05:28 - What are field hierarchies?

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.

*Adding fields to the Filters or Values Quadrant – *If you add a field to the Rows or Columns quadrants then you create hield hierarchies. If you add additional fields into the Values quadrant, then the Pivot Table will summarise that additional field as well for whichever field is in the Rows or Columns areas. If you add additional fields into the Filters quadrant, then you’ll add another filter option above the Pivot table and simply have another way of filtering the data to which the Pivot Table has access.