Grouping

Quick reference

• 00:04 In this video, we're going to look at a super cool technique for
• 00:07 displaying things on a pivot table, which is grouping.
• 00:10 Now I have about 2,500 rows of data here, obviously, far too much to read.
• 00:15 So naturally, I'm going to pull it into a nice little pivot table,
• 00:17 which I've already built on the numbers tab over here.
• 00:20 This summarizes my class and my categories up by amount.
• 00:24 But the thing is that I'm running a restaurant here.
• 00:26 And what I'd really like to do is break this down to see it in shifts of
• 00:29 breakfast, lunch, dinner, and late night, for example.
• 00:33 The problem is, I don't have that data in my original data source.
• 00:37 Now normally, I'd probably encourage you go back to the original table.
• 00:40 Do some VLOOKUP statements or some IF statements to try and
• 00:43 actually build up those buckets.
• 00:45 But what I want to show you here is that we don't always actually need to,
• 00:47 which is kind of nice.
• 00:48 Because sometimes we can't actually manipulate the source data if it's coming,
• 00:52 say, directly from a database.
• 00:53 So what I'm going to do is I'm going to go click in my pivot table, and
• 00:57 I'm going to add the point of sale chit hour into columns.
• 01:00 And this gives us every individual hour, but
• 01:02 that's obviously broken down way too much.
• 01:05 So what I'd like to do is I'd like to actually group this up to show my data
• 01:08 in a slightly different way.
• 01:10 I'd like to take these guys here, 8 o'clock, 9 o'clock, and
• 01:14 10 o'clock hours, and I'd like to group these up to be breakfast.
• 01:17 So to do that, I'm going to select them, right-click, and
• 01:20 I'm going to choose Group.
• 01:22 Now this groups them up and gives me a new Group1, which I'm going to go and
• 01:26 just type over and call it Breakfast.
• 01:29 And there we go, I can even collapse it to get it down to just the breakfast items.
• 01:35 The next shift that I'm going to work with is my 11 o'clock to 2 o'clock hours
• 01:38 inclusive, so that's 1400 hours.
• 01:40 So I'll select all these guys here, right-click, and
• 01:42 again, we'll choose Group.
• 01:45 That gives me Group2, which I can now go and say, let's call this one, Lunch.
• 01:51 I'll collapse this guy down as well.
• 01:52 The next ones I'm going to work with is from 1300 or
• 01:55 1500 hours all the way up to 1800 hours, so that's going to take me up to 6 PM.
• 01:59 I'm going to right-click, we're going to say Group on this one here,
• 02:03 and we'll go and rename this one to be our Dinner.
• 02:07 And finally, from 7 PM, or 1900 hours, all the way through to the 2200 hours, or
• 02:11 10:00 o'clock at night, that's the last hours of business that we do here.
• 02:15 We're going to right-click and we're going to group this guy as well,
• 02:19 and we'll just call this one our Late Night shift.
• 02:22 So now we've actually got everything broken down quite nicely to see our four
• 02:26 different shifts that we actually have for our restaurant, which is pretty cool.
• 02:30 Now, the next thing that I might do though is I might go back and say, well,
• 02:33 that's neat, great that I could do this on my columns.
• 02:35 But what about on my rows?
• 02:36 Because when I look at my data here, I've got things like my appetizers and
• 02:41 my soups and salads, those look like they should probably be in a group that's
• 02:45 called something like starters.
• 02:47 So I'll grab these two guys, right-click, we'll group them.
• 02:51 And now that I have that new group down here called Group1,
• 02:54 I'm going to rename this one to be Starters.
• 02:58 I'll Enter on that, there we go.
• 03:00 Maybe I could put all of my beers together.
• 03:02 So I'll grab these two guys, hold down my Ctrl key, grab my beers there,
• 03:07 right-click, we'll group those.
• 03:09 Group2 now becomes Beer.
• 03:12 I'll leave my coolers/ciders, my liquor, my wine, alone, those ones are fine.
• 03:17 But I might want to come and play with some other pieces in here, like,
• 03:19 say, burgers, entrees.
• 03:22 Maybe I'll throw the food modifiers in here and the sandwiches.
• 03:25 All of these I can now group to become my Mains.
• 03:32 And now I can easily go and
• 03:33 say let's collapse down all the individual items here so
• 03:36 I don't have to look at all of these guys in their full blown out glory here.
• 03:40 And now I can actually see exactly what's going on,
• 03:42 looking at the individual components that I have.
• 03:45 So that's grouping some numbers, grouping some text.
• 03:48 Now, I want to show you what happens when you group dates.
• 03:51 And this also changed in Excel 2016.
• 03:53 Now, here is one of the key things you need to worry about though is notice that
• 03:56 when I move to my other pivot table,
• 03:58 the grouping levels have automatically been applied to this other pivot table.
• 04:01 Well, that's not exactly what I want here.
• 04:02 So I'm going to go on and I'm going to say, let's show our field list.
• 04:07 And you'll notice now that in my field list,
• 04:09 I have Class, Category2, and Category.
• 04:11 It's added a whole new grouping level called Category2.
• 04:15 If I don't want that on this pivot table, I'm going to drag it off.
• 04:18 And this will bring me back to the original view that I had here.
• 04:21 So if I go back to numbers, everything's looking nicely grouped.
• 04:23 If I come to dates, it looks like this.
• 04:26 What I'm going to do now is I'm going to drag date onto columns.
• 04:30 And you'll notice that it actually summarizes it by January and February.
• 04:33 And if I open this up, it shows me the individual days.
• 04:36 This is an automatic grouping feature that was introduced with Excel 2016.
• 04:40 It's not saying that you necessarily have to keep it this way though.
• 04:45 You can also right-click on any existing group, the ones that we did in
• 04:48 the previous pivot table as well, and you can modify the grouping levels.
• 04:53 So in this case, maybe I say, you know what, I want to go with years, months, and
• 04:57 days.
• 04:57 And what'll happen now is that when I say OK, you'll notice in my field list,
• 05:02 I have a years field, a months field, and also the POSChitDate.
• 05:05 Now, POSChitDate was the name of the original field that I had.
• 05:10 Whatever the lowest level of granularity I have, it's going to keep that field,
• 05:14 the rest will be added to the pivot table as new items.
• 05:18 So now I could turn around and say, hey, I don't want to see the POSChitDate,
• 05:21 I'll pull it off.
• 05:22 It keeps it in the field list, so
• 05:23 it's still there if I want to use it somewhere else.
• 05:26 But now we're down to just the items that we actually want to have.
• 05:29 So that's how we can actually take control of dates and text, all kinds of things
• 05:33 with grouping on a pivot table to really make it look the way we want.
• 05:37 And finally, what if you make a mistake and you need to ungroup things and
• 05:40 start over?
• 05:40 To do that, it's quite simple, right-click on one of the grouped items,
• 05:45 choose Ungroup, and now you can start the whole process all over again.

Lesson notes are only available for subscribers.