Totals and Sub-Totals

Quick reference

• 00:04 One of the cardinal rules of creating a good dataset for
• 00:07 a pivot table is to never include a total or a sub total in the data table.
• 00:12 And the reason for that is that pivot tables can add them for us, and
• 00:15 I'm gonna show you this, because this is a very cool feature.
• 00:18 So if we say we've got a nice big table of data here.
• 00:21 I'm gonna say insert.
• 00:22 We're gonna add a pivot table on a new worksheet.
• 00:25 There we go.
• 00:26 Now.
• 00:27 What I'm gonna do is I'm going to grab class and put that on my rows.
• 00:31 So I've got my alcohol and food, and I'll just go and
• 00:34 sum the amount on the values field.
• 00:36 There we go.
• 00:37 Now.
• 00:38 I'm gonna break the alcohol and food down a little bit more.
• 00:41 And I'm gonna do that by grabbing the category and putting it under class.
• 00:45 And what you can see is that it automatically adds a subtotal for
• 00:49 all of the items that are actually showing up for alcohol.
• 00:52 So if we look down at the bottom here, we can see that I have 5,686.1,
• 00:56 and that's exactly what is subtotalled up in the top.
• 01:00 Now, if you're an accountant, though, this isn't cool because subtotals belong
• 01:05 underneath the data not on top of the data.
• 01:07 So the first thing that I'm gonna do as an accountant who's rather well, particular,
• 01:12 is I'd like to move this down below.
• 01:14 And I'd like to move the food subtotal down below.
• 01:17 To do that, we're gonna go to the pivot table tools tab and go onto design.
• 01:22 And over here on the very left hand side we've got a button that says subtotals.
• 01:26 If we click on the bottom it says Show all Subtotals at Bottom of Group.
• 01:31 And that will flip them down below where I actually wanna see them.
• 01:35 So that's good.
• 01:37 Now, let's go and add another level to this.
• 01:40 We'll add Item Name under Category.
• 01:43 And you'll see that now we get all of our individual sales items showing
• 01:47 up under the individual categories as well.
• 01:49 And if I scroll down to say about row 128 or so, I believe, here we go,
• 01:55 we've got an alcohol subtotal, we've got a wine subtotal above it,
• 02:00 just gonna scroll up a little bit here and you can notice that if I hit
• 02:03 the little collapse button it brings up the subtotals as well.
• 02:07 So it compresses all the individual values and still shows me that subtotal on
• 02:11 the table so this allows me to drill in and drill out.
• 02:14 The key that I wanna show you here those,
• 02:15 what if I don't want a subtotal sum of these things.
• 02:18 Maybe I didn't want to put a subtotal on all of the individual categories.
• 02:23 So if I right-click on wine here, this is one of my headers for a category.
• 02:28 I could uncheck the box to say subtotal category.
• 02:32 And all of those category subtotals will go away.
• 02:35 Well, wine didn't.
• 02:36 Why?
• 02:38 Wait a minute, if I open it up, you'll notice that all the items are there and
• 02:44 it's not subtotaling it but if I collapse it, it'll come back as a subtotal.
• 02:49 So let's take a look at appetizers.
• 02:50 We can see that all of these guys here, there's no subtotal on it.
• 02:53 But when I collapse this, it now subtotals it up for me as well.
• 02:57 So I can still see as I collapse all these guys what's actually going on in there.
• 03:03 And I'm only looking at the details on the particular items that I wanna see.
• 03:06 So that's kinda nice.
• 03:08 What if I didn't want any subtotals.
• 03:12 If we go back up to the subtotal menu again it's on pivot tables tool design and
• 03:15 this menu will only show up while we're in our pivot table.
• 03:19 We can go to design subtotals and we can turn off, do not show subtotals at all.
• 03:25 So, all of the subtotals will disappear except for the collapsed items.
• 03:29 And if we drill into those, you'll see we've got no subtotal on food.
• 03:32 We've now got no subtotal on breakfast.
• 03:35 Nothing above, nothing below, but again when we collapse
• 03:37 these individual categories, it still totals those up for us.
• 03:40 So that's kinda cool.
• 03:43 Let me get rid of the Item Name.
• 03:45 I'll pull that off the table, and
• 03:47 we're gonna go all the way back up to the top here.
• 03:50 Now, I'm gonna show my subtotals again at the bottom of the group.
• 03:53 So I can turn them back on just by selecting one and
• 03:55 saying, Show all Bottom of Group, that's cool.
• 03:58 We also have some options for subtotals.
• 04:01 If we right-click on one of them and
• 04:03 go to Field Settings, you'll notice that we can choose Custom.
• 04:07 We could choose to add a Sum and a Count and an Average and Max.
• 04:11 We can even go in and out of other things like standard deviations as well.
• 04:15 If we say OK it actually allows us this way to create multiple items on for
• 04:20 our subtotals on our pivot tables.
• 04:21 This happens for alcohol and for food down below as well.
• 04:24 And you'll notice the $9.36 average here is the average of
• 04:28 all of the items that made up the totals that are showing up here.
• 04:32 So it's pretty good detailed stuff.
• 04:35 We can also get rid of them the same way, right-click Field Settings.
• 04:38 Uncheck all the guys that we selected, or set it to Automatic, and
• 04:42 they will go away just like that.
• 04:44 Now make a quick change to this pivot table, gonna drag class onto columns,
• 04:48 because I wanna show you something about grand totals.
• 04:50 We've got a grand total for every row here as well as grand totals for columns.
• 04:55 If we go back to the pivot table tools design tab.
• 04:57 You'll notice we have very limited options for grand totals.
• 05:01 We can turn them off completely, or
• 05:05 we could go and turn them on for just rows.
• 05:09 And it'll sum across.
• 05:11 Or we could turn them on for just columns.
• 05:14 So, not very many options, but
• 05:16 certainly there are some to make your pivot table display the best way possible.

Lesson notes are only available for subscribers.