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.
Building Combination Line and Area Charts.xlsx22.1 KB Building Combination Line and Area Charts - Completed.xlsx
27.8 KB
Quick reference
Topic
Creating line and area combination charts
Where/when to use the technique
When you want to graph 3 line series, but only want 2 in focus most of the time.
Instructions
Creating a Line chart
- Select cells B28:N31
- Go to Insert --> Recommended Charts --> select the first recommended chart
Enhancing the Chart context
- Select the Chart Title --> Press = --> Select cell B28 --> Enter
- Widen the chart so that the dates lie flat
Add the Combination effect
- Right click the Budget series --> Change Series Chart Type
- Change the Budget series to the first Area chart type --> Click OK
- Right click the Budget fill area --> Format Data Series --> Bucket icon --> Gradient fill
Add colour to the line elements
- Right click the Actual series --> Format Data Series
- Click the bucket icon in the taskbar --> Line --> Color --> Dark Blue
- Click the bucket icon in the taskbar
- --> Marker --> Marker options --> Built-in --> Type --> Circle
- --> Fill --> Solid Fill --> Color --> Dark Blue (same as the line colour)
- --> Border --> Solid line --> Color --> Dark Blue (same as the line colour)
- Right click the Flexible Budget series --> Format Data Series
- Click the bucket icon in the taskbar --> Line --> Color --> Dark Yellow
Fine tuning the elements
- Right click the date axis --> Format axis
- Set Axis Position --> On tick marks
- 00:04 Sometimes a single style chart won't do us.
- 00:08 And that's where we can reach into some real black magic charting.
- 00:10 The data that we're looking out here actually has the three series of
- 00:13 data that's running across it.
- 00:15 One of them is a little bit special.
- 00:17 This is a cost of goods sold budget is what's actually happening here.
- 00:21 So, somebody's gone through and
- 00:22 they've budgeted the amount of revenue that they're expecting to make.
- 00:25 And there's two different budgets for the costs that are coming through here.
- 00:28 The first one is a static budget, which is just labeled as budget.
- 00:31 And that's where the accountant will take what the projected revenue is,
- 00:34 what the projected cost of sales percentage is and come up with here's what
- 00:38 we believe if we hit our revenue target our cost of sales should be.
- 00:42 The second one is a flexible budget and what that does is it actually looks at
- 00:46 the actual revenue and multiplies it by the cost of sales percentages.
- 00:50 Just to say based on the actual revenue we made.
- 00:54 This is what we believe our cost of sales should actually be.
- 00:56 So it's two different measures to try and figure out what's going on.
- 01:00 So in this case, we're actually comparing the actual cost of goods sold
- 01:03 versus the static budget that was made before revenue was known.
- 01:07 Versus the cost of goods sold that we should have now
- 01:09 that we actually know what our revenue is.
- 01:12 Now the challenge with this one is that we could do a line chart but
- 01:16 some things kinda fall sort of amongst the way by doing that.
- 01:19 So we're gonna go into a little bit of black magic stuff here and I'm gonna show
- 01:21 you how to create a combination chart that combines two chart types together.
- 01:26 To do that, we start off first by going to our recommended chart, and
- 01:30 we'll start off with nice-looking little line chart.
- 01:34 So that's all good and wonderful and everything else, but
- 01:37 we kinda like this to be a little different.
- 01:40 So what we're gonna do is we're gonna select the Budget Series,
- 01:43 because while this is interesting the Flexible Budget is much more important.
- 01:48 So I'd like to make the Static Budget kind of go in the background a little bit.
- 01:52 To do this I've selected the series.
- 01:54 I'm now gonna go to Chart Design, I'm gonna go to Change Chart Type and
- 01:59 I'm gonna choose an Area Chart.
- 02:02 I'll take the first one.
- 02:05 And you might be thinking now holy cow,
- 02:06 Ken I thought you said you wanted this a background and this is subtle.
- 02:09 This is not subtle, I would definitely agree.
- 02:11 But notice that we've got two different type of charts put together here.
- 02:15 We've got two series of line charts and
- 02:17 this garishly orange background is an area chart.
- 02:20 So two different types.
- 02:21 So let's grab this guy here and we'll right-click.
- 02:25 And let's do a format on our data series here and
- 02:27 we'll very quickly get rid of that nasty orange.
- 02:29 We'll click on the bucket.
- 02:30 We're gonna go choose a gradient fill and that works.
- 02:33 It's nice and light and in the background.
- 02:36 At this point we're gonna start playing around with the different things that we
- 02:39 might need to do,
- 02:40 in order to customize this chart to make it look a little bit better here.
- 02:43 So, things like making our legend a little bit more
- 02:47 readable by having a larger font size.
- 02:49 That's one that we'll take care of really quickly here.
- 02:52 So, go and make those guys set up.
- 02:54 And we'll do the same thing with the legend as well.
- 02:57 I'm also gonna expand the width here.
- 03:00 So we'll go in to format access, and we'll go to our options and
- 03:06 we will plot on tick marks to get rid of that white space at the end.
- 03:12 Now the next thing that I may wanna do here is I may want to go through and
- 03:15 actually make these series a little bit more stand out and what not.
- 03:21 So let's go through and set our actual series here to a thicker line.
- 03:27 So we'll go with a width, we'll go out to say three.
- 03:31 And maybe we'll use a nice dark blue so that we can really see it.
- 03:37 And then our flexible budget.
- 03:38 We could go into the background here and say, well you know what.
- 03:41 Let's go and we'll put this guy here at a three point as well.
- 03:46 But maybe we'll choose a slightly different color.
- 03:48 I guess we could go with gray, we could.
- 03:50 Yeah sure, let's go with a dark gray so we can, nah, it's a little bit too similar.
- 03:55 Maybe we won't.
- 03:55 Maybe we'll go with a light green.
- 03:57 How about that, there we go.
- 03:58 So we can see what's going on in these guys here now.
- 04:03 Once again the other thing that we may wanna do is select our line,
- 04:06 maybe put some markers on it.
- 04:08 So we'll go and choose our marker option and
- 04:10 say that we're gonna go with an automatic marker.
- 04:13 And that will give a little bit more to help, although,
- 04:16 to be honest with you I'm not really a big fan of these markers.
- 04:20 They're the wrong color.
- 04:20 So I'll go back in and I'll modify my markers again.
- 04:23 So you know that marker option is great, but
- 04:27 the fill color needs to be a solid fill and
- 04:30 I'd really rather have this in the same dark blue as we had previously.
- 04:35 Maybe it's even a little bit subtle too, just size-wise.
- 04:38 So we'll go with our round one and jack the size up to a seven or an eight.
- 04:43 So that we can see it a little bit better, and that somewhat helps out.
- 04:47 So the nice thing about this chart here is that
- 04:50 the static budget sort of fades into the background.
- 04:52 It looks like a little bit of a mountain, but
- 04:54 the two series that I'm really focused on are the ones that are out in front here.
- 04:58 So as I'm looking at this chart.
- 04:59 These are the ones that jump into view mostly.
- 05:02 And yet, the other one is in the background if I need it.
- 05:04 So this is a kind of a cool looking little chart that you can play around with
- 05:07 to get some really good insights fading one series into the background.
- 05:11 And yet it's still there when you do need it but it doesn't distort and
- 05:14 confuse the picture by having too much data when you don't need to read it.
Lesson notes are only available for subscribers.