Grouping

Quick reference

• 00:04 Now, we're gonna look at a super cool technique for pivot tables,
• 00:07 which is grouping.
• 00:09 I've got about 2500 and some odd rows of data here.
• 00:12 I lost count cuz I lost interest after about 50 rows, way too much to read, so
• 00:16 I'm gonna use a pivot table to summarize it up.
• 00:19 I've already created it.
• 00:20 It's on the numbers tab here.
• 00:22 And it's just a basic pivot table,
• 00:23 like what you've been looking at through the first part of the course here.
• 00:27 So we've classes, categories showing, and we've got the sum of amount here.
• 00:32 Now, what I'd like to do though is I'd like to take a look at my table by hour,
• 00:38 and pull it the way across here, but you know, this isn't really ideal for
• 00:42 the way that I wanna see my data.
• 00:43 What I'd really like to do is break this up, this is a restaurant so
• 00:46 I'd like to see it broken up by shifts of breakfast, lunch, dinner and late night.
• 00:52 Now, normally what I would probably encourage you to do is go back to your
• 00:56 table, and insert a new row and try and, figure out, based on the hour.
• 01:00 Maybe do a V lookup to figure out what period it is, but
• 01:02 a lot of people don't like V lookup,
• 01:04 so we can do it without adding any more data to those tables at all.
• 01:08 Here's how. What
• 01:09 we're gonna do is we have to make some determination.
• 01:11 But I'm gonna say everything between the 8 o' clock and
• 01:14 10 o' clock hours is gonna be breakfast.
• 01:17 So I'm gonna select those, right click on them, and I'm gonna say Group,
• 01:20 and it comes back and it says Group One.
• 01:23 Well that's cool.
• 01:24 I can click in the pivot table and I can just go and type over the cell and
• 01:29 it gives me a label of breakfast.
• 01:31 Awesome.
• 01:32 From an eleven o'clock hour up until about the two o'clock hour we're gonna call
• 01:36 this lunch.
• 01:37 So I'm selecting these values I'm gonna right click and
• 01:40 we're gonna say group, and that gives me Group2.
• 01:43 So now we can say Lunch.
• 01:45 From the three o'clock hour up until about let's
• 01:50 say the 6 o'clock hour inclusive, we'll right click on these guys here and
• 01:54 say Group, and this guy here can be Dinner.
• 02:00 And then for our final shifts here,
• 02:02 we've got about four of them that go all the way across.
• 02:04 We'll right-click on this guy here > Group, and this one can be Late Night.
• 02:10 So you can see, I didn't have to go and
• 02:12 add a column to this thing in order to make it work, and that's kinda nice.
• 02:18 Now, what if I also wanted to do some grouping on the individual labels here?
• 02:24 Maybe I am wanted to try to pull burgers and sandwiches and soups together.
• 02:31 These guys here all look like lunch items.
• 02:34 So what I'm gonna do is grab burgers, and I'm gonna hold down my Control key,
• 02:39 and I'm gonna select the other items that I want.
• 02:42 I'll grab Food Modifiers, Sandwiches, Soups and Salads, and
• 02:47 I think that should probably just about do it.
• 02:49 I'm gonna right click and say group on these guys.
• 02:54 And you'll notice that something nasty happens.
• 02:56 Every single item looks like it's being subtotaled now, but,
• 03:01 down here, I've got my group for my lunch items.
• 03:07 So that's cool.
• 03:09 Now I need to pull together some other stuff, though.
• 03:11 Appetizers and Entrees and Desserts, we can put all those guys together.
• 03:16 So we'll right click and say, Group, and these will become my dinner items.
• 03:22 Maybe I'll just make this consistent, we'll go with breakfast items.
• 03:28 So you can see it can over-type things and labels here and
• 03:30 on the pivot table as well, non-alcoholic beverage looks fine, but
• 03:34 now of course I've got a bit of a mess up in my alcohol section too.
• 03:37 And this is one of the drawbacks when you do grouping this way,
• 03:39 is that it does tend to kind of skew things out a little bit.
• 03:42 But, we're going to grab my bottled beer, canned beer, and my coolers and
• 03:46 ciders, and I'm going to group those guys together and call them bottled
• 03:52 products and we're going to go ahead and rename this guy here, to just draft.
• 03:58 Get rid of the beer.
• 03:59 There we go, perfect.
• 04:00 So you can see now that I've got a pivot table.
• 04:03 I didn't add anything to the source columns underneath, and yet
• 04:07 I've got something that actually displays a lot nicer for me.
• 04:11 What if I wanted to group dates?
• 04:14 Well I've got similar things going on over here.
• 04:17 It looks like I'm gonna have to go and
• 04:18 update some labels in my pivot table but that's minor.
• 04:20 But what happens if I go and drag dates across my columns?
• 04:24 I've got tons of information, it's too much to read, but watch this.
• 04:29 Right click Group.
• 04:32 And we get the option to group by months and years, and I'm going to do both.
• 04:36 And you'll notice over here it's going to add some new fields to my field well for
• 04:39 me, like years.
• 04:40 And you can see now that it's actually broken things down, January and February.
• 04:46 The only other thing that I want to show you here is when we want to get rid of
• 04:49 some of these things, we could just pull years right off the pivot table.
• 04:52 That would get rid of it and leave it in my field well, or
• 04:56 I could put it back on and I could right-click and say Ungroup and
• 05:00 that would actually ungroup the damage that I've done in these particular cases.
• 05:04 I could do the same thing with my breakfast items,
• 05:06 ungroup it, the same thing with my bottled products and ungroup those.
• 05:10 So what we do, we can undo, but this gives us a real nice way to
• 05:15 actually be able to control some of the look and feel of the PivotTable.

Lesson notes are only available for subscribers.