Building Combination Line and Area Charts

Quick reference

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