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

Combo Charts - Begin.xlsx
33.9 KB
Combo Charts - Complete.xlsx
40 KB

Quick reference

Building Combination Line and Area Charts

Creating line and area combination charts.

When to use

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

Instructions

Creating a Line chart

  • Select your source data
  • Go to Insert --> Recommended Charts --> select one of the line charts

Enhancing the Chart context

  • Optional:   Select the Chart Title --> Press = --> Select the cell that holds your chart’s title
  • Widen the chart so that the axis displays properly

Add the Combination effect

  • Right-click one of the  series --> Change Series Chart Type
  • Reconfigure the chart so that the desired series is showing as an area chart
  • Format the area’s fill to use a lighter color with transparency

Modifying line elements

  • Right-click one of the line series --> Format Data Series
  • Click the bucket icon in the taskbar --> Line --> Color --> choose your preferred color
  • Click the bucket icon in the taskbar
    • --> Marker --> Marker options --> Built-in --> Type --> Circle
    • --> Fill --> Solid Fill --> Color --> choose your preferred color
    • --> Border --> Solid line --> Color --> choose your preferred color

Other recommended fine-tuning techniques

  • Right click the date axis --> Format axis --> Set Axis Position --> On tick marks
  • Go to Chart Tools --> Design --> Select Data --> use the arrows to change the order of the lines drawn on the chart
Login to download
  • 00:04 In this video, we're going to look at a very special chart that I actually
  • 00:09 used extensively when I was doing different revenue versus budget
  • 00:13 variances in our accounting offices.
  • 00:16 Now, before we do that, though,
  • 00:17 I want to call something very specific out about the data table you're seeing here.
  • 00:21 If I go and click inside this thing, what you're going to notice is that my dates
  • 00:25 are built on a formula that's referring to these, and
  • 00:28 reconstituting it into a nice format.
  • 00:30 My actual is a formula, my budget's a formula, my flexible budget is a formula.
  • 00:35 All of these are reading from the data tables that are hidden under these
  • 00:39 grouping layers.
  • 00:40 This is the original calculations as needed to be done.
  • 00:43 And then when I collapse them down, I've used a summary table to summarize
  • 00:47 them all into a nice contiguous block of cells that are perfect for charting.
  • 00:51 And I highly recommend you do this because it can make your life a lot easier
  • 00:55 than trying to chart against non-contiguous data sets.
  • 00:58 All right, now that we know how to do that, let's just go grab our data.
  • 01:02 We're going to go to Insert > Recommended Chart, and
  • 01:05 you'll see that Microsoft recommends a line chart, and that's good.
  • 01:09 They also have column charts and area charts.
  • 01:11 What they don't know is that I actually want two of these type of charts in
  • 01:16 a single chart.
  • 01:17 But they don't have that option here, so I'm going to choose the line chart.
  • 01:22 Now, this is all good and everything else, there's only one big problem with it.
  • 01:26 Let me just make this a little bit wider, so
  • 01:28 our labels all lie down horizontally there, that looks good.
  • 01:30 My Chart Title is not great, so let me go and say equals, and
  • 01:34 we'll pick up this is our draft beer analysis and link that to the cell.
  • 01:38 Now, we're not too bad a shape, but the problem with this is that I've got three
  • 01:42 lines and they're all screaming for attention.
  • 01:44 It's very hard to actually draw the difference between these things.
  • 01:48 So what I want to do is I want to focus on actual and flexible budget.
  • 01:54 Now, if you're not familiar with flexible budget,
  • 01:56 what this is is this is very useful in something like a cost of sale analysis.
  • 02:00 We actually reforecast our budget based on the revenue levels that we have.
  • 02:05 So this is much more interesting to me than the static budget that was projected
  • 02:10 when I didn't know what my revenues were going to be.
  • 02:13 Now, I want to send this one to the back, so I don't have to really focus on it.
  • 02:17 So I'm going to go and select the orange line here.
  • 02:20 I'm going to go right click on it, and
  • 02:22 I'm going to choose to Change Series Chart Type.
  • 02:25 What happens now is we get taken into the custom combination builder.
  • 02:30 And this is really kind of cool because we get to choose a different chart type for
  • 02:34 each series, for actual, for budget, or flexible budget.
  • 02:38 Now, actual and flexible, I'm happy with leaving these lines, but budget,
  • 02:43 I'm going to change out to be an area.
  • 02:45 Now notice, you can use columns and
  • 02:46 bars, some of those might look a little bit weird.
  • 02:48 Column and line charts work well together.
  • 02:51 But in this case, we want area, and this is going to look really ugly,
  • 02:55 just a big warning there.
  • 02:56 Is that ever orange?
  • 02:58 I'm going to go and say, OK.
  • 03:00 Sometimes unfortunately with charting,
  • 03:02 you have to make it look gross before you can make it look better.
  • 03:05 So let's soften this because this is just obnoxious.
  • 03:08 So I'm going to go and click on my orange block here,
  • 03:12 right-click, and choose to Format Data Series.
  • 03:16 I'm going to go over to the paint bucket, and right away,
  • 03:18 what I'm going to do is I'm going to make sure fill is expanded.
  • 03:21 I'm going to go and choose a Solid fill.
  • 03:24 I'm going to to use a nice light blue fill here, so that's much less intrusive.
  • 03:30 And then I'm also going to dial up the transparency to about 80%.
  • 03:35 Okay, let's see how close we can get that there with the mouse.
  • 03:37 Sorry, now I'm going to have to write it.
  • 03:39 So there we go, it's going to be, oops, 80%, perfect.
  • 03:43 Notice, we can see the grid lines through it.
  • 03:45 Now, the only thing that's a little bit not good on this one is when I click away,
  • 03:49 it doesn't have a very clearly defined line at the top, and
  • 03:52 I need that to really make sense of this.
  • 03:55 So I'm going to go back in here, and I'm going to change to use a solid line,
  • 03:59 just gray is fine.
  • 04:00 And now, when we click out, we can see a very solid line at the very top of this.
  • 04:04 And this is important because this is actually a line chart, essentially, but
  • 04:07 with some fill.
  • 04:08 That top line is my budget.
  • 04:11 Now, let's keep tweaking this.
  • 04:13 We're going to go right click on my Format Axis here,
  • 04:16 plot On tick marks to make that one a little bit wider, that looks good.
  • 04:21 The most important lines for me are actual and flexible budget, and
  • 04:24 actual being the most important, but it's behind my flexible budget right now.
  • 04:28 So I'm going to grab my chart, go to my Chart Design > Select Data, and
  • 04:33 I'm going to move Actual so that it is the last item that gets plotted.
  • 04:38 So Budget's plotted first, Flexible Budget's drawn on top of that,
  • 04:41 then Actual at the end.
  • 04:43 There we go, we can see that our blue line is now in front of the gray, so
  • 04:46 that's a little bit better.
  • 04:47 And then I might go, of course, and say, select this particular series,
  • 04:52 go and add some markers.
  • 04:54 Let's go and use a Marker Option.
  • 04:56 We'll go Built-in, and I think I'm going to go and
  • 04:58 put a little dots on this one here so I can see it.
  • 05:01 And then maybe we'll go and do the same thing for our gray line in the back here,
  • 05:05 we'll use a different type of marker here.
  • 05:07 So this one, I'll go and put, I don't know,
  • 05:09 add a little star just to be a little bit different.
  • 05:12 And there we go.
  • 05:13 We have a very useful chart right here that allows me to focus on the difference
  • 05:18 between actual and the static budget when I need to.
  • 05:21 But when I don't need to look at static, the main focus is in between actual and
  • 05:24 my flexible budget,
  • 05:25 that something's gone seriously wrong in a couple of these places here.
  • 05:29 So that is a useful chart that allows me to focus on the specific data I need to
  • 05:34 see when I need to see it, and ignore it when I don't.

Lesson notes are only available for subscribers.

Building Line Charts
05m:20s
Data Validation
05m:18s
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