🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

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

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