Locked lesson.
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.xlsx193.4 KB Difference From Calculations - Completed.xlsx
195.4 KB
Quick reference
Topic
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 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 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 Pivot
- Choose PivotTable Options
- Check the box next to “For error values show:” (and leave the field blank)
- 00:04 In this video,
- 00:05 we're going to look at the methods we can use to show growth in a pivot table.
- 00:10 And the way that we do that is, we pull a value into our values area.
- 00:15 And then we actually go and we set up a difference from calculation in there,
- 00:19 instead of just using a regular sum or an average.
- 00:22 So if we look at the pivot table that I have here.
- 00:25 We can just check the configuration a little bit,
- 00:27 we've got a week across the top.
- 00:28 And you can see the weeks of the month, 1, 2, 3, 4, 5, we've got a category on rows
- 00:33 that's showing us the categories for all of our different sales items.
- 00:36 And in the values area of our pivot table here you can see that we've got units.
- 00:39 And this is just a simple sum of units measures that we've got in here that's
- 00:43 counting up, or summarizing, rather,
- 00:45 all of the individual units that have been sold, the total quantity of them.
- 00:49 Now, what I'd look to do, though,
- 00:50 is I'd like show on here the difference between this week and the previous week.
- 00:56 So the way that I'm gonna do tha,
- 00:57 is I'm gonna drag units again on to the PivotTable.
- 01:00 It's already on there once, but we'll drag it on again.
- 01:03 And you'll see that I now get an extra sum of units.
- 01:06 I'm gonna change the title on this one to growth versus prior week.
- 01:13 And because it didn't wrap very nicely I'm gonna select the row, right click and
- 01:18 format cells, and we'll just go to our alignment and we'll wrap text.
- 01:24 Whoops, that's not where.
- 01:25 Here we go, wrap text.
- 01:27 And we've now got a nice header here, but the numbers are still sum of units and
- 01:31 that's not very good.
- 01:32 So, what we're gonna do is we have two potential ways we could do this.
- 01:36 We could right click on it and we could go to value field settings.
- 01:40 And from value field settings we could go click show values as and
- 01:45 change from no calculation.
- 01:48 Somewhere down here we can see a difference from.
- 01:52 And we can set that up to be the base item of category.
- 01:56 But I would rather actually go and set this up to be based on the week.
- 02:01 And I'm gonna look at the previous week for the base item.
- 02:05 And what's gonna happen is,
- 02:07 in my first week it'll show me that there's no difference from the prior week,
- 02:10 which makes sense because there was no prior week here.
- 02:13 When I look at the next week, this is week two, you can see I had 66 and
- 02:17 a half units here, and it's a difference of 19 and a half units.
- 02:20 In this case we had 10 we had 30 in week two, that was a difference of 20 units.
- 02:27 In week three we had a difference of 13 units again over the prior week.
- 02:30 So that's kind of a neat little way that we can actually
- 02:33 start making up a difference via the previous column.
- 02:38 Now what if I wanted to set up that growth via week 1, so all of the stats,
- 02:42 for all of the pieces versus week number 1, I can easily do that as well.
- 02:47 I'll just grab units and pull that onto the pivot table, yet again.
- 02:52 And instead of sum of units we'll go with growth versus week one.
- 02:57 Now you'll notice that'll update across the pivot table and
- 03:00 again we have to right click and say format cells.
- 03:04 And we'll need to route text on those to make them look a little bit better.
- 03:09 And now we need to modify this measure again.
- 03:11 We do this one slightly differently.
- 03:13 We'll right-click, this time we'll say show values as, different from.
- 03:18 So we don't need to dig into the value fields settings to get there.
- 03:21 We can do it this way as well.
- 03:22 So difference from, when it says what's your base field,
- 03:24 I want the one that's sitting in the My Columns area, which is Week.
- 03:28 And this time instead of previous,
- 03:30 I'm gonna choose a specific value for week one.
- 03:33 Let's say okay.
- 03:35 So you'll notice that again in our first week we
- 03:38 have no difference versus week one.
- 03:40 That makes sense.
- 03:41 In our second week, we have a difference versus week one.
- 03:43 Which is the same as the previous week.
- 03:46 That makes sense again cuz week two, the previous week has the same as what?
- 03:49 Week one is?
- 03:51 But when we get to week three, you'll notice that we have 21 units.
- 03:54 The growth for via the previous week is down 45.5 but
- 03:59 the growth via week one is only down 26 units.
- 04:02 21 off of 47 gives us 26.
- 04:05 So we can see we have a different measure here and
- 04:08 it's working differently for the pivot.
- 04:11 Now, something that's probably worth mentioning, as well,
- 04:13 is that we don't actually have to keep our original units on the table.
- 04:17 We could pull that off and only show the differences if we wanted.
- 04:20 I could even say what's the Growth vs Week 1 if I wanted to pull all of that off.
- 04:25 And show you exactly how much things went up or down, relative to the first week.
Lesson notes are only available for subscribers.