GoSkills
Help Sign up Share
Back to course

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 difference from another value in a PivotTable. 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.

Difference From Calculations.xlsx
197.3 KB
Difference From Calculations - Completed.xlsx
198.8 KB

Quick reference

Difference From Calculations

Calculating differences from other fields in PivotTables.

Where/when to use the technique

When you need to display values that are a difference from another value in a PivotTable.  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 Pivot
  • 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 Pivot
  • 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
Using % difference from
  • Follow the steps for “Difference From” above, but choose “% Difference From” instead of “Difference From” in the “Show Values As” step

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 this video,
  • 00:05 we're going to look at setting up a difference on our pivot table.
  • 00:08 And this is a good place where it might actually be really useful for us.
  • 00:11 You'll notice we've got categories down the left hand side, we've got our week
  • 00:14 numbers across the top, and then the values area for a pivot table.
  • 00:17 We're summing up the number of units sold by product by week.
  • 00:21 But what if I want to see how that week has changed versus the prior week?
  • 00:26 This is where a difference from can be a very useful field on our pivot table.
  • 00:30 So to add one of these, what we're going to do is again,
  • 00:33 we're going to drag units back into the pivot table again.
  • 00:36 This now gives us a Units column and a second column called Sum of Units,
  • 00:42 which I'm now going to rename two Diff vs Prior Week.
  • 00:47 Now, unfortunately, this didn't really look all that good because we've got text
  • 00:50 that's actually hiding under here.
  • 00:52 So what I'm going to do is I'm actually going to right click on this entire column
  • 00:55 here, a row here, and we're going to go to Format Cells.
  • 00:58 And what I'm going to do is go to Alignment, and
  • 01:01 I'm going to choose to Wrap Text and say OK.
  • 01:03 And that'll actually wrap that in so I can see these things.
  • 01:06 Now, of course, this isn't the difference versus the prior week right now,
  • 01:09 it's just a regular old sum, so we need to change that.
  • 01:13 So I'm going to start again by doing this the hard way,
  • 01:15 which is going to Value Field Settings.
  • 01:18 When I go into Value Field Settings, I'm still going to use a sum.
  • 01:21 I'm happy with the name of my field.
  • 01:22 I'm going to go to Show Values As, and rather than do No Calculation,
  • 01:26 we're going to scroll down this list until we find Difference From.
  • 01:31 Now, when it asks me to set up my difference, it immediately comes back and
  • 01:35 gives me the field from the left hand side, which is Category.
  • 01:38 And that might be useful maybe, except that in this case I really want to see
  • 01:42 the difference versus the prior week which is a column value at the top.
  • 01:45 So what I'm going to do is, I'm going to scroll down this list and say, no,
  • 01:49 I want to go based on week.
  • 01:50 And it says, all right, do you want to base it on?
  • 01:53 We're going to choose previous item.
  • 01:55 And we'll say OK, and just like that you'll notice that in the first week
  • 01:59 there's no difference between that and the previous week because there is no previous
  • 02:03 week, so that kind of makes sense.
  • 02:05 We've got 66.5 units for appetizers in week number 2,
  • 02:09 which is 19.5 units more than what there was in week 1.
  • 02:13 And then from 66.5, we went down to 21 so
  • 02:16 we've actually got a decrease of 45.5 units.
  • 02:20 So it's actually showing us our difference versus the previous weekly value,
  • 02:23 which is perfect.
  • 02:24 That's exactly what we want.
  • 02:26 But what if we also wanted to say, can you show me the difference versus week 1?
  • 02:31 So I want to go to a specific week.
  • 02:33 And the answer is, of course, we can do that.
  • 02:34 We just have to do it a little differently.
  • 02:37 We'll grab units, we'll pull it on to the pivot table.
  • 02:39 And now, once we got some of the units on the pivot table again, we can go on and
  • 02:44 we're going to go and we're going to rename this one to Diff vs Week 1.
  • 02:49 Notice again it did not wrap the text.
  • 02:51 So once again, we're going to have to go back, right click on the row,
  • 02:54 format cells, check the box for wrap text and say, okay.
  • 02:57 It's very unfortunate it doesn't stick on our headers.
  • 03:00 Regardless, now that we actually have that there, we can go back and make changes.
  • 03:05 We could, again, go through Value Field Settings, or
  • 03:09 we can go through Show Values As and choose our Difference From.
  • 03:14 Within the Difference From,
  • 03:15 we can now say instead of using the base field of Category, we'll choose from week.
  • 03:19 And what's really nice about this little filter is it actually reduces
  • 03:22 it to the fields that are actually on the pivot table, allowing us to choose from
  • 03:26 just what's actually relevant, where the Value Field Settings gives us everything.
  • 03:29 The base that I'm going to use in this case is not previous,
  • 03:33 it's going to be week 1.
  • 03:34 And we'll say OK, and
  • 03:36 now what you can see again, there's no difference versus week 1,
  • 03:39 because that's what we're actually looking at, 19.5, so that totally makes sense.
  • 03:44 But when we start moving across the pivot table,
  • 03:47 we can see that with 47 units in week 1, we're now down to 21, that is 26 less.
  • 03:52 So everything looks like it's working perfectly.
  • 03:55 So this is how we can actually set up differences, and
  • 03:57 you can obviously set them to work versus the row above,
  • 04:00 we've just worked it with columns in this particular case.
  • 04:03 And naturally,
  • 04:04 if you don't want to see the original units, there's no need to show them.
  • 04:07 You can pull it straight off and
  • 04:08 just show the differences on the pivot table as well.

Lesson notes are only available for subscribers.

Percent of x Calculations
06m:13s
Percentage Difference From Calculations
04m:10s
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