GoSkills
Help Sign up Share
Back to course

Building Combination Line and Area Charts

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

When you want to graph 3 line series, but only want 2 in focus most of the time.

Exercise files

Download this lesson’s related exercise files.

Building Combination Line and Area Charts.xlsx
22.1 KB
Building Combination Line and Area Charts - Completed.xlsx
27.8 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 --> Format Data Series --> Bucket icon --> Gradient fill

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
Login to download
  • 00:04 Sometimes a single style chart won't do us.
  • 00:08 And that's where we can reach into some real black magic charting.
  • 00:10 The data that we're looking out here actually has the three series of
  • 00:13 data that's running across it.
  • 00:15 One of them is a little bit special.
  • 00:17 This is a cost of goods sold budget is what's actually happening here.
  • 00:21 So, somebody's gone through and
  • 00:22 they've budgeted the amount of revenue that they're expecting to make.
  • 00:25 And there's two different budgets for the costs that are coming through here.
  • 00:28 The first one is a static budget, which is just labeled as budget.
  • 00:31 And that's where the accountant will take what the projected revenue is,
  • 00:34 what the projected cost of sales percentage is and come up with here's what
  • 00:38 we believe if we hit our revenue target our cost of sales should be.
  • 00:42 The second one is a flexible budget and what that does is it actually looks at
  • 00:46 the actual revenue and multiplies it by the cost of sales percentages.
  • 00:50 Just to say based on the actual revenue we made.
  • 00:54 This is what we believe our cost of sales should actually be.
  • 00:56 So it's two different measures to try and figure out what's going on.
  • 01:00 So in this case, we're actually comparing the actual cost of goods sold
  • 01:03 versus the static budget that was made before revenue was known.
  • 01:07 Versus the cost of goods sold that we should have now
  • 01:09 that we actually know what our revenue is.
  • 01:12 Now the challenge with this one is that we could do a line chart but
  • 01:16 some things kinda fall sort of amongst the way by doing that.
  • 01:19 So we're gonna go into a little bit of black magic stuff here and I'm gonna show
  • 01:21 you how to create a combination chart that combines two chart types together.
  • 01:26 To do that, we start off first by going to our recommended chart, and
  • 01:30 we'll start off with nice-looking little line chart.
  • 01:34 So that's all good and wonderful and everything else, but
  • 01:37 we kinda like this to be a little different.
  • 01:40 So what we're gonna do is we're gonna select the Budget Series,
  • 01:43 because while this is interesting the Flexible Budget is much more important.
  • 01:48 So I'd like to make the Static Budget kind of go in the background a little bit.
  • 01:52 To do this I've selected the series.
  • 01:54 I'm now gonna go to Chart Design, I'm gonna go to Change Chart Type and
  • 01:59 I'm gonna choose an Area Chart.
  • 02:02 I'll take the first one.
  • 02:05 And you might be thinking now holy cow,
  • 02:06 Ken I thought you said you wanted this a background and this is subtle.
  • 02:09 This is not subtle, I would definitely agree.
  • 02:11 But notice that we've got two different type of charts put together here.
  • 02:15 We've got two series of line charts and
  • 02:17 this garishly orange background is an area chart.
  • 02:20 So two different types.
  • 02:21 So let's grab this guy here and we'll right-click.
  • 02:25 And let's do a format on our data series here and
  • 02:27 we'll very quickly get rid of that nasty orange.
  • 02:29 We'll click on the bucket.
  • 02:30 We're gonna go choose a gradient fill and that works.
  • 02:33 It's nice and light and in the background.
  • 02:36 At this point we're gonna start playing around with the different things that we
  • 02:39 might need to do,
  • 02:40 in order to customize this chart to make it look a little bit better here.
  • 02:43 So, things like making our legend a little bit more
  • 02:47 readable by having a larger font size.
  • 02:49 That's one that we'll take care of really quickly here.
  • 02:52 So, go and make those guys set up.
  • 02:54 And we'll do the same thing with the legend as well.
  • 02:57 I'm also gonna expand the width here.
  • 03:00 So we'll go in to format access, and we'll go to our options and
  • 03:06 we will plot on tick marks to get rid of that white space at the end.
  • 03:12 Now the next thing that I may wanna do here is I may want to go through and
  • 03:15 actually make these series a little bit more stand out and what not.
  • 03:21 So let's go through and set our actual series here to a thicker line.
  • 03:27 So we'll go with a width, we'll go out to say three.
  • 03:31 And maybe we'll use a nice dark blue so that we can really see it.
  • 03:37 And then our flexible budget.
  • 03:38 We could go into the background here and say, well you know what.
  • 03:41 Let's go and we'll put this guy here at a three point as well.
  • 03:46 But maybe we'll choose a slightly different color.
  • 03:48 I guess we could go with gray, we could.
  • 03:50 Yeah sure, let's go with a dark gray so we can, nah, it's a little bit too similar.
  • 03:55 Maybe we won't.
  • 03:55 Maybe we'll go with a light green.
  • 03:57 How about that, there we go.
  • 03:58 So we can see what's going on in these guys here now.
  • 04:03 Once again the other thing that we may wanna do is select our line,
  • 04:06 maybe put some markers on it.
  • 04:08 So we'll go and choose our marker option and
  • 04:10 say that we're gonna go with an automatic marker.
  • 04:13 And that will give a little bit more to help, although,
  • 04:16 to be honest with you I'm not really a big fan of these markers.
  • 04:20 They're the wrong color.
  • 04:20 So I'll go back in and I'll modify my markers again.
  • 04:23 So you know that marker option is great, but
  • 04:27 the fill color needs to be a solid fill and
  • 04:30 I'd really rather have this in the same dark blue as we had previously.
  • 04:35 Maybe it's even a little bit subtle too, just size-wise.
  • 04:38 So we'll go with our round one and jack the size up to a seven or an eight.
  • 04:43 So that we can see it a little bit better, and that somewhat helps out.
  • 04:47 So the nice thing about this chart here is that
  • 04:50 the static budget sort of fades into the background.
  • 04:52 It looks like a little bit of a mountain, but
  • 04:54 the two series that I'm really focused on are the ones that are out in front here.
  • 04:58 So as I'm looking at this chart.
  • 04:59 These are the ones that jump into view mostly.
  • 05:02 And yet, the other one is in the background if I need it.
  • 05:04 So this is a kind of a cool looking little chart that you can play around with
  • 05:07 to get some really good insights fading one series into the background.
  • 05:11 And yet it's still there when you do need it but it doesn't distort and
  • 05:14 confuse the picture by having too much data when you don't need to read it.

Lesson notes are only available for subscribers.

Conditional Formats with Custom Rules
05m:31s
Data Validation
05m:44s
Share this lesson and earn rewards

Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement