Locked lesson.
About this lesson
When you want to graph 3 line series, but only want 2 in focus most of the time.
Exercise files
Download this lesson’s related exercise files.
Combo Charts - Begin.xlsx33.9 KB Combo Charts - Complete.xlsx
40 KB
Quick reference
Building Combination Line and Area Charts
Creating line and area combination charts.
When to use
When you want to graph 3 line series, but only want 2 in focus most of the time.
Instructions
Creating a Line chart
- Select your source data
- Go to Insert --> Recommended Charts --> select one of the line charts
Enhancing the Chart context
- Optional: Select the Chart Title --> Press = --> Select the cell that holds your chart’s title
- Widen the chart so that the axis displays properly
Add the Combination effect
- Right-click one of the series --> Change Series Chart Type
- Reconfigure the chart so that the desired series is showing as an area chart
- Format the area’s fill to use a lighter color with transparency
Modifying line elements
- Right-click one of the line series --> Format Data Series
- Click the bucket icon in the taskbar --> Line --> Color --> choose your preferred color
- Click the bucket icon in the taskbar
- --> Marker --> Marker options --> Built-in --> Type --> Circle
- --> Fill --> Solid Fill --> Color --> choose your preferred color
- --> Border --> Solid line --> Color --> choose your preferred color
Other recommended fine-tuning techniques
- Right click the date axis --> Format axis --> Set Axis Position --> On tick marks
- Go to Chart Tools --> Design --> Select Data --> use the arrows to change the order of the lines drawn on the chart
- 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.