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
163.4 KB
Totals and Sub-Totals - Completed.xlsx
208.3 KB

Quick reference

Totals and Sub-Totals

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 building good pivot table data is to never have totals
  • 00:08 or sub totals and your data source.
  • 00:11 And in this video, we're going to show you exactly why,
  • 00:13 it's because the pivot table can create them for you.
  • 00:16 So I'm going to take this range of data here.
  • 00:18 It's in a nice format, in a nice table, and I'm going to go to insert Pivot Table,
  • 00:23 you'll see it picks up the tables name for source data.
  • 00:25 And I'm going to go drop this pivot table on a new worksheet.
  • 00:29 And we're now going to go and build it up.
  • 00:31 We're going to grab our class, we'll put that on rows, and I'm going to go and
  • 00:35 grab amount and put it on the values.
  • 00:38 And just like that, it nicely sums it up by alcohol and food.
  • 00:42 But I want to make this bigger.
  • 00:44 So I'm going to grab Category and slide it down underneath class on rows.
  • 00:48 And now you can see that it's broken down our class, alcohol and food,
  • 00:52 by our individual categories.
  • 00:53 And it's actually also nicely sub totaled them here.
  • 00:56 So there's the 5,686.1 and 18,122.5.
  • 01:02 So that's pretty cool, except that there's a problem with these subtotals here.
  • 01:06 They're above the data.
  • 01:08 Now, if you ask any accountant around the world,
  • 01:09 they'll tell you that subtotals belong below the data.
  • 01:13 So how do we do that?
  • 01:14 Well, we can actually modify this by going to our pivot table tools design tab.
  • 01:20 So on Office 365, we just have design over here.
  • 01:23 In earlier versions, you'll see the pivot table tools contextual tab at the top.
  • 01:27 When you click on design, it's going to take you into the ability to go and
  • 01:31 change your subtotals over here on the left.
  • 01:33 Notice that I can click the drop down and
  • 01:35 I can show all my subtotals at the bottom of the group.
  • 01:38 And it will now pop those guys down below the data rather than on top.
  • 01:42 So that's pretty cool.
  • 01:44 But let's amp this up even more.
  • 01:46 Let me grab item name and slide it down underneath category on my pivot table.
  • 01:51 And now you'll notice that my item names are here, every category is subtotaled.
  • 01:55 And if I scroll down to say, around row 129 here, you'll notice
  • 02:00 that we get to our category total for wine and our class total for alcohol.
  • 02:04 The other nice thing about working with multiple levels of data inside
  • 02:08 a pivot table is that it actually gives you these little plus, minus buttons here.
  • 02:12 So I can collapse Wine down and it will show me the subtotaled amount.
  • 02:16 This is cool because I can drill in and out, right?
  • 02:18 I can go and click on the plus and open it up,
  • 02:20 collapse it down when I don't want look at it.
  • 02:22 But then I might look at this even more and say, yeah, but you know what,
  • 02:25 do I really want all these subtotals?
  • 02:27 Maybe I don't want subtotals for some of our categories, like wine for example.
  • 02:32 I'm going to right click on here and
  • 02:33 I'm going to choose to say don't subtotal the category.
  • 02:36 And you'll notice that my appetizers now no longer have a subtotal and
  • 02:40 my alcohol items, which are above wine, no longer have a subtotal.
  • 02:44 And yet, wine still still does.
  • 02:46 Why?
  • 02:47 Well, it's because it's collapsed.
  • 02:48 If I open it back up, notice that the subtotal disappears.
  • 02:51 It's not there anymore.
  • 02:53 And when I go back and collapse it down, the subtotal goes away.
  • 02:58 What if I want to turn the subtitles off for the entire table?
  • 03:00 Maybe I don't want it for alcohol.
  • 03:03 Once again from the pivot tables tools design tabs, I can go and say don't show
  • 03:07 sub totals at all and now you will notice that the alcohol subtotal goes away.
  • 03:11 Now I'm going to make some modifications to this a little bit more.
  • 03:13 I'm going to pull item name off the pivot table and
  • 03:16 I'm just going to go back up to the top of the worksheet.
  • 03:19 And we're going to take a look at what we can play around with on these on
  • 03:22 the subtotals as well.
  • 03:22 So I'll turn the subtotals back on.
  • 03:24 And we come over here.
  • 03:25 Pivot table tools design, we'll say show our subtotals at the bottom of the group.
  • 03:31 Right now, this is subtotaling with SUM action.
  • 03:34 So if I go and select all these guys, you'll see down the bottom here
  • 03:38 it comes to 5686.1, which is a match for the subtotal.
  • 03:41 But I'm not restricted to just using sums either.
  • 03:44 If I right click on the actual name of the category, and go to Field Settings.
  • 03:50 You'll notice that I have the ability to go with automatic subtotals,
  • 03:54 or I can go into Custom.
  • 03:56 And with Custom, I can pick multiple different subtotals that I might want for
  • 04:00 my aggregations.
  • 04:02 When I say OK, you'll notice I get my sum, my count, my average and my max.
  • 04:07 And if I want to turn that off, I can right-click on it again,
  • 04:11 go to Field Settings again and say either clear all these items or
  • 04:16 click on Automatic to go back to the base sum that I had before.
  • 04:21 So that's how we work with subtotals, with lots of different options there.
  • 04:25 Now what I want to show you is what about our grand totals.
  • 04:27 I'm going to move class over two columns just to collapse the pivot table down
  • 04:31 short in a slightly different form.
  • 04:32 And you'll notice we have a grand total down the bottom that's summing
  • 04:35 everything above it.
  • 04:36 And we have a grand total on the right hand side that's summing across.
  • 04:39 But what if I don't want to have my grand total going across?
  • 04:43 Right besides subtotals, we have grand totals, and we could say,
  • 04:47 I'd like to see this sub or a grand totals on for columns only.
  • 04:51 And this will stop it from summing across but
  • 04:53 still leave us with a grand total at the bottom.
  • 04:56 I could flip this into the other method.
  • 04:58 Go on for columns.
  • 05:00 We can go on for rows.
  • 05:02 That will actually flip at this direction.
  • 05:03 I no longer have a grand total at the bottom but I do have one sideways.
  • 05:07 Naturally, I can turn them off altogether or I can turn them back on for
  • 05:12 both as well.
  • 05:13 So not quite as many options to play with the subtotals but
  • 05:16 still lots of options to be able to make your data look exactly as you want.

Lesson notes are only available for subscribers.

Managing Power Queries
04m:39s
Choosing PivotTable Layouts
04m:53s
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