Running Totals

Quick reference

• 00:04 IN this video we're gonna look at a neat little trick here for
• 00:07 putting running totals on a pivot table.
• 00:10 Now this can be really useful in certain situations.
• 00:13 So before we jump to it, let's just quickly review what we have here.
• 00:16 We have a pivot table that's got weeks running across the top in columns
• 00:20 as we can see.
• 00:21 On the left hand side we've got our class, so alcohol and food.
• 00:25 And Food's collapsed right now.
• 00:27 We've got another row field that shows us our subcategories for
• 00:31 our different classes.
• 00:32 And there will be more underneath the food areas as well.
• 00:35 And in the values area of the pivot table,
• 00:37 you can see on the right hand side that we have units.
• 00:39 So this is a simple sum of units count.
• 00:42 Now what my goal is here, is that I'd really like to see another column here
• 00:46 that shows the accumulating count by category of what my unit sales are.
• 00:51 So I'd like you to start off with ten and then go to 14 when it hits canned beer.
• 00:55 There will be no more when we hit coolers/ciders so
• 00:58 it would stay 14 and then get to 60 and 77 and so on down to 102.
• 01:03 So the way that we're gonna do that is we're gonna look over here
• 01:07 where it says units and you'll notice it's all ready on the PivotTable, but
• 01:11 that's okay, because we can add the same field multiple times.
• 01:14 So we're gonna drag that guy back onto the PivotTable again.
• 01:18 Let them go and we've got sum of units.
• 01:19 Now the first thing I like to do when I'm trying to make something different is I
• 01:24 like to go to the pivot table and just rename it
• 01:26 to something that makes a little bit more sense so I don't get lost and confused.
• 01:30 So I'll just change this to running total.
• 01:33 And now I need to actually turn this into a running total.
• 01:36 So the way I can do that is, there's a couple of different methods,
• 01:39 one faster than the other.
• 01:40 So this is the long way.
• 01:41 We'll right click, we'll see Value Field Settings.
• 01:46 We'll go to Show Values As.
• 01:49 And instead of no calculation, we're gonna go and take a look at running total in.
• 01:56 Now, if I'm trying to make a running total that accumulates down a column, I need to
• 02:01 point to a row field because basically what it's saying is, every time your
• 02:05 field over here changes, I'd like to add whatever's in this to the previous value.
• 02:11 Okay, so for us right now that's gonna be category, so
• 02:14 every time the category changes, I'd like you to take the units for
• 02:17 that category and add them to the running total of the previous categories.
• 02:22 So, we'll say okay.
• 02:24 And look at that, it's magic.
• 02:25 We've got ten units starting off.
• 02:27 And on the next row we get 14.
• 02:29 And the 14 again because there was no unit sold.
• 02:32 We're up to 60, 77, 102.
• 02:35 And that matches to the total for alcohol sales which is great.
• 02:39 But look at our subtotals, we've got a bit of a problem here.
• 02:42 When we have a subtotal for a running total, It says nothing.
• 02:46 It doesn't bother with it because it knows that we've done a running total,
• 02:49 all the way up to this point.
• 02:50 And that's gonna match the sub total over here.
• 02:52 So it leaves this blank.
• 02:53 But what about when our rows are collapsed, like we get a #N error.
• 02:57 And what's really weird is when we open this up, it goes away.
• 03:01 Because it can give us the running totals, so that's all good.
• 03:05 But as soon as we collapse it again, we get the #N/A back.
• 03:08 Well, that's not ideal, and we don't really want to see those in a pivot table.
• 03:11 And we can change that by flagging a pivot table option.
• 03:14 So if we were to right-click on the pivot table, and say PivotTable Options.
• 03:20 You'll notice that there's a setting here that says, For error values show.
• 03:24 Let's check that box and leave it blank.
• 03:28 And when we say okay, you'll notice those error values go away.
• 03:32 So now, we can expand Food.
• 03:34 We can see what's there.
• 03:36 We can collapse it and the error values go away.
• 03:38 And that's perfect.
• 03:40 Now, what about rows.
• 03:43 What if we wanted to accumulate going across.
• 03:46 Well we can certainly do that as well.
• 03:49 We've got the exact same pivot table frame here.
• 03:51 So let's try it.
• 03:52 Let's go and drag units back onto the pivot table again.
• 03:56 And let's call this one a running total by week.
• 04:02 Because that's what we're looking at here.
• 04:04 And I'm just gonna quickly go and route the text on this, so
• 04:07 that we can actually see what I said.
• 04:09 So, wrap text, here we go.
• 04:13 And now we're gonna set this up so it's gonna give us a different running total.
• 04:17 So, we're gonna right click on this.
• 04:19 And this time instead of going through value field settings,
• 04:21 I'll go to show values as, because I can get to it from here as well.
• 04:25 Running total in, because I'm going a cross I need to point for
• 04:29 something up here.
• 04:29 So, rather than category we'll take a running total in week.
• 04:34 And say OK.
• 04:34 And notice that now we've got 10 plus 30 equals 40,
• 04:39 plus 43 equals 83, so this looks like it's working nicely.
• 04:43 What's even better with this is that sometimes this is too much
• 04:46 noise on the pivot table.
• 04:47 We don't to see the original units, all we want to see is the running total, so
• 04:51 let's just grab units and pull it off.
• 04:53 It's not dependent on each other.
• 04:57 See 10, 40, 83, 118, so depending on how we like to see it
• 04:59 we can make this pivot table look exactly as we want.

Lesson notes are only available for subscribers.