🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

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

Facebook 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