Building Combination Line and Area Charts

Quick reference

• 00:02 In this video, we're gonna look at some black magic charting techniques and
• 00:07 build combination charts.
• 00:09 Now one of the things you may run into at some point in your career with Excel,
• 00:13 is this crazy combination of information that you've gotta generate in order to be
• 00:18 able to feed into a chart but lots of analysis to even get there.
• 00:21 You'll notice is that as I scroll down this particular area,
• 00:25 in rows 27 through 31, I've actually recapped all the stuff above
• 00:30 into a nice and easy source to use for my chart.
• 00:33 And this is something that I highly recommend you do.
• 00:35 If you're gonna build any charts, is actually make a recap table that is
• 00:40 specific to feeding the exact chart that you're working on.
• 00:44 So now that we've done that, why don't we build a pretty cool chart here.
• 00:47 The first thing of course that we need to do is we need to start with a chart, so
• 00:52 we're going to grab our series of data and we're gonna go to Insert and
• 00:55 we're gonna start with a regular line chart.
• 00:58 So it's a three series line chart.
• 01:00 And this isn't too bad to start with.
• 01:02 We're going to make a few modifications to it.
• 01:04 Well actually, you know what,
• 01:05 all we're really gonna do right now is we're just going to link the title.
• 01:07 So I've selected the Title, we'll say Equals.
• 01:10 And we'll link it back to cell B28.
• 01:12 So that our chart actually reads that it is a cost of goods sold comparisons chart.
• 01:17 The other thing I might do actually, just widen it a little bit so
• 01:20 I can see a little bit more of the the data labels on the, the bottom for this.
• 01:24 Now the most important piece of information, two most important pieces of
• 01:28 information on this chart, to me, are the Actual and the Flexible Budget series.
• 01:33 And basically what a Flexible Budget is, if you consider that our original budget
• 01:38 was set assuming that we would make certain revenue targets, and
• 01:42 this is a cost of sales chart.
• 01:43 So what happens with the Flexible Budget is we actually say well,
• 01:47 if the revenue was budgeted at $10,000 but we only achieved $5000,
• 01:52 then the cost of sales shouldn't really be $5000, it should be $2500.
• 01:58 It should be half because the sales were only half, so
• 02:01 that's what a Flexible Budget is where we actually return the flexed budget based on
• 02:06 the Actual revenue.
• 02:07 So for me, when I'm looking at this chart,
• 02:09 the really important things to compare is the Actual Flexible Budget but
• 02:12 sometimes we get asked about the Original, so we need to know about it.
• 02:16 So, I don't really wanna focus on this all the time.
• 02:19 It kind of clutters my chart, I'd really rather have this fade into the background.
• 02:23 So here's what we're gonna do.
• 02:24 I've selected this particular series and now what I'm gonna do is I'm gonna
• 02:29 right-click on that series and say Change Series Chart Type.
• 02:33 And you'll notice that down in the bottom here,
• 02:36 I have the ability to choose different chart types.
• 02:40 I'm not bound to stuck or stuck with just line charts,
• 02:44 I can actually put in an area chart for one of my series.
• 02:48 So that's pretty cool.
• 02:49 Now to be fair, when I say OK, it gives me this pretty horrible garish orange.
• 02:55 And that's way too much, so now we gotta go and do some work to soften this up.
• 02:59 So I'm gonna right-click on this guy, and take a look at the Fill options here.
• 03:03 Here, hey, there's some Gradient fills and, beautiful,
• 03:06 they support live previews.
• 03:08 So I can actually go and
• 03:09 take a look at what the different versions of this might look like.
• 03:11 And I think I'm just gonna start yeah sure.
• 03:14 The one on the top left is good enough.
• 03:16 We'll go with that.
• 03:17 So the nice piece here is that I can look at this and it's kind of just
• 03:20 fades into the background like it's the background of a picture.
• 03:24 But I kinda do need to make these lines pop a little bit more.
• 03:27 So I'm gonna select this Series, right-click on it.
• 03:31 And we're gonna change the Outline Fill to a nice dark blue.
• 03:34 That's my actual series.
• 03:35 And you know, the other thing that would be nice is if I actually gave this some
• 03:39 markers, so that I could actually draw it back and show or
• 03:42 see which part of the series it was.
• 03:44 So let's go right-click on this and say Format Data Series,
• 03:49 and we'll go click on the Bucket.
• 03:52 And we'll look at Marker and Marker options.
• 03:56 We'll use a Built-in marker, of the bullet.
• 03:59 And I'm gonna change the Fill for my marker, to a Solid Fill, and
• 04:04 that fill is going to be the same dark blue color that I used before.
• 04:09 So, let's take a look at that.
• 04:11 We'll click on the chart outside.
• 04:13 Perfect. It's got some nice dots there.
• 04:15 I also probably wanna change the color of this gray line,
• 04:17 because it's really fading into the background.
• 04:20 And that's not ideal so, we'll grab him, right-click on it.
• 04:23 And we'll just change the outline color to a nice orange and
• 04:26 blue work quite well together.
• 04:28 So, I'll work with that.
• 04:29 And well, one challenge,
• 04:31 my Flexible Budget is showing up in front of my Actuals,
• 04:35 I think my Actuals are more important, so we'll go and we'll change that.
• 04:39 We'll click select Data, and we'll take our Actual Series, and we'll drop it
• 04:44 down below the Flexible Budget Series to bring that line out in front.
• 04:48 So that looks better.
• 04:50 This chart is pretty darn close to finished, the only other thing that I
• 04:54 wanna do is get of this wasted white space on the side here.
• 04:57 And the way that I'm gonna do that, is I'm going to go and select my Axis, and
• 05:01 over on the Format Axis task pane, we'll click on the little charting icons.
• 05:06 And we're gonna go and say Tick Marks.
• 05:09 And let me see now, that's not going to work for me either.
• 05:13 We want Access options, that's what we're looking for.
• 05:16 And we're going to set the axis position to On Tick Marks.
• 05:20 And basically, at this point I can close this window off and
• 05:24 I can look and say I have a pretty decent chart.
• 05:27 The nice piece about this is that this blue background, which shows the budget,
• 05:32 is there, but really my eyes are drawn to those two lines in front.
• 05:36 If I do need the background, I can focus on those.
• 05:39 So it's a three little series chart, but
• 05:41 the nice thing is that it acts like a two series chart.
• 05:44 You can fool your brain into seeing it.
• 05:46 So it's there when you need it and it's not when you don't.
• 05:48 It's one of my favorite charts to make.

Lesson notes are only available for subscribers.