Building Combination Line and Area Charts

Quick reference

• 00:04 In this video, we're going to look at a very special chart that I actually
• 00:09 used extensively when I was doing different revenue versus budget
• 00:13 variances in our accounting offices.
• 00:16 Now, before we do that, though,
• 00:17 I want to call something very specific out about the data table you're seeing here.
• 00:21 If I go and click inside this thing, what you're going to notice is that my dates
• 00:25 are built on a formula that's referring to these, and
• 00:28 reconstituting it into a nice format.
• 00:30 My actual is a formula, my budget's a formula, my flexible budget is a formula.
• 00:35 All of these are reading from the data tables that are hidden under these
• 00:39 grouping layers.
• 00:40 This is the original calculations as needed to be done.
• 00:43 And then when I collapse them down, I've used a summary table to summarize
• 00:47 them all into a nice contiguous block of cells that are perfect for charting.
• 00:51 And I highly recommend you do this because it can make your life a lot easier
• 00:55 than trying to chart against non-contiguous data sets.
• 00:58 All right, now that we know how to do that, let's just go grab our data.
• 01:02 We're going to go to Insert > Recommended Chart, and
• 01:05 you'll see that Microsoft recommends a line chart, and that's good.
• 01:09 They also have column charts and area charts.
• 01:11 What they don't know is that I actually want two of these type of charts in
• 01:16 a single chart.
• 01:17 But they don't have that option here, so I'm going to choose the line chart.
• 01:22 Now, this is all good and everything else, there's only one big problem with it.
• 01:26 Let me just make this a little bit wider, so
• 01:28 our labels all lie down horizontally there, that looks good.
• 01:30 My Chart Title is not great, so let me go and say equals, and
• 01:34 we'll pick up this is our draft beer analysis and link that to the cell.
• 01:38 Now, we're not too bad a shape, but the problem with this is that I've got three
• 01:42 lines and they're all screaming for attention.
• 01:44 It's very hard to actually draw the difference between these things.
• 01:48 So what I want to do is I want to focus on actual and flexible budget.
• 01:54 Now, if you're not familiar with flexible budget,
• 01:56 what this is is this is very useful in something like a cost of sale analysis.
• 02:00 We actually reforecast our budget based on the revenue levels that we have.
• 02:05 So this is much more interesting to me than the static budget that was projected
• 02:10 when I didn't know what my revenues were going to be.
• 02:13 Now, I want to send this one to the back, so I don't have to really focus on it.
• 02:17 So I'm going to go and select the orange line here.
• 02:20 I'm going to go right click on it, and
• 02:22 I'm going to choose to Change Series Chart Type.
• 02:25 What happens now is we get taken into the custom combination builder.
• 02:30 And this is really kind of cool because we get to choose a different chart type for
• 02:34 each series, for actual, for budget, or flexible budget.
• 02:38 Now, actual and flexible, I'm happy with leaving these lines, but budget,
• 02:43 I'm going to change out to be an area.
• 02:45 Now notice, you can use columns and
• 02:46 bars, some of those might look a little bit weird.
• 02:48 Column and line charts work well together.
• 02:51 But in this case, we want area, and this is going to look really ugly,
• 02:55 just a big warning there.
• 02:56 Is that ever orange?
• 02:58 I'm going to go and say, OK.
• 03:00 Sometimes unfortunately with charting,
• 03:02 you have to make it look gross before you can make it look better.
• 03:05 So let's soften this because this is just obnoxious.
• 03:08 So I'm going to go and click on my orange block here,
• 03:12 right-click, and choose to Format Data Series.
• 03:16 I'm going to go over to the paint bucket, and right away,
• 03:18 what I'm going to do is I'm going to make sure fill is expanded.
• 03:21 I'm going to go and choose a Solid fill.
• 03:24 I'm going to to use a nice light blue fill here, so that's much less intrusive.
• 03:30 And then I'm also going to dial up the transparency to about 80%.
• 03:35 Okay, let's see how close we can get that there with the mouse.
• 03:37 Sorry, now I'm going to have to write it.
• 03:39 So there we go, it's going to be, oops, 80%, perfect.
• 03:43 Notice, we can see the grid lines through it.
• 03:45 Now, the only thing that's a little bit not good on this one is when I click away,
• 03:49 it doesn't have a very clearly defined line at the top, and
• 03:52 I need that to really make sense of this.
• 03:55 So I'm going to go back in here, and I'm going to change to use a solid line,
• 03:59 just gray is fine.
• 04:00 And now, when we click out, we can see a very solid line at the very top of this.
• 04:04 And this is important because this is actually a line chart, essentially, but
• 04:07 with some fill.
• 04:08 That top line is my budget.
• 04:11 Now, let's keep tweaking this.
• 04:13 We're going to go right click on my Format Axis here,
• 04:16 plot On tick marks to make that one a little bit wider, that looks good.
• 04:21 The most important lines for me are actual and flexible budget, and
• 04:24 actual being the most important, but it's behind my flexible budget right now.
• 04:28 So I'm going to grab my chart, go to my Chart Design > Select Data, and
• 04:33 I'm going to move Actual so that it is the last item that gets plotted.
• 04:38 So Budget's plotted first, Flexible Budget's drawn on top of that,
• 04:41 then Actual at the end.
• 04:43 There we go, we can see that our blue line is now in front of the gray, so
• 04:46 that's a little bit better.
• 04:47 And then I might go, of course, and say, select this particular series,
• 04:52 go and add some markers.
• 04:54 Let's go and use a Marker Option.
• 04:56 We'll go Built-in, and I think I'm going to go and
• 04:58 put a little dots on this one here so I can see it.
• 05:01 And then maybe we'll go and do the same thing for our gray line in the back here,
• 05:05 we'll use a different type of marker here.
• 05:07 So this one, I'll go and put, I don't know,
• 05:09 add a little star just to be a little bit different.
• 05:12 And there we go.
• 05:13 We have a very useful chart right here that allows me to focus on the difference
• 05:18 between actual and the static budget when I need to.
• 05:21 But when I don't need to look at static, the main focus is in between actual and
• 05:24 my flexible budget,
• 05:25 that something's gone seriously wrong in a couple of these places here.
• 05:29 So that is a useful chart that allows me to focus on the specific data I need to
• 05:34 see when I need to see it, and ignore it when I don't.

Lesson notes are only available for subscribers.