Locked lesson.
About this lesson
How to group data, and add your own expandable and collapsible sections to hide and unhide portions of data.
Exercise files
Download this lesson’s exercise file.
118 Grouping data Income Statement.xlsx28.3 KB
Quick reference
Grouping Data
This functionality in Excel enables you to add your own expandable and collapsible sections to hide and unhide portions of data.
When to use
Use this when you have large datasets that you can collapse into main and even nested (or sub-) sections to decrease the amount of noise and expand to drill down into more detail.
Instructions
- Menu path: Data tab, Outline grouping, Group button (or Ungroup)
- Select the rows or columns you want to include in the section and click the Group button
- If you want nested (subsections) in a grouping, start with the lowest level (most detail) and work your way up in the hierarchy
- Note the level and expand/collapse buttons next to row and column headings
- 00:04 We've all seen datasets that are so big, it's overwhelming and
- 00:08 intimidating to look at.
- 00:10 It takes so much time to make sense of the information,
- 00:13 it actually starts to lose its value.
- 00:16 In this example, we have a data set just like that.
- 00:18 An income statement, a statement of revenue and expenses.
- 00:23 This statement is supposed to show us the net profit or loss for the year.
- 00:28 But you have to scroll all the way down to row 58 to see the net profit.
- 00:34 And to be honest, this example income statement isn't even as big as ones
- 00:38 you might have to deal with.
- 00:41 So, let's see what we can do.
- 00:43 Excel has a functionality that you can apply to cells if
- 00:46 you want to hide them from view.
- 00:48 And I'm not referring to right click and hide columns,
- 00:51 that's usually not a great idea.
- 00:54 On the Data tab, there's an outline grouping and a group icon.
- 00:59 The grouping of the data adds this outline and four dots above the columns
- 01:04 that it's hiding in a button to collapse if you want to hide the data.
- 01:10 If you want to undo it on the same outline grouping,
- 01:13 there's an ungroup button you can use to ungroup, if it's only one grouping.
- 01:18 Or if you want to clear the whole sheet, you can use clear outline.
- 01:24 So I suppose we can just ungroup this one and all is returned to normal.
- 01:29 Okay, but I do want to group those columns.
- 01:32 So I'm going to go back to the outline, hit group.
- 01:38 I also want next year's quarters grouped so that I can hide them as well.
- 01:45 You'll see on the side that it's added two level buttons.
- 01:49 The lower the level, the less the detail.
- 01:52 And the higher the level, the more detail you see.
- 01:55 So if I click on the one, it collapses everything on this level, but
- 01:59 if I click on the two, it expands everything on the same level.
- 02:03 Let's see what we can do with the rows to make this simpler.
- 02:06 I'm going to start adding levels to the rows with the most detail.
- 02:11 I'm going to group these rows under entertainment first.
- 02:14 Go to outline, hit group.
- 02:17 Now let's go to motor vehicle expenses and do the same thing.
- 02:21 Outline and group.
- 02:22 And we also have telephone and internet expenses.
- 02:26 Now I could use the group icon again but there is a shortcut.
- 02:30 If I press F4 right now or Command Y on a Mac,
- 02:33 it will repeat whatever action I did last.
- 02:37 For example, if I just deleted a row and I hit F4,
- 02:40 it will delete whatever row is selected now.
- 02:43 But the action I just performed was to add a grouping.
- 02:46 So if I press F4 or Command Y on a Mac, it will add a grouping.
- 02:53 There we go.
- 02:54 Now, let's see, level one and level two, level one is less information.
- 03:00 It hides all my blue cells.
- 03:02 Good, but this is still too much detail.
- 03:06 I want to hide all of the rows of expenses.
- 03:09 I'm fine just seeing the total of these expenses on the line that says,
- 03:13 selling general and admin expense.
- 03:16 So let's select all the rows we want to group.
- 03:20 We see up above that we've already got a level one and level two of grouping, so
- 03:24 watch what happens when we group all of these rows.
- 03:29 As you might expect, it's now added a level three.
- 03:33 On my third level, I also want to hide the other rows of detail and
- 03:38 only see the totals for each category.
- 03:42 So let's select the rows under contract revenues earned, go over,
- 03:46 click on outline, then group.
- 03:50 Same thing for cost of revenues earned, select the rows, outline and group.
- 03:56 For other income, let's use the shortcut again.
- 03:58 Let's select the two rows and click F4.
- 04:00 Now let's see what level one looks like.
- 04:04 That's nice.
- 04:05 We've got our category totals only,
- 04:08 with all the itemized rows hidden within our groups.
- 04:12 And we can also collapse the columns down to level one and
- 04:15 hide the quarters to see the annual totals.
- 04:18 And there is a nice compact income statement that makes sense and
- 04:22 I can drill down to detail anytime I want
Lesson notes are only available for subscribers.