Outlining

Quick reference

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