Locked lesson.
About this lesson
When you want to graph 3 line series, but only want 2 in focus most of the time.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Building Combination Line and Area Charts.xlsx23.2 KB Building Combination Line and Area Charts - Completed.xlsx
28.6 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 --> Fill (min toolbar) --> Gradient --> First light gradient
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
- Go to Chart Tools --> Design --> Select Data
- Select the Actual series and click the Move Down icon
- 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.