Outlining

Quick reference

• 00:04 One of the challenges we have with Excel is that we often try to present too
• 00:09 much information.
• 00:10 And when you look at values like this that are summing the individual detail lines.
• 00:14 The problem is that not everybody wants to see all the detail.
• 00:17 So it'd be nice if there was a way to collapse this information quickly.
• 00:21 And yet still leave a trace for the user so
• 00:23 they can drill back in if they need to see it.
• 00:25 And for that reason we actually have this really cool feature.
• 00:28 Where we can take these rows and collapse them and it's called outlining.
• 00:33 Specifically, from our outlining tools,
• 00:36 which are probably not collapsed like mine are on your ribbon.
• 00:39 You'll find group and when I group this,
• 00:42 notice that it adds a new little architecture with a 1, 2 over here.
• 00:46 And it gives me the band of areas that have been selected
• 00:49 with a plus minus button.
• 00:51 Notice that all the rows that I selected.
• 00:53 Fit into what is going to be the collapsible area, and
• 00:56 the row immediately below has the navigation piece.
• 00:59 Which, when I click it, will actually go and collapse the information.
• 01:05 Even better, it leaves a trace behind so the user knows that there's
• 01:07 something there they can drill into more collapse, which is pretty useful.
• 01:12 Now, I'd like to apply some more group into this.
• 01:15 We're gonna go and take salaries, we're gonna take the entire salaries area up to
• 01:18 the sum total and I'm going to group it.
• 01:21 And then I'm gonna grab supplies from everything from the line immediately
• 01:25 before the supply area and I'm gonna go.
• 01:28 And group that too, as well as my other costs which I'm gonna roll
• 01:33 down to pick up everything up to the other costs sub heading and group.
• 01:39 And now, what you can see is that I can make use of the 1, 2 area.
• 01:43 One will collapse to some heading level 1.
• 01:46 And at this point I've got my gross revenues and my individual cost,
• 01:49 total cost and my net profit loss.
• 01:52 If I wanna see everything, click on the two, it all opens up.
• 01:56 That one key around this though that I'm not thrilled with.
• 02:00 The lines under here and
• 02:01 this is the hardest thing to get right with some totals.
• 02:05 The challenge here that we are working with if you look at for
• 02:08 example the 79007 and I'm just gonna go and
• 02:11 expand this because I want you to notice that there is no line on the top of this.
• 02:15 And yet when I expand everything there is and
• 02:19 the reason being these lines do not exist between cells.
• 02:22 They actually exist on the top and bottom of cells and
• 02:26 this is the hard part about getting right.
• 02:27 And sometimes it takes multiple instances of working with this to actually get these
• 02:31 lines to stick the way that you want which is very frustrating.
• 02:34 So here's what I'm gonna do.
• 02:36 I'm gonna go and take all of this information here.
• 02:40 And I'm going to go and say, Home.
• 02:41 And I'm gonna clear all of these borders.
• 02:45 No Border.
• 02:46 And then, what I'm gonna do is, I'm gonna go and say,
• 02:48 well, when I collapse it now, it looks better.
• 02:52 But, unfortunately, when I go and say, let's open this up,
• 02:55 I don't have the lines that I need.
• 02:58 So let's go and
• 02:58 grab this guy here and we'll put the line at the bottom of the St. Helena Hospital.
• 03:03 Why?
• 03:04 Well, because when this row gets collapsed,
• 03:06 that line should go away which it does.
• 03:10 What lines gonna get collapse if I collapse the next section?
• 03:13 It's not this one.
• 03:14 It's actually this line here.
• 03:16 So we're gonna go and say let's put the Top Border on cell B22 and likewise St.
• 03:22 Helena Hospital here is gonna get a bottom border.
• 03:25 So at this point when I collapse those, those two lines should go away.
• 03:30 For my supplies, again, I'll put the border underneath.
• 03:35 And for St Helena, I'll put the border here on the bottom.
• 03:40 And at this point when I collapse that, hopefully it plays nice as well.
• 03:44 As I say, sometimes it can take multiple instances to get this right,
• 03:47 and you have to work on a very granular basis all the way through it.
• 03:51 Now, at this point, this looks cool, but my costs are still broken down.
• 03:55 Can I go even bigger on this one and say let's collapse my gross revenues and
• 04:00 my total cost so that I just have three lines?
• 04:03 Well, sure I can.
• 04:04 Even though there's grouping levels here, and
• 04:06 this will work whether they're expanded or collapsed, it doesn't matter.
• 04:09 I can actually grab all of this information.
• 04:12 Now, I'm gonna expand it, cuz I want you to see exactly what's happening here.
• 04:15 I've selected all of this information, so total cost is my last row.
• 04:19 But you'll notice that I've got,
• 04:20 from the blank row immediately below gross revenues.
• 04:23 What I'm gonna do is go to Data, and we're gonna choose Outline, and Group.
• 04:28 And I now get an extra level of grouping.
• 04:31 So if I now go and say, let's go to the top here and click on the two.
• 04:34 It will collapse my cross sections down.
• 04:37 And clicking on the one will actually go and collapse my next section down.
• 04:43 Now, this point I now need to try and
• 04:44 figure out how I'm gonna play with these particular guys.
• 04:47 So if I open this up and open this up, It doesn't look like I have a line on
• 04:51 the bottom of Gross Revenues, it must be a total cost line that's playing around.
• 04:55 So at this point, I would go and say Home.
• 04:58 Let's go wipe out the borders on this cell here.
• 05:02 And now, we'll come back and say, let's put a bottom or a top border here.
• 05:07 And we'll put a bottom border on our other Costs.
• 05:11 And now, hopefully, everything
• 05:16 collapses and looks very good.

Lesson notes are only available for subscribers.