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.xlsx195.9 KB Grouping - Completed.xlsx
200.1 KB
Quick reference
Grouping
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 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.