Outlining

Quick reference

• 00:04 In this video, we're gonna look at Excel's Outlining tools and
• 00:07 how they can be used to really easily hide, and then again, show,
• 00:11 different rows or columns inside a worksheet.
• 00:15 So if you look here, we have a little bit of a financial statement.
• 00:18 And what I'd like to do, is I'd like to hide off the details on the revenues and
• 00:21 just show the Gross Revenues line.
• 00:23 Because maybe the audience I wanna talk to doesn't really care about this level of
• 00:28 detail.
• 00:28 So what I'm gonna do is I'm going to highlight rows 6 through 10,
• 00:33 I'm gonna go to the Data tab, and I'm gonna click Group.
• 00:38 And you'll notice that over on the left hand margin, it expands a little bit and
• 00:41 I've got this little minus icon here with a line above it.
• 00:44 Well if I now go and
• 00:45 click that, you'll notice that it actually collapses these rows.
• 00:50 And you can see they're hidden, we can see the green bar.
• 00:52 We can see that we also have little icons change to a plus.
• 00:54 So if I click on that, it'll bring the details back.
• 00:57 And this stuff all still calculates live.
• 00:59 This is a formula, everything's working nicely.
• 01:02 But it gives me a very quick way to get rid of some of the detail I may not wanna
• 01:06 see.
• 01:06 So why don't I try the same thing again here with some of the other pieces.
• 01:10 Let's grab our Salaries here, and we'll group those.
• 01:14 And we'll grab our Supplies, and we'll group those as well.
• 01:18 And, let me see here.
• 01:19 We'll scroll down the worksheet just a little bit, and
• 01:22 we'll grab our Other Costs, and we'll group those as well.
• 01:27 And you'll notice now that I can collapse these guys down
• 01:30 by pressing the 1 up in the top here.
• 01:33 And it will collapse all levels of my grouping, so
• 01:37 that they're all going away all at once.
• 01:39 And I can click the 2, and it'll bring them all back together.
• 01:43 Well, that's kinda cool.
• 01:44 In addition, if I wanted to, I can even do nested grouping levels.
• 01:48 If you watch this now, we'll scroll down a little bit past here,
• 01:53 we'll go down to where our Other Costs is, and I'm going to group.
• 01:57 Now you'll notice again the big key here is that every row that I selected,
• 02:02 when I hit Group, it put the subtotal on the following row.
• 02:06 Okay, so at this point now, if I click 2, I've got an extra grouping level now.
• 02:12 If I click 2, it actually groups the second-level items, which is my Costs,
• 02:18 if I click 1, it'll actually collapse just to Gross Revenues and Total Cost.
• 02:25 And if I go to 3, it'll expand them all again.
• 02:28 Again, I could click on 2 and collapse them down again.
• 02:32 Now, one thing that's a little bit strange here, though, is you'll notice that as
• 02:36 I collapse certain things, watch as I collapse Gross Revenues.
• 02:40 Notice how it's got an underline on the top of it, here?
• 02:43 When I click this, it goes away.
• 02:44 Well, that's good.
• 02:46 So we can open that up again.
• 02:47 Yeah, it had a line for sure.
• 02:48 It went away.
• 02:50 If I go and expand all these guys here in my Costs section,
• 02:53 let's go and open this up for a second here.
• 02:56 You'll notice that I actually have quite a few underlines.
• 02:58 Everything looks quite consistent in the way that it's done.
• 03:02 And yet, when I go and actually collapse this section,
• 03:07 you'll notice that it has a line underneath it for some reason.
• 03:10 When I go and collapse my Supplies section, it's got a line above and below.
• 03:15 And when I collapse this one here, again, a line above and below.
• 03:17 And I'm going, man, you know, that's really not the way I wanna see this.
• 03:20 I'd like these lines to go away.
• 03:23 Well, the challenge with these things, and this is something you have to be careful
• 03:26 with with your grouping, is when you actually look at the properties of a cell,
• 03:30 we think it's got a line on the top and bottom.
• 03:32 But that's actually not true, and I'm gonna show you why.
• 03:35 Let me right-click here and say Format Cells.
• 03:38 And I'm going to clear the borders from this cell.
• 03:40 We'll say None.
• 03:42 There we go.
• 03:43 What I'm gonna do is, I'm gonna put a border on the bottom of this cell.
• 03:48 So we'll go and put a border there.
• 03:49 And we'll put a border on the top of this cell.
• 03:54 And now let's try and collapse it and see what happens.
• 03:58 They go away.
• 03:59 So we need to remember that the borders are not necessarily on the top and
• 04:04 bottom of a specific cell.
• 04:06 The borders actually reside in each cell.
• 04:09 It's not between them.
• 04:10 So you can have a little bit of control over these things.
• 04:12 And this can drive you a little bit crazy as you're working with it.
• 04:15 But the nice piece about it,
• 04:16 as I say, is that we've now got this thing able to be collapsed down very easily
• 04:21 into a different manner to show just Gross Revenues, what are our Gross Costs.
• 04:25 Or if we want to, we could collapse all the way down to this particular level and
• 04:29 send it out the door.
• 04:31 And the nice piece about using this versus just hiding rows is that it's actually
• 04:34 obvious to our users on the side, too, when they come in later,
• 04:37 they'll know that rows have been hidden.
• 04:39 That may or may not be what you want.
• 04:42 In many cases, for me,
• 04:43 it is what I want because I don't want people to lose things.
• 04:45 I want them to know that I've actually hidden something intentionally.
• 04:48 So this was Outlining in Excel

Lesson notes are only available for subscribers.