GoSkills
Help Sign up Share
Back to course

Running Totals

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
200.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)
Login to download
  • 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.

Aggregation
05m:01s
Percent of x Calculations
06m:13s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement