Locked lesson.
About this lesson
When you need to display a running total 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.
Running Totals.xlsx200.7 KB Running Totals - Completed.xlsx
200.9 KB
Quick reference
Running Totals
Adding running totals to PivotTables.
Where/when to use the technique
When you need to display a running total on your PivotTable.
Instructions
Accessing running totals
- Locate your PivotTable on the worksheet (not the field well)
- Right-click a cell in the PivotTable column/row you wish to add a running total to
- Either:
- Choose Value Field Settings > Show Values As > Running Total In
- Choose Show Values As > Running Total In
Running totals in columns
- For the Base field, choose the name of a field you put in the rows area of your PivotTable
Running totals on rows
- For the Base field, choose the name of a field you put in the columns area of your PivotTable
Hiding error values
- Running totals don’t display a value in subtotals or totals
- Worse, collapsing a subtotal displays a #N/A error for a subtotal
- To hide #N/A errors:
- Right-click the PivotTable
- Choose PivotTable Options
- Check the box next to “For error values show:” (and leave the field blank)
- 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.