GoSkills
Help Sign up Share
Back to course

Percentage Difference From Calculations

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
197.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)
Login to download
  • 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.

Difference From Calculations
04m:37s
Top and Bottom x Items
05m:17s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement