GoSkills
Help Sign up Share
Back to course

Grouping

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
195.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
Login to download
  • 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.

Choosing PivotTable Layouts
04m:53s
Aggregation
05m:01s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement