Locked lesson.
About this lesson
When you need to display values that are a percentage of difference from another value in a Pivot Table. This is very useful for showing growth/decline from previous periods.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Percentage Difference From Calculations.xlsx197.1 KB Percentage Difference From Calculations - Completed.xlsx
198.5 KB
Quick reference
Percentage Difference From Calculations
Calculating percentage differences from other fields in PivotTables.
Where/when to use the technique
When you need to display values that are a percentage of difference from another value in a Pivot. This is very useful for showing growth/decline from previous periods.
Instructions
Accessing % difference from calculations
- Locate your PivotTable on the worksheet (not the field well)
- Right-click a cell in the PivotTable column/row you wish to add a difference calculation to
- Either:
- Choose Value Field Settings > Show Values As > % Difference From
- Choose Show Values As > % Difference From
% Difference from in columns
- For the Base Field, choose the name of a field you put in the rows area of your PivotTable
- For the Base Item:
- To calculate the difference from the previous item, choose (previous)
- To calculate the difference from the next item, choose (next)
- To calculate the difference from a specific item, choose the item
% Difference from in rows
- For the Base Field, choose the name of a field you put in the column area of your PivotTable
- For the Base Item:
- To calculate the difference from the previous item, choose (previous)
- To calculate the difference from the next item, choose (next)
- To calculate the difference from a specific item, choose the item
Hiding error values
- Calculating differences from cells with no values may yield a #NULL error for a subtotal
- To hide #NULL errors:
- Right click the PivotTable
- Choose PivotTable Options
- Check the box next to “For error values show:” (and leave the field blank)
- 00:04 In addition to a straight out difference from calculation for
- 00:08 a pivot table we also have the ability to get a percent difference from as well.
- 00:12 This one can look a little bit strange depending on how you do it, so
- 00:15 let's go and explore this one and see what we come up with.
- 00:19 What I'm going to start by doing is going and grabbing the Units column and
- 00:22 pulling it onto my pivot table again.
- 00:24 So I now have my Units and I've got my Sum of Units.
- 00:27 And I'm going to change the aggregation on this to Show Values As and
- 00:31 we'll use a % Difference From.
- 00:33 Now, at this point, it's going to give me a horizontal one.
- 00:36 I'm going to say, well, rather than Bottled Beer, let's go with (previous),
- 00:39 because there's some strange stuff that happens when you do this.
- 00:43 When you click OK,
- 00:43 you'll notice that not all of the fields that are actually here get filled in.
- 00:47 Well, why is that?
- 00:49 Well, if you look at Bottled Beer, there's no previous unit.
- 00:52 So it's not going to give you any value for
- 00:54 this because there can't be a percentage difference from nothing.
- 00:57 When we get to 4 units for Canned Beer, you can see that we've actually got
- 01:00 a difference of 6 units so it's dropped 60% over the 10.
- 01:04 Then we get Coolers Ciders and we're blank again, we have no units.
- 01:07 So when we get to Draft Beer because there is nothing there it can give as a decrease
- 01:12 or an increase per se that works out to a meaningful percentage, so
- 01:15 it leaves it blank.
- 01:17 But then back at Liquor, well we now know that we've actually dropped by 29 units so
- 01:22 now we can actually make a proper calculation out of it.
- 01:25 So it looks a little bit strange when you do this.
- 01:27 I want to show you how to reconfigure one of these when you realize that,
- 01:30 hey, I didn't really want this going up or down.
- 01:32 I really want it going left, right.
- 01:34 I'm going to go back and I'm going to reapply the % Difference From.
- 01:39 And it says okay, you didn't want Category, what do you want instead?
- 01:42 I'm going to say hey, you know what, I really wanted Week.
- 01:45 So let's get the percentage difference from the previous week.
- 01:48 And when I say OK, it now flips it so
- 01:52 I can say that's cool, % Difference From Prior Week.
- 01:58 And of course, this doesn't look very good on my header, so I'll right click,
- 02:02 format cells, wrap the text and there we go, now we can see what's going on.
- 02:07 Now this looks a little bit strange because we've gone from ten units
- 02:10 to 30 units, which is a 200% increase.
- 02:12 You think, well, wait a minute, is that 300%?
- 02:14 It's actually not because we're actually using the difference from the prior week.
- 02:18 The difference from the prior week is actually 20 units, and
- 02:22 therefore it's a 200% increase over the original 10.
- 02:26 So it looks a little bit strange potentially to the way this would work.
- 02:29 And then when we move across,
- 02:30 we now have 13 units difference between these two guys here.
- 02:34 So what that means is 13 as a percentage of 30 works out to 43%, and there we are.
- 02:38 We have this guy all set up.
- 02:40 One more, let's go grab units, we'll pull this onto the pivot table again.
- 02:45 And for this one, what we're going to do is we're going to make something that is
- 02:48 a % Difference From Week 1.
- 02:53 Again, the formatting is awesome.
- 02:55 Right click, Format Cells, check wrap text.
- 02:59 That looks a little bit better, and now we'll make the change to our aggregation.
- 03:04 Show Values As, % Difference From.
- 03:07 We'll go and change it to Week for our column header, and
- 03:11 we'll say let's lock it in to Week 1.
- 03:13 Once again, this area will all blank out because there's no difference from Week 1
- 03:17 to Week 1.
- 03:18 But when we get to Week 2, you can see that the percentage difference is exactly
- 03:23 the same as what we had when we had prior week.
- 03:26 Well, that makes sense because Week 1 is the prior week.
- 03:29 But when we move over a column, you'll now notice that there is a big difference.
- 03:34 When we get into Week 3, the percentage different from the previous week,
- 03:38 which is Week 2, is 43.33%.
- 03:40 But the percentage difference from Week 1, which where we had 10 units,
- 03:45 we're now at 43 units.
- 03:46 So the difference is 33 units, which is 330% of 10 units, okay?
- 03:52 So that's the way this thing is working in order to calculate these things out.
- 03:55 So whether or not this is something you use frequently is up for
- 03:59 debate of course, but it's nice to know that we have the option.
Lesson notes are only available for subscribers.