Grouping Data: Create Your Own Collapsible and Extendible Sections

Quick reference

• 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.