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.xlsx193 KB Percentage Difference From Calculations - Completed.xlsx
195.8 KB
Quick reference
Topic
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 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
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 we're going to look at difference from.
- 00:08 But this time with a slight bit of a twist.
- 00:10 Rather than putting just values onto our pivot table.
- 00:13 We're going to use a percentage difference from.
- 00:16 So that we can actually get the percentage change between week 1 and
- 00:21 / or the previous week.
- 00:23 So using the exact same pivot table layout as we used in our previous video.
- 00:27 So we've got weeks across the top.
- 00:29 We've got sales categories down the left-hand side.
- 00:31 And the main body of our pivot table here is sum of units.
- 00:36 So this time, in order to use a percent difference from.
- 00:40 What I'm gonna do is, I'm once again gonna grab a duplicate copy of units, and
- 00:44 drag it onto the PivotTable.
- 00:47 Once it's there, I'm going to quickly change the name of my column.
- 00:50 So that I don't lose track of what I'm trying to do.
- 00:52 That's going to be percent growth versus prior week.
- 00:58 And I'm quickly going to go and Format Cells.
- 01:04 And we're going to wrap text.
- 01:07 That looks a little bit better.
- 01:09 Now we're going to make our modification to this pivot table column.
- 01:13 So now we're going to right click, and we'll say show values as.
- 01:17 Rather than difference from, we'll take percent difference from.
- 01:22 And when I'm trying to apply a percent difference to a previous column.
- 01:25 I'm always gonna look for the value that ends up in my column header.
- 01:29 If I was trying to make a percent difference on a row basis.
- 01:32 I will be looking for the row headers.
- 01:33 But in this case here, we're gonna go with a column.
- 01:36 So we're gonna grab week.
- 01:37 And I'm gonna base this versus the previous week, and say okay.
- 01:41 And you'll notice that my growth versus the previous week is 41% increase,
- 01:47 200% increase.
- 01:48 And then when I move over to week 3, I've got a 68% decrease for
- 01:51 appetizers, >> And
- 01:52 a 43.3% increase over the previous week.
- 01:56 Which was already up 200% for my bottled beer.
- 01:57 So that's kind of some interesting statistics to have.
- 02:02 Now what if I want my percentage growth versus week one.
- 02:06 So that I can see how my base week compares to where I am now?
- 02:12 Well again, we'll drag grab units.
- 02:14 We'll drag this across, drop it on the pivot table again.
- 02:18 We'll quickly go back and give it a new title.
- 02:20 Percent growth versus week, oops, 1.
- 02:24 And it's frustrating that we have to do this.
- 02:28 But every time we do this, we have to go back and rewrap the text on it.
- 02:32 Because it doesn't inherit that property.
- 02:35 And there we go, it's there now.
- 02:37 And now, once again, we'll right click, show values as, percent difference from.
- 02:42 And this time we're gonna go back, and we're gonna show it versus the week.
- 02:48 But rather than just choosee the previous item, or the next item.
- 02:51 Which would show the percent difference from the next week.
- 02:53 What we're gonna do is, we're gonna again force this back to week 1.
- 02:57 And then we'll say okay.
- 02:59 So again, we've got nothing in our week 1 column.
- 03:02 Remember these first three are for week one.
- 03:04 And that makes sense because its 100%.
- 03:07 Our growth versus week 1, in week 2.
- 03:12 Matches what the growth versus the prior week was.
- 03:14 So that makes sense as well.
- 03:16 And then as we move across and we start looking further out.
- 03:19 We can that the values very much change.
- 03:23 So our growth versus week 1, we were down.
- 03:25 Actually we're down in a lot of different categories here versus week 1.
- 03:29 Although some versus the prior week, I should say.
- 03:31 So week 4 must have been just great.
- 03:33 But we also were way up over week 1, in certain cases 254%.
- 03:38 So Some different formulas that allow you to get different values out of your pivot.
- 03:44 And return some different intelligence.
- 03:46 And again, with these you don't have to keep the original pieces on there.
- 03:49 If the only thing you want to see is the growth versus week 1,
- 03:52 then you can just rip these other two guys off.
- 03:55 And go back across.
- 03:56 And you can see what the change is that's heading through here.
- 03:58 So, however you like to see it, you can configure it to work that way
Lesson notes are only available for subscribers.