Building Combination Line and Area Charts

Quick reference

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