Building Combination Line and Area Charts

Quick reference

• 00:04 In this video we're actually gonna unlock kind of a black magic charting
• 00:08 technique here.
• 00:09 We're gonna combine two different chart types together
• 00:13 to build the most effective story we can for our data.
• 00:16 This is kind of a neat little trick to show three different series, but
• 00:19 only have your mind focus on two of them at any one time.
• 00:23 You'll notice the spreadsheets set up that we have three series and
• 00:25 an actual budget and a flexible budget.
• 00:28 The flexible budget is the more important budget for us, because this is actually
• 00:31 the budget based on what the current revenues are that are driving actual.
• 00:35 So, that's all right we can keep that.
• 00:36 And they're all driven by some cells that are grouped and hidden here, because we
• 00:39 don't really need to see all of the calculations that are actually driving it.
• 00:44 To create the chart, we'll select our data.
• 00:45 And then we're gonna go to insert and we'll go to recommended charts.
• 00:51 And will recommend a line chart for us because there's three series of data
• 00:56 over time, so that seems like the most logical choice.
• 00:58 So we'll say okay.
• 01:01 Now we'll make some quick changes to this thing to
• 01:03 do some of the things we need to do.
• 01:04 We'll say equals and we'll link our chart source to the title that we want for
• 01:10 cost of goods sold comparisons and now we'll make the chart a little bit bigger
• 01:14 so that it's got the dates lined out the way we want.
• 01:18 We'll right click on our axis and we'll set our axis to show on tick marks.
• 01:25 To get it in the right place as well.
• 01:28 So the chart's not looking too bad now but the big question is,
• 01:30 can we do anything better?
• 01:32 So here's what we're gonna do.
• 01:34 We're gonna go and select the orange series budget.
• 01:37 This is the least important budget.
• 01:39 It's important to know what we originally budgeted, but the flexible budget that
• 01:42 reflects our current revenue trend is more important to us.
• 01:46 So we're gonna actually fade this guy into the background a little bit by doing this.
• 01:50 We're gonna right click on it and we're gonna say change series chart type.
• 01:56 And now, we can actually pick the series that we want.
• 01:59 So let's change budget to an area chart.
• 02:04 We'll just go with a regular area chart here.
• 02:07 Now you will notice that it gives us a pretty horrendous color
• 02:10 fill to begin with.
• 02:11 And you're thinking, man why would we ever do this.
• 02:13 But bear with me as we go through and we figure this one out.
• 02:16 So we'll say okay.
• 02:18 And that creates this pretty nasty thing.
• 02:20 You'll notice that its also reset the plotting on tick mark.
• 02:24 Which is a little bit irritating.
• 02:25 So let's fix that first.
• 02:27 We'll go to format axis.
• 02:29 And we'll go and choose to show on tick marks,
• 02:33 there we go, extending it to the edges.
• 02:35 And now what we'll do is we'll select our orange series here.
• 02:40 We'll go to the paint bucket and we're gonna change this to a gradient fill.
• 02:46 You'll notice when we do that, that this gets a lot lighter and it kind of fades
• 02:49 into the background, almost a little bit like a mountain or something like that.
• 02:52 So that's kinda nice.
• 02:54 The other thing that I would probably do with this is I would probably go and
• 02:57 throw a solid line on it and not in red.
• 03:00 That's a little bit too much.
• 03:01 We'll go with a nice solid blue.
• 03:03 The reason for that is because when you look at it,
• 03:06 you wanna be able to distinguish where that top line
• 03:09 actually ends because the color is sort of fading away.
• 03:12 Given this now, these other two series are problematic in the fact that they
• 03:16 are the same kind of colors.
• 03:18 So let's go in and make some changes to that as well.
• 03:21 Let's make our actual series a much darker solid line.
• 03:26 We'll make it a dark blue, so that we can really see it.
• 03:31 And our budget, we'll select that line and we'll make it a solid line.
• 03:35 But let's make this something different, maybe orange or
• 03:38 something like that that ends up going with the chart fairly well.
• 03:41 Yellow and blue tend to be complimentary colors.
• 03:45 We can go back to our dark blue line, maybe give it a marker so
• 03:48 that we can distinguish where it is and we'll go with an automatic marker.
• 03:54 We better make the fill, you can see right now the fill is a different color
• 03:58 in that marker, so we better make that a solid fill and lock it in to the same dark
• 04:02 blue we're using on the line to really make it stick there.
• 04:06 So that'll work.
• 04:08 The final thing that we would wanna do with this,
• 04:10 after dismissing this guy here, is bringing this blue line in front.
• 04:14 So once again, we select the chart.
• 04:17 We go to chart tools, design, select data.
• 04:22 And we'll just move our actual to be the last plotted series in our group.
• 04:28 And there we go.
• 04:30 The blue line is now in front of the yellow and
• 04:32 it looks like it's a pretty decent chart.
• 04:35 What I like about this chart is that this dark blue fill for the original budget,
• 04:41 we can compare our actual series against it by looking at where it is.
• 04:44 But for the rest of the time that we don't need it, where we're more focused on this
• 04:47 other series, that seems to just sort of fade into the background.
• 04:51 It kinda looks like a mountain behind it, something scenic, but
• 04:53 it doesn't really get in the way of consuming our data.

Lesson notes are only available for subscribers.