Locked lesson.
About this lesson
When you need to display a value as the % of another value on your PivotTable.
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.
Percent of x Calculations.xlsx10.9 MB Percent of x Calculations - Completed.xlsx
10.9 MB
Quick reference
Topic
Display a value as the % of another value on a PivotTable.
Where/when to use the technique
When you need to display a value as the % of another value on your PivotTable.
Instructions
Accessing % of calculations
- Drag a value column onto the PivotTable
- Right click a cell in column you wish to convert to a % calculation
- Either:
- Choose Value Field Settings > Show Values As > the desired % of calculation
- Choose Show Values As > the desired % of calculation
% of Grand Totals
- % of Column Total: Expresses value as a % of the Grand Total row for that column
- % of Row Total: Expresses value as a % of the Grand Total column for that row
- % of Grand Total: Expresses value as a % of the Grand Total of the PivotTable
% of Parent
- % of Parent Column: Expresses value as a % of the parent field’s (sub)total for that row
- % of Parent Row: Expresses value as a % of the parent field’s (sub)total for that column
- % of Parent Total: Expresses value as a % of the (sub)total of the PivotTable
% of… (a specific field)
- To choose your base field, you need to decide if you’d like the % of to be calculated down a column or across a row
- Column: Pick a row field
- Row: Pick a column field
- % of can be set to:
- A specific item within a field (useful for comparing growth vs a specific year)
- The previous item (useful for comparing sales performance vs the prior month)
- The next item (useful for comparing forecast expectations)
- 00:04 In this video we're going to take three separate pivot tables, and try and
- 00:07 add a little bit more intelligence to the numbers that are actually showing on them.
- 00:12 So let's start with the sales by year.
- 00:15 And what I'd like to know right now is,
- 00:17 if I can add extra intelligence to the pivot table.
- 00:19 To find out what our sales are as a percentage of the individual years.
- 00:24 And also as a percentage of the categories going across.
- 00:28 The way that I'm gonna do this,
- 00:29 is I'm going to drag the amount column back on to my pivot table.
- 00:34 So, sales dollar, is the amount column.
- 00:36 We're gonna duplicate this one.
- 00:38 And then what I'm gonna do is.
- 00:39 I'm gonna right click and go to value field settings.
- 00:43 And I'm gonna click the show value as tab.
- 00:46 And this time instead of using no calculation,
- 00:49 which is what the default is, we're gonna go and choose percent of column total.
- 00:53 And what we'll do is we'll give this a more
- 00:57 logical name like % last 5 years.
- 01:01 So this will show us the sales as a percentage of the last five years of
- 01:06 sales.
- 01:07 And we can see that 2009 sales.
- 01:10 And if I were to go and prove this out, 250,000 divided by the grand total.
- 01:16 Tells me that it's 19.9% of the grand total of sales.
- 01:19 This is what this is actually calculating here.
- 01:21 Is what this is as a percentage of the grand total.
- 01:27 In addition we've got the same thing replicating for food sales as well as for
- 01:31 the total sales.
- 01:31 So that's kinda cool.
- 01:33 So how about going across the pivot table then.
- 01:35 If we wanted to have our percentages, so
- 01:37 that they work as a percentage of total sales.
- 01:39 Rather than the percentage of the grand total row.
- 01:41 And that's easy enough to do as well.
- 01:43 We'll grab our amount, drag it on to the pivot table again.
- 01:47 We'll right click.
- 01:48 Go to value field settings, show values as.
- 01:52 We'll chose percentage of row total.
- 01:56 And this time around,
- 01:57 we'll give this one here a name that says percent of current year.
- 02:01 And, okay.
- 02:03 And you'll see that now we can see that our alcohol sales were 40% of
- 02:08 our total sales, and 59.3% of our total sales was our food sales.
- 02:14 So that works across as well.
- 02:17 What if we wanted to see, though,
- 02:19 the percentage as a percent of the grand total for the entire thing.
- 02:23 Well we can do that too.
- 02:25 I'll drag Amount onto the pivot table as well.
- 02:28 Once again, right-click,
- 02:30 Value Field Settings > Show Values As > % of Grand Total.
- 02:35 And this will pick up from the very, very bottom corner of all.
- 02:39 So here we'll say, % of Grand Total.
- 02:43 And we can now see that of the $3.3 million we've brought in
- 02:47 over the last five years.
- 02:49 That our food sales in 2009 were 10.94% of those total numbers.
- 02:57 And 7.49% of the grand total, was the alcohol sales for 2009.
- 03:01 So lots of different information that we can get out of that.
- 03:05 Now let's go look at the sales by category report.
- 03:09 We've got sales dollars on here, again it comes to the amount column.
- 03:12 We'll drag amount on here again.
- 03:14 And this time rather than using percent of the Column.
- 03:18 Why don't we got and summarize this one by the percent of the parent row.
- 03:26 And what this is going to do, is it's actually going to create a calculation
- 03:30 where if we look at the 21,000 divided by the 250,000.
- 03:37 You'll see that we come back with 8.49%.
- 03:42 This guy here, though, the 40%, is actually 40% of the 616,000.
- 03:47 So basically what's happening is this calculation percent of column total.
- 03:52 Is actually generating or present a parent total rather,
- 03:56 Is actually generating based on the category sub-heading level.
- 04:00 And if it is at the top level category, it's generating based on the grand total.
- 04:04 We can also of course, run the exact same thing by pulling this back onto the table,
- 04:09 again another sum of amounts.
- 04:11 And we can right click, value field settings, show values as.
- 04:16 And this time we can go with the percent of the parent column total.
- 04:21 This guy, instead of working from top to bottom on the pivot table this way.
- 04:26 We'll actually look at the items going across the table.
- 04:30 So, we can see in this particular case.
- 04:33 Everything is based on 2009 and 2010.
- 04:37 If we added additional sub-categories to that, say March, April, May, June.
- 04:41 It would actually be calculating the percentage horizontally across.
- 04:45 So that is the % of Parent Row.
- 04:51 And this guy here being the % of Parent Column.
- 04:56 Last, let's go look at the sales trend.
- 04:58 On this pivot we're going to throw two more columns on.
- 05:01 The amount column once, and twice.
- 05:05 The first one we'll go to value field settings.
- 05:08 And we're going to do something a little bit different.
- 05:10 This time we'll choose percentage of and set this to be the percentage of 2009.
- 05:16 We'll just label it 2009.
- 05:20 And on the other one, we're going to right click value filled settings again.
- 05:26 Show values as.
- 05:27 Choose again, percentage of.
- 05:29 But we're going to chose the previous year.
- 05:32 And so again percentage of previous will work.
- 05:37 Say okay.
- 05:39 And you can see the differences here that are interesting.
- 05:41 This will give us the percentage of 2009.
- 05:43 100% for 2009, obviously.
- 05:45 97% percent of 2009.
- 05:47 98.
- 05:48 You can see that this one actually returns the percentage of the previous year.
- 05:52 So in the particular case of the 101.24.
- 05:56 It's actually an increase over 2010.
- 05:58 Where that's actually a decrease over 2009.
- 06:01 So another couple of way that you could use percentage of to get some good data.
Lesson notes are only available for subscribers.