Locked lesson.
About this lesson
Use to turn totals and subtotals on/off where needed in 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.
Totals and Sub-Totals.xlsx155.5 KB Totals and Sub-Totals - Completed.xlsx
200.6 KB
Quick reference
Topic
Controlling totals and sub-totals in a PivotTable.
Where/when to use the technique
Use to turn totals and subtotals on/off where needed in your PivotTable.
Instructions
Create your PivotTable
- Begin by creating a PivotTable with at least 2 row fields and 2 column fields
- Notice that subtotals are automatically added
Subtotals
Show Subtotals Below Data
- Go to PivotTable Tools Design tab, Subtotals, Show all Subtotals at Bottom of Group
Turning Subtotals on/off
- Individual subtotals: Right click row (or column) label on the PivotTable and choose to check/uncheck the “Subtotal <field name>”
- All subtotals: Go to the PivotTable Tools Design tab, Subtotals, Do Not Show Subtotals
Multiple Levels of Subtotals
- Right click the row label and choose Field Settings
- Change “Automatic” to Custom and click all the subtotals you wish to display
Keep in mind
- You can turn subtotals back on at any time
- Collapsing rows/columns will display the subtotaled values
Grand Totals
- Grand total commands are found under PivotTable Tools Design tab, Grand Totals
- Can hide or show grand totals for both columns and rows at once
- Can hide or show grand totals for columns only (shows in final row of Pivot Table)
- Can hide or show grand totals for rows only (shows in final column of Pivot Table)
- 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.