Locked lesson.
About this lesson
Use to group different row or column fields together for a more logical display.
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.
Grouping.xlsx188 KB Grouping - Completed.xlsx
195.6 KB
Quick reference
Topic
Grouping fields in PivotTables.
Where/when to use the technique
Use to group different row or column fields together for a more logical display.
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
Grouping Dates
- Right click the desired row or column label and choose Group
- Accept the default start and end dates or override them with your own values
- Select all the grouping levels desired
- Note that you can set your date boundaries to values that do not currently exist in your data set
Grouping Numbers
- Right click the desired row or column label and choose Group
- Accept the default lower and upper boundaries or override them with your own values
- Set the grouping increment
- Note that you can set your lower and upper boundaries to values that do not currently exist in your data set
Grouping Selected Items
- Select the row or column labels in the Pivot Table that you’d like to group
- Hint: You can select non-contiguous cells by holding the CTRL key as you click them
- Right click the selected cells and choose Group
- You will need to manually group all other cells back together once you’ve grouped your initial selection
- Change the labels by selecting the cell and typing over the generic labels that appear
Un-grouping Selected Items
- Select the grouped labels in the PivotTable
- Right click and choose Ungroup
- 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.