Combination Charts - Area and Line

Locked lesson.

Upgrade

About this lesson

This lesson shows a trick to include an extra data series in your chart that fades in and out of view as it's needed.

Quick reference

Combination Charts - Area And Line

Combining line charts and area charts into a single chart.

When to use

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

Instructions

Reminder of Charting Goals

  • Every chart should tell a story, quickly and effectively
  • Extra chart elements create noise, and get in the way of the story
  • It is a best practice to remove as much excess ink (noise) as you can

Creating the Combination Chart

  • Select your data
  • Go to Insert --> Charts -->Line Charts and select the 2D Line Chart
  • Right click the line you wish to see as a Column --> Change Series Chart Type
  • Change the series chart type for each series you wish to change

Make the Area “softer”

  • Right click the Area chart’s background fill
  • Via the Toolbar or Format Data Series, soften the fill color
  • Make sure to also set a lighter outline to act as the “line” of the area chart

Adding context

  • Expand the chart so that all rows are readable
  • To link a title to a cell, select the chart title, press the equals key, click on a cell and press Enter
  • Other modifications can be made as with any normal chart

Hints & tips

  • Remove items and elements that you don’t need by selecting them and pressing the Delete key
  • Change font sizes by selecting an element and formatting it from the Home tab
  • When removing gridlines, make sure you are selecting the gridlines and not the entire chart
  • Adding markers to the lines is a great way to show how they compare to the column values
Login to download
  • 00:04 I now want to build a nice chart to show my cost of sales.
  • 00:09 But I've got a bit of a challenge here, and that challenge.
  • 00:12 Is that I actually have multiple budgets for my cost of sales.
  • 00:17 And you go what, why how does that make sense, well here's the challenge.
  • 00:21 This company has been asked to budget well in advance of what's actually happening.
  • 00:26 This budget is based as 42% of the revenue, which was budgeted.
  • 00:32 The problem is is that the actual revenue, in some cases is very different.
  • 00:38 So this is where we come up with the concept of a flexible budget.
  • 00:41 The flexible budget takes the current actual revenue x the cost of
  • 00:45 sales to come up with what we should have actually consumed for our cost of sales.
  • 00:50 We compare that back to the actual.
  • 00:52 The challenge is though, head office,
  • 00:54 always wants to know about the budget that you reported to them months ago.
  • 00:57 So, we need to also show this guy here.
  • 01:00 There's one more series of data that's kind of important to me as well and
  • 01:03 that is this variance number, because this tells me exactly what's going
  • 01:07 on here when I actually have a budget variance.
  • 01:10 If there's a big gap between the actual and the flexible budget,I need to know why
  • 01:14 this tells me what the actual breakdown is.
  • 01:17 So let's go and see what we can do with this.
  • 01:20 I'm gonna select all my data and I'm gonna go to insert and
  • 01:24 we're gonna grab just a standard line chart to start with.
  • 01:28 And here we are.
  • 01:29 We'll give it a nice title.
  • 01:31 We'll say equals.
  • 01:32 Make it cost of sales, why not?
  • 01:34 That's pretty logical.
  • 01:36 And at this point there's a few things that we can do.
  • 01:39 I'm not in love with align chart for showing this particular series.
  • 01:44 So what I'm gonna do is I'm gonna right click on this and
  • 01:46 say change series chart for type for our variance line.
  • 01:49 Of course, In Excel 2013 and higher, that takes us into the dialogue where we can
  • 01:53 then change all of our series chart types at once.
  • 01:56 So I'm going to set this one to a column chart.
  • 01:58 If you're in Excel 2010, you'd have to stop and then come back and
  • 02:02 right click on the budget static line, the gray one,
  • 02:06 because this is the next one that I'm going to change.
  • 02:09 What I love about this technique, I'm going to change it to area, and what
  • 02:14 it does, is it actually puts, it fills in all of the space beneath the line.
  • 02:18 Why?
  • 02:19 Well, you know what?
  • 02:20 Let's take a look at this for a second.
  • 02:22 When I say okay, it kinda looks a little ugly right now.
  • 02:26 But if I right click on it, and I go and I change my fill,
  • 02:30 into a nice light colored fill, and then I change the outline
  • 02:35 to a couple of shades darker in the same series, and I step away from it.
  • 02:40 What you'll notice is that it now sort of fades in like a picturesque landscape.
  • 02:46 It's there as background,
  • 02:48 but I don't really notice it, until I need to focus on it.
  • 02:51 When I do, I can say hey look what's this revenue point right here, okay cool.
  • 02:55 I can compare the 51 or
  • 02:57 5018 against my data point that looks like it's above 6500, so I can see the data.
  • 03:03 But when I'm not really focused on it, it fades into the background.
  • 03:06 And that's cool for me,
  • 03:07 because the most important stuff is the difference between the blue and
  • 03:10 the orange lines because those are what's based on the actual sales.
  • 03:14 Now I want to do a couple of other things here.
  • 03:18 I'd like to first off,
  • 03:20 bring the blue series forward because this one's kind of important to me.
  • 03:23 Again, if we go and we say, select data.
  • 03:27 We can move our actual series down.
  • 03:31 Now we just need it in front of the flexible budget line.
  • 03:34 That means it will be plotted in the right place.
  • 03:36 So that's good to go.
  • 03:37 We can say okay.
  • 03:39 Maybe we want to go and add some markers to this.
  • 03:42 So we'll right click on this.
  • 03:44 We'll go to format data series, to the paint bucket.
  • 03:48 Find our markers.
  • 03:50 And I'm gonna go with a marker option and use the built in, and
  • 03:54 we're going to use a nice circle.
  • 03:58 I'm also gonna put some markers on the orange series for the flexible budget.
  • 04:05 For this one, let's try diamonds.
  • 04:07 Why not?
  • 04:10 So that looks a little bit better.
  • 04:12 I'm happy with that so
  • 04:13 far, but there's a couple of other things that I might want to change.
  • 04:16 I like data labels on my column charts,
  • 04:18 because this thing here tells me a lot about what's going on.
  • 04:22 When I see a line where the actual goes way higher than what the flexible budget
  • 04:26 was, and then the next month, it goes way lower,
  • 04:27 that tells me there's an inventory variance.
  • 04:30 And in this case, I can see the exact numbers, 1,100 and 1,145 negative.
  • 04:34 These look like they offset.
  • 04:35 Someone miscounted for one month and it corrected the next month.
  • 04:37 So that's valuable information for me, if i'm actually trying to manage this stuff.
  • 04:41 But there's actually a challenge with this too,
  • 04:43 is that this now overlaps on top of all of my axis labels.
  • 04:48 So lets grab those, we'll go to the text options on those,
  • 04:52 scroll down, look at labels.
  • 04:55 And what we're gonna do is we're gonna change the label position to say, low.
  • 05:01 And when we do that it kicks it down below and
  • 05:03 gives me a 2,000 negative as an axis on here as well, which is kinda nice.
  • 05:09 At this point I gotta say I'm pretty close to done with this chart.
  • 05:13 The only other thing that I might decide to do with it is right click and
  • 05:17 say, you know what?
  • 05:17 Lets format the axis and lets change this so
  • 05:20 that it's not plotting between the tick marks, it's plotting on the tick marks.
  • 05:24 That removes some of the white space from the side.
  • 05:27 I'm pretty happy with the way that looks except that, you know what?
  • 05:30 It only gives me half a column at the end, so maybe not.
  • 05:33 I'll press Ctrl+Z, bring that one back.
  • 05:35 At this point I've got my chart here, Ctrl+X.
  • 05:39 We'll go to the dashboard, get rid of this dialog, Ctrl+V.
  • 05:45 And now I've got a nice chart that's actually pretty close to where I need it.
  • 05:48 I'll hold down my Alt key, bring it down down, up, there we are.
  • 05:54 And resize it to make it fit to my grid.
  • 05:57 And I've now got a beautiful chart that's a combination base that shows me my cost
  • 06:01 of sales from an actual and my flexible budget along with my variances.

Lesson notes are only available for subscribers.

Gift this course