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.xlsx12.6 KB Building Combination Line and Area Charts - Completed.xlsx
15.5 KB Building Combination Line and Area Charts - Extra Practice.xlsx
28.3 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 --> Charts --> Line --> select the first line chart
Enhancing the Chart context
- Right click the Legend --> Format Legend --> Show at Bottom --> OK
- Chart Tools --> Layout --> Chart Title --> Above Chart
- 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
- Click Area --> Change the selection to the left-most Area chart --> OK
- Right click the Budget fill area --> Fill (mini toolbar) --> Gradient --> First light gradient
Add color to the line elements
- Right click the Actual series --> Format Data Series
- --> Line Color --> Solid Color --> Dark Blue
- --> Marker Options --> Built-in --> Type --> Circle
- --> Marker Fill --> Solid Fill --> Color --> Dark Blue (same as the line color)
- --> Marker Line Color --> Solid line --> Color --> Dark Blue --> Close
- Right click the Flexible Budget series --> Format Data Series
- --> Line Color --> Solid Line --> Color --> Dark Yellow --> Close
Fine tuning the elements
- Right click the date axis --> Format axis --> Axis Options --> Position Axis --> On tick marks --> Close
- Go to Chart Tools --> Design --> Select Data
- Select the Actual series and click the Move Down icon
- 00:04 In this video, we're going to look at some black magic charting techniques and
- 00:08 built combination charts.
- 00:10 Now, one of the things you may run into at some point in your career with Excel is
- 00:14 this crazy combination of information that you've got to generate in order to be able
- 00:18 to feed into a chart.
- 00:19 Lots of analysis to even get there.
- 00:22 You'll notice this as I scroll down this particular area.
- 00:25 In rows 27 through 31,
- 00:27 I've actually recapped all the stuff above into a nice and
- 00:32 easy source to use for my chart, and this is something I highly recommend you do.
- 00:36 If you're gonna build any charts, is actually make
- 00:39 a recap table that is specific to feeding the exact chart that you're working on.
- 00:44 So now that we have that, let's build a pretty cool chart here.
- 00:47 We'll select our series of data, as we normally would.
- 00:51 We'll go to the insert menu, and we'll choose to insert a line chart and,
- 00:55 like normal, the top left one for a 2D line.
- 00:59 And it gives us this nice three-line chart that looks,
- 01:02 well it's not nice it's pretty horrendous.
- 01:03 So, let's do the normal kind of formatting that we generally do.
- 01:07 We'll grab the legend and we'll go layout.
- 01:10 And we'll put the legend down below the chart.
- 01:14 And we'll add a chart title above the chart.
- 01:18 And while the selection handles are around it we'll say equals, and
- 01:22 we'll link it back to cell B 28 so
- 01:23 that our chart reads out that it's a cost of goods sold comparison.
- 01:28 We're also gonna go and soften off some of the stuff that we don't usually like.
- 01:32 I'm gonna get rid of this vertical axis and tick marks here.
- 01:35 So, we'll right-click and say Format.
- 01:37 Whoops! Missed it.
- 01:38 I'll grab the actual axis and say Format Axis.
- 01:43 We will remove the tick marks, so none, and
- 01:48 we will remove the line color to no line there.
- 01:51 So now when we go back and we loo at,
- 01:53 the different areas we've removed that excess ink that's not required.
- 01:58 The other thing I'm going to do is I'm going to click on the grid lines here.
- 02:01 And change the line style to a dash type just to soften it again to make sure that
- 02:05 we don't have extra ink that's in our way all the time when we're looking at things.
- 02:09 Now, move the chart out so we can see it a little bit.
- 02:13 We'll also expand the boundaries on the chart a little bit.
- 02:15 Make sure that those labels are lying horizontal sort of
- 02:18 a little bit easier to read.
- 02:21 Okay.
- 02:22 Now, the next piece that I wanna do is, I wanna try and
- 02:25 figure out a way to make this thing not look quite so cluttered.
- 02:29 The most important numbers on this chart to me are the actual and
- 02:33 the Flexible Budget.
- 02:35 And what a Flexible Budget is is it's where we've taken our
- 02:38 budgeted cost of goods sold and we've made it
- 02:41 relative to the actual revenue that we've received instead of having.
- 02:46 So, so basically what this would mean is if we had actual revenue that we budgeted
- 02:50 of $10,000, but our revenue came in at $5,000,
- 02:52 we would expect to have only half the cost to consult.
- 02:55 So that's what the flexible budget is.
- 02:58 So, budget is interesting, you can see it's really high here, and
- 03:01 our actual is really low, and out flexible budget was lower here.
- 03:04 It's. Interesting but
- 03:05 it's not what I really wanna focus on.
- 03:07 So I'd like to kind of push this to the background a little bit.
- 03:10 So the way I'm gonna do this is I'm gonna use a combination charting
- 03:13 technique to make it happen.
- 03:14 I'm gonna select the orange line.
- 03:17 I'm gonna right-click, and I'm gonna say Change Series Chart Type.
- 03:21 And this is cool because you can do this for just one series on the chart.
- 03:26 So, we'll go and grab the area chart.
- 03:29 And we're going to move it over here where I don't want a stacked one,
- 03:31 we're going to put this in behind and say OK.
- 03:34 And yikes, that's awful.
- 03:36 Really dense and orange, don't like that.
- 03:38 Let's soften it.
- 03:40 So while we're selected on the orange fill,
- 03:42 you can see I've still got my format data series tab up here.
- 03:45 We'll go to fill.
- 03:47 Let's go to gradient fills.
- 03:48 Hey, that looks much better.
- 03:50 Much less, garish.
- 03:51 And, as a matter of fact, I think I'm just going to stick with that one right now.
- 03:56 One thing I missed doing earlier,
- 03:59 is that I've got extra white space on the outside here.
- 04:01 So I'm gonna click on my axis.
- 04:03 I'm gonna change my, position my axis on the tick marks to stretch it out,
- 04:06 make it look a little bit more full and complete.
- 04:09 And now the last thing that, or last couple of things we really need to do
- 04:12 is just really work on the lines that I have for
- 04:14 my actual revenue in my flexible budget.
- 04:17 I'm gonna make my actual revenue line much darker.
- 04:22 So, we're gonna change the line series to a solid line color.
- 04:27 And I'm going to set this to a really dark blue, so that it's very obvious.
- 04:31 And I'm gonna throw some marker options on here for a built in dot, of a marker.
- 04:38 I'm also going to change the marker fill color, I think,
- 04:41 instead of this light blue to make something that matches the line.
- 04:44 So we'll go with solid fill, and we'll go with the same dark blue.
- 04:48 So that's gonna look a little bit better.
- 04:50 The last thing I'm gonna do is I'm gonna select the flexible budge line.
- 04:54 And I'm gonna change the color on this guy as well because that's one a little
- 04:57 but I dunno.
- 04:58 It just doesn't seem to be standing out.
- 05:01 So, we'll change the line color to a solid line.
- 05:04 And why don't we make this line something like orange.
- 05:07 Orange and blue work quite well to complement each other.
- 05:12 The one other thing that I'm really not a big fan on this is that the yellow line is
- 05:16 drawn on top of the blue line so I'm very quickly going to reorder this chart.
- 05:20 We'll close the Format Chart Area window so that we can do it, and
- 05:24 we'll go up to Design, Select Data.
- 05:29 And we'll move the actual down, below the budget and the flexible budget, so
- 05:34 that now the blue line is in front.
- 05:36 I love this chart, we use these all the time in, in food and
- 05:39 beverage comparisons, because that static budget sort of fades to the back.
- 05:44 It's there if you need it, but it's not taking your attention away all the time.
- 05:47 The mean time, those two lines really pop out.
- 05:50 And those are the things that we generally focus on.
- 05:52 So there's a little black magic of charting for
- 05:54 you, is how to build a combination line chart and area chart to build
- 05:58 an effective chart to show three series but only two at a time in a way.
Lesson notes are only available for subscribers.