Advanced Pivot Table Tips and Tricks - Part 2

Locked lesson.

Upgrade

Quick reference

Advanced Pivot Table Tips and Tricks - Part 2

Learn continue to look at some advanced PivotTable Tips and Tricks. 

When to use

We use the advanced features of PivotTables whenever we want to supercharge our analysis. 

Instructions

Calculated Fields

Calculated Fields allow us to perform calculations in the PivotTable using existing PivotTable fields. 

  • Remove the Sales Channel field from the Columns area. 
  • Add the Total Revenue field to the Values area and format the numbers. 

In this example, we are going to create a calculated field that calculates total revenue when a 20% sales tax has been added. 

  • Click anywhere in the 'Sum of Total Revenue' column. 
  • From the PivotTable Analyze tab, in the Calculations group, click Fields, Items & Sets. 

We can use the fields at the bottom to construct a calculation. 

  • Click in the name field. 
  • Name the field 'Rev + Tax'. 
  • Click in the Formula field. 
  • From the Fields area, double-click on Total Revenue. 
  • Type *20%

This calculation will give us the total tax amount only. If we want to see the total revenue including the tax we need to add the total revenue on. Remember to include brackets so Excel knows what to calculate first. 

We now have a new calculated column. 

Add a Slicer

Slicers are visual filters. They provide an easy way for users to filter PivotTable data. 

  • Click anywhere in the dataset. 
  • From the PivotTable Analyze tab, in the Filter group, click Insert Slicer. 
  • Select 'Region' and 'Order Priority' from the list. 
  • Click OK. 

Format the Slicers

We can apply formatting to the slicers. 

Change the slicer layout

By default, slicers list items vertically. We can change this to horizontal. 

  • Click on the slicer. 
  • From the Slicer tab, in the Buttons group, increase the number of columns to 7. 
  • Resize the slicer. 

  • Repeat this process for the other slicer. 
  • Place both slicers at the top of the worksheet above the PivotTable. 

Work with Pivot Charts

We can visualize our PivotTable data using a Pivot Chart. 

  • Remove all PivotTable fields except 'Country' and 'Sum of Total Cost' 

Pivot Charts look best when they are showing a refined, smaller dataset. Currently, we have too much data to display. 

Apply a Top 10 Filter
  • Click in the PivotTable. 
  • Right-click and select Filter. 
  • Choose Top 10 from the list. 
  • Show the Top 5 Items. 
  • Click OK. 
Insert a Pivot Chart
  • Click in the PivotTable. 
  • From the PivotTable Analyze tab, in the Tools group, click Pivot Chart. 
  • Choose Column from the list. 
  • Select the Clustered Column Chart. 

Update Pivot Data

The beauty of PivotTables and Pivot Charts is that they are easy to update when new data is added to the source. 

  • Go to the 'New_Data' worksheet. 
  • Press CTRL+A to copy the data. 
  • Go to the 'Source' worksheet. 
  • Press CTRL+down arrow to jump to the bottom. 
  • Press CTRL+V to paste the data. 

The Excel table will auto-expand to accommodate the new data. We can now update our PivotTable and Pivot Chart with one click. 

  • Click on the PivotTable or the Pivot Chart. 
  • From the PivotTable Analyze tab, in the Data group, click Refresh and Refresh All. 
Login to download
  • 00:04 The next step is creating calculated fields.
  • 00:07 Now for this, I'm actually going to remove the sales channel so
  • 00:12 that we just have the sum of the total cost.
  • 00:15 Now what I'm also going to do here is I'm going to add the total revenue
  • 00:20 into values as well.
  • 00:21 And let's just very quickly apply some number formatting so
  • 00:25 that it matches everything else.
  • 00:27 Now maybe there's a 20% sales tax that needs to be added onto the top.
  • 00:32 Now we don't have that information anywhere in our source data.
  • 00:36 But not to worry, we can use a calculated field which will effectively execute
  • 00:41 a formula, which will allow us to work out what 20% is of the total revenue for
  • 00:47 example, and add that information into our pivot table.
  • 00:51 So all we need to do here is, I'm going to click in sum of total revenue.
  • 00:54 I'm going to go up to PivotTable Analyze and
  • 00:58 we're going to choose Calculated Field.
  • 01:00 Now with Calculated Fields you can see all of the fields listed below, and
  • 01:05 we can use these to construct a formula.
  • 01:07 Now we need to give our calculated field a name, so
  • 01:11 I'm going to call this Rev plus Tax.
  • 01:13 Now our formula is going to be the total revenue multiplied by 20%.
  • 01:21 That's going to give us the tax amount.
  • 01:23 If we want to have the tax amount plus the revenue,
  • 01:25 we need to do something else in here.
  • 01:27 So this is much like if we were doing a sum calculation, we need to put this first
  • 01:33 part in brackets and then we need to plus the total revenue, click on OK.
  • 01:38 And now we get a brand new column that's showing us that calculation.
  • 01:43 My next tip is making this data really easy for
  • 01:46 people to filter by adding a slicer.
  • 01:48 So once again, I'm going to click on my data up to PivotTtable Analyze and
  • 01:53 we can insert a slicer.
  • 01:54 Now slicers are just visual filters and
  • 01:56 we can choose any of the fields that we have in our pivot table.
  • 02:00 So maybe I want to add a slicer so that I can filter by region and
  • 02:05 also, let's say, Order Priority, and click on OK.
  • 02:10 So now I get these two little slicers, I can place them wherever I like and
  • 02:14 I can apply different formatting to them.
  • 02:16 For example, maybe I want to take these columns up so they're all on one row, and
  • 02:21 I can make this more of a horizontal slicer on my spreadsheet.
  • 02:25 Now a lot of the time I will leave some room at the top for things like slicers,
  • 02:29 so maybe I want to place this up here, remember you can drag it out a little bit.
  • 02:33 And I could do the same with the order priorities.
  • 02:36 So let's put these on one line as well,
  • 02:39 drag it up, and then we can place this just here.
  • 02:43 Now, obviously you can fiddle around with this a little bit more than I am, and you
  • 02:47 can even make these look really cool by creating your own slicer custom formats.
  • 02:51 Now remember, when you click on them, you get a slicer ribbon,
  • 02:54 I'm going to change these to green so that they match my data.
  • 02:57 And now, we can simply use these to filter our data in a really nice visual way.
  • 03:03 Now another thing we might want to do here, and we are going to
  • 03:06 focus a lot on this in the next lesson, so I'm not going to linger too much,
  • 03:10 is insert a pivot chart based on our pivot data.
  • 03:12 Now I'm going to remove some of these fields because as you'll see,
  • 03:17 pivot charts work best with smaller datasets.
  • 03:20 So I'm going to take this all the way down, so
  • 03:22 that we're just showing the country and the total costs.
  • 03:25 And I'm also going to apply a filter, only show the top five items.
  • 03:31 So now we have a much smaller data set.
  • 03:34 I'm going to jump up to PivotTable Analyze and
  • 03:37 just very quickly insert a clustered column chart.
  • 03:40 So this is a very basic chart.
  • 03:42 Now the reason why I'm inserting this at this stage, as I said,
  • 03:46 we will look at this in more detail in the next section,
  • 03:48 is just to show you what happens when you update your pivot table data.
  • 03:52 So if we take a look at the bottom here, I have a tab called New_Data, so
  • 03:57 maybe this is the sales information for the next month.
  • 04:01 So what I want to do here is I'm going to select all, Ctrl A, Ctrl C to copy,
  • 04:06 and we're going to paste this onto the bottom of our source data.
  • 04:10 So let's click in cell, Ctrl V.
  • 04:13 And notice that because this data is in a table,
  • 04:16 it automatically expands to accommodate that new data.
  • 04:20 So it means that all we need to do to update our pivot tables and
  • 04:24 pivot charts to include this new information is simply click refresh.
  • 04:29 So let's click in the pivot table, go to PivotTable Analyze, and
  • 04:33 click the refresh button.
  • 04:35 And you should see that those numbers update to include that new data,
  • 04:40 as does the pivot chart.

Lesson notes are only available for subscribers.

Gift this course