Locked lesson.
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.
Exercise files
Download this lesson’s related exercise files.
Combination Charts - Area and Line.xlsx53.5 KB Combination Charts - Area and Line - Completed.xlsx
58.4 KB
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
- 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.