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.xlsx192.8 KB Running totals - Completed.xlsx
193.6 KB
Quick reference
Topic
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 Pivot
Running Totals on rows
- For the Base field, choose the name of a field you put in the columns area of your Pivot
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 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 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.