GoSkills
Help Sign up Share
Back to course

Totals and Sub-Totals

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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

Managing Power Queries
04m:39s
Choosing PivotTable Layouts
04m:53s
Share this lesson and earn rewards

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