Locked lesson.
About this lesson
Learn about adding and working with Excel’s outlining tools to quickly hide irrelevant sections of your worksheet.
Exercise files
Download this lesson’s related exercise files.
Outlining - Begin.xlsx24.4 KB Outlining - Complete.xlsx
24.5 KB
Quick reference
Outlining
Adding and working with Excel’s outlining tools to quickly hide irrelevant sections of your document.
When to use
Outlining tools are fantastic for hiding and showing key sections of your document very quickly. This lets you compress pieces you don’t need in order to focus on the document pieces that you do need to see.
Instructions
Adding Outlining
- Select rows 6:10 and go to Data > Group > Group
- Notice that there is now a compression line at row 11 (the row AFTER your selection)
- Click the – button to hide the rows, and the + button to re-expand them
- Apply outlining to rows 14:18, 20:25 and 27:32 (compression lines on 19,26,33)
Creating nested outlining
- Select rows 13:33, go to Data > Group > Group
- Click the 1 in the top left margin and it collapses to just Gross Revenues and Total Costs
- Click the 3 and it expands all rows
- Click the 2 and it expands the Revenues and Costs section, but not the cost details
Dealing with border issues
- Notice cells B26 and B33 have borders top and bottom, but B19 only has a border on the bottom
- Click the 3 to expand all rows
- B19, B26, and B33 all have borders top and bottom!
- Select B26 and clear the border
- Select B25 and place a bottom border, then B27 and place a top border
- Clear borders from B33
- Select B32 and place a bottom border
- Select B33 and place a bottom border (we want a bottom border when B33 and B34 are showing
- Collapse to level 2 and notice the borders have gone away
Key Takeaway
- Borders just look like they’re between cells. They are actually on the tops and bottoms of specific cells which get hidden/shown when Outlining is used.
- 00:04 In this video, I want to show you a cool tool that we can use to dynamically
- 00:07 collapse certain sections of a report so
- 00:09 that we don't have to look at all the detail all the time.
- 00:11 So here's how this is going to work.
- 00:15 I'm going to go and select the revenue section that has my revenues header and
- 00:20 my detail lines.
- 00:21 But I'm not going to select gross revenues here.
- 00:23 And what I want to do is I want to collapse these down so
- 00:26 I'm just looking at the gross revenue line.
- 00:28 To do that, we're going to leverage Excel's outlining tools.
- 00:31 Which you can find over here on the data tab in the outlined group.
- 00:35 I'm now going to choose to group these particular rows.
- 00:38 And you'll notice that we get a nice little collapse icon over the side here
- 00:41 where we can collapse those down which just hides them.
- 00:43 But it leaves a nice little plus so that we can go and
- 00:46 expand it back when we need it later, which is pretty awesome.
- 00:50 We just collapse out again and get out of the way for right now.
- 00:52 All right, I'm going to go and
- 00:54 start collapsing down some sections within my costs as well.
- 00:57 So I'm going to grab salaries,
- 00:59 I'm grabbing from the header all the way down to the last cost item,
- 01:03 but I'm not grabbing the salaries total, because I want this line to show up.
- 01:08 And what you'll notice is when I go to group this, whatever rows I've selected,
- 01:12 it always puts the grouping level on the next line.
- 01:15 So now if I collapse that down, what I see is Salaries.
- 01:19 So if I want to hide the entire supply section,
- 01:22 I'm going to grab this blank row above it, the header, all the data, and group it.
- 01:26 And then likewise for other costs, I'm going to grab the blank row all the way
- 01:31 down to the last data line and group this as well.
- 01:35 And at this point, what I can do is I can collapse these down, and we can see that
- 01:39 we've got our cost section with salary, supplies and other costs and their totals.
- 01:44 But there is one thing that I'm really not loving about this, and
- 01:48 that is that underlines did not go away.
- 01:50 And this is where things are going to start to get really, really finicky.
- 01:54 Because what you may or may not realize is that those lines are not between cells.
- 01:59 They are either at the top of a cell or at the bottom of a cell.
- 02:02 And we need to make sure that the cell that gets hidden takes its lines
- 02:06 with them.
- 02:07 And this is the tricky part.
- 02:09 So let's go and expand everything.
- 02:10 We can do that by clicking on the highest level of outlining here.
- 02:14 So number two, and that opens everything up.
- 02:17 I'm happy with the revenue section because the line is going away, so
- 02:21 that looks good.
- 02:22 But this whole section here, what I'm going to do is I'm actually going to go
- 02:26 and wipe out all of the lines completely.
- 02:28 Now, when I look at an individual section,
- 02:31 I find it helpful to start at the bottom and move up because if you start applying
- 02:36 lines to a section that already has lines, it tends to mess them up a little bit.
- 02:41 So I'm going to look first at the line below my salaries total here.
- 02:46 This is a line that is in the supplies area.
- 02:50 So when I collapse this, do I want this line to stay or do I not?
- 02:52 And in this particular case, the answer is, yeah,
- 02:55 I probably do because it's a subtotal line.
- 02:58 So what I'm going to do is I'm going to go and choose to put on a top border.
- 03:02 Meanwhile, if I come and look at the St. Helena Hospital,
- 03:06 if I collapse all of this information, I want this line to go away.
- 03:10 So on this one, I'm going to put on a bottom border.
- 03:13 And what you see is that if I collapse this down right now,
- 03:16 I get 79,007 underlined.
- 03:18 But my top underline has gone, so that's not so bad.
- 03:21 Let's try the same thing here.
- 03:24 We're going to go with a line above so top border and below and
- 03:27 a bottom border on top.
- 03:29 And now if I go and collapse these things,
- 03:32 what you'll see is when I collapse this section, this line will also go away.
- 03:37 So there we go, it's gone.
- 03:38 I still have one under supplies, but that's okay.
- 03:42 Now, this section gets trickier, Total Costs,
- 03:46 I definitely want a line below that.
- 03:49 If I go and take a look at my total costs I want my line to be above this one
- 03:53 because if all of these things are collapsed I want an underline there.
- 03:57 And then for
- 03:58 my other costs, I'm actually going to put a line at the bottom of the 417.
- 04:03 And this is a tricky thing that you need to experiment with.
- 04:06 I generally find that wiping all the lines out makes it a lot easier to get
- 04:11 this right.
- 04:11 Finally, the last thing that I want to show you here is we can also expand this
- 04:16 and if I want to apply multiple grouping levels on top of things, I can do that.
- 04:21 So if I want to come all the way down to the very last row here, and
- 04:25 I decide that hey, you know what, let's go to our data tab here and choose Group.
- 04:31 I can group it right down to net profit.
- 04:33 I could also come in here and say let's put a group on these lines for costs.
- 04:38 Here we go, and group this, I now have four levels of grouping here.
- 04:42 If I go and select on one, it'll take me right down to net profit, or loss, so,
- 04:47 there we go, that looks pretty good.
- 04:49 If I go to two, I get a statement that shows my gross revenues and costs.
- 04:52 If I go to three, it opens up my revenues, and my cost profile.
- 04:56 And if I go to four, it opens up everything.
- 04:59 So that's a kind of a neat little tool that we can use to
- 05:04 dynamically drill, in or out of sections.
- 05:07 And the nice thing is you don't have to actually keep them at
- 05:12 consistent levels either.
Lesson notes are only available for subscribers.