Totals and Sub-Totals

Quick reference

• 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.