Running Totals

Quick reference

• 00:04 In this video, I'd like to show you another cool trick which is
• 00:07 where we can actually put running totals on our pivot tables.
• 00:10 Now before we get into it, I'm just going to quickly review what we have here.
• 00:13 We've got a pivot table that's got our class on the left hand side with
• 00:16 alcohol and food.
• 00:17 And you can see that food right now is collapsed to show us just the totals.
• 00:21 We also have it broken down by category.
• 00:24 So as you can imagine, if I expanded food, we'd see the different categories for
• 00:27 breakfast items and burgers, and things like that listed in here.
• 00:31 We also have the week number across the top, so we're seeing our sales by week.
• 00:35 And you can see from our values area that we're counting units, not amounts,
• 00:38 in this case.
• 00:40 Now what I'd like to do is I'd like to add something to the pivot table.
• 00:42 So for week 1, I can see the cumulative sales going down this category.
• 00:46 So what I'd like to see is in the first row, 10, and then in the second row 14,
• 00:50 because I'd like to add 4 to it.
• 00:52 And then the third row where we get to coolers and ciders,
• 00:55 we're going to stay at 14 because we've got a blank and
• 00:58 then draft beer will add 46 to it and so on, all the way down to get 202.
• 01:02 So how do we do that?
• 01:03 We already have units on the pivot table but that's no big deal because we can go
• 01:07 and we can add the same field again, and do a different aggregation.
• 01:10 So we'll grab units, we'll drag it back in there.
• 01:13 Now obviously, it gives the same thing but
• 01:15 one of the things I like to do right away is rename my columns and
• 01:18 give them a nice little descriptive name so I know what I'm actually working on.
• 01:22 It helps give me context so it'll call us when we're running total.
• 01:26 Here we go.
• 01:27 And once I rename it, they're renamed it on all the fields.
• 01:30 But of course, it's not running total yet, I need to fix that.
• 01:33 There's two ways to do this.
• 01:34 I'm going to show you the slow way first.
• 01:37 The slow way is where we'd right-click and go into value field settings.
• 01:40 It's one of the most used dialogs we actually have on this pivot table is
• 01:43 this field.
• 01:44 When we get there, it takes us in, it says, this is a running total.
• 01:47 Would you like it to be a sum, a count, or an average?
• 01:49 Well, obviously I'm going to stay with a sum.
• 01:52 But what we're going to do is we're going to flip over to this Show Values As.
• 01:56 And when we do that, we get to this No Calculation right now.
• 01:59 This is just a straight out sum.
• 02:01 We're going to change this to say that we'd actually like to have
• 02:04 a Running Total.
• 02:06 There it is, Running Total In.
• 02:08 And now it asked me, what would you like your running total to be in?
• 02:10 And at this point, we need to use a field.
• 02:13 If we want to have a running total that runs down the table,
• 02:16 we need to use one of the fields on the left.
• 02:18 In this case, it probably makes good sense to use category.
• 02:22 So we'll say OK, and you'll notice that it changes right away, 10, 14,
• 02:26 14, 60, 77, 102.
• 02:28 That's perfect, that's doing exactly what I want.
• 02:32 But, what's with the #N/A going on here?
• 02:35 That's not really good at all.
• 02:37 Notice that the Alcohol Total has a blank cell in this particular case.
• 02:42 And the reason being is because, it's run all the running totals,
• 02:45 it's not going to restate it in this area, it's just the way the pivot table works.
• 02:49 If I collapse alcohol, notice that it gives me a #N/A there.
• 02:53 Well, that's not cool.
• 02:54 I'd rather see this be blank than do that.
• 02:57 Now as it turns out, there's a pivot table option that we can use to control this.
• 03:02 To do that, we're going to go right click,
• 03:04 and we're going to choose PivotTable Options.
• 03:07 Notice that there is an option here that say, for error values show.
• 03:10 So I'm going to check the box there and I'm just going to leave this blank.
• 03:15 And now what you'll see is all these #N/As disappear.
• 03:19 So with food, when I drill in, I can see my running total.
• 03:21 When I drill out, it just collapses it down.
• 03:25 The same is true for alcohol.
• 03:26 No more errors but the running totals show up when I need them and
• 03:29 go away when I don't.
• 03:31 Now, that's a running total down a column.
• 03:33 And you'll notice this done it for each column, which is nice.
• 03:36 What if I wanted to have a running total if it goes across my columns, so on rows.
• 03:42 Let's see how this looks different.
• 03:43 I can grab unit scan, going to pull it in over here, and this time,
• 03:47 we're going to say.
• 03:49 This one is a running total again, so we'll call that Running Total.
• 03:54 And now, I'm going to show you the other way to do this, right-click,
• 03:59 we're going to Show Values As, Running Total In.
• 04:02 So if I've already rename the column, this saves me a little click here.
• 04:06 And it says what's your base column.
• 04:08 Now, if I did category, that's going to repeat what we did before,
• 04:10 that's not really going to help us.
• 04:11 So what I'm going to do is I'm going to change this and say,
• 04:14 let's use week instead.
• 04:16 And when I say OK, now you'll see that we go 10 plus 30 equals 40,
• 04:21 plus 43 equals 83.
• 04:23 The challenge here, when you're using a running total that goes across your table,
• 04:27 it's really hard to read if you've got the original units column in there.
• 04:31 It just seems to give us, I don't know, more noise.
• 04:35 So you know what the cool thing is here?
• 04:37 I'm going to drag units off the pivot table, I can get rid of it.
• 04:41 And there we go, 10, 40, 83, 118, 135.
• 04:44 This is now the running total of our sales going across our pivot table.
• 04:49 So this is the cool thing.
• 04:50 We've got some great different ways to go either vertical or horizontal depending on
• 04:54 what we need, and we don't even have to have the original column there.
• 04:57 We can make the pivot table show exactly what we want for our needs.

Lesson notes are only available for subscribers.