Locked lesson.
About this lesson
When you want to graph 3 line series, but only want 2 in focus most of the time.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Building Combination Line and Area Charts.xlsx33.4 KB Building Combination Line and Area Charts - Completed.xlsx
38.9 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 colour 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 the lines are drawn on the chart
- 00:04 In this video, we are gonna show you a very special type of chart,
- 00:07 called a Combination Chart.
- 00:09 This is where we actually combine multiple different chart types to show our
- 00:13 information for effectively.
- 00:15 Before we do, I just wanna call up that we have some hidden data here.
- 00:18 This is the actual results table, kind of our budget results table as well.
- 00:23 I'm gonna collapse these guys down, and
- 00:25 point out that they reconstitute again in the table down below here.
- 00:28 And actually, this is a pro tip I have for
- 00:30 you is that I never chart the original data source.
- 00:33 I always like to it, as you can see here,
- 00:36 in order to create a table that is specifically setup to chart properly.
- 00:39 It just makes things easier when you wanna make your systems more dynamic later on.
- 00:44 Now, I'm gonna go and say, Insert recommended chart, and
- 00:49 you'll notice that I have a bit of a problem.
- 00:51 Why?
- 00:52 We'll, actually has to do with this header row,
- 00:54 it's confusing things because it's attached to this data.
- 00:57 So I'm gonna say, you know what?
- 00:58 Let's insert a block here, there we go, and we'll right click and hide it.
- 01:04 Now there is separation between these two things.
- 01:07 So the chart sources doesn't look like it's attached to this, and
- 01:10 my recommended charts dialog will actually work.
- 01:14 You'll notice that I have lots of different kinds of charts here, but
- 01:16 they're all the same charts, all lines, all columns, all area.
- 01:20 No big deal, we'll come back to that.
- 01:22 But right now, let's say OK, and it creates us our original chart.
- 01:26 I'm gonna naturally link the chart title to be a little bit more sensible,
- 01:32 and we'll say, OK.
- 01:33 And now I want to focus on my issue.
- 01:36 I have an actual line here, this is the one that's most important to me.
- 01:40 But I have two budgets, the flexible budget is the one that's being adjusted
- 01:44 based on my sales and is what I really need to manage too.
- 01:48 The problem is I have a static budget.
- 01:50 This is what I built for my head office before I started reporting, and
- 01:53 before I knew what the actual sales were.
- 01:55 And this is what I get evaluated against, but
- 01:58 it's not as relevant to me as the flexible one.
- 02:00 So I'd like to find a way to send this to the background, and
- 02:03 that's where this trick becomes really handy.
- 02:05 So I selected the line, I'm gonna right click, and
- 02:07 choose, Change Series Chart Type.
- 02:11 This will take me into the combination dialogue builder.
- 02:15 Now you'll notice that I have my actual here, and it's showing as a line chart,
- 02:18 I could change it if I want but this works just fine.
- 02:21 Budget though, I'm gonna change to show as an area chart, and
- 02:25 bear with me cuz this is gonna look horrible.
- 02:28 There it is, flexible budget will leave as a line as well, and
- 02:32 we're gonna say, OK, and that is truly nasty.
- 02:37 So now we need to do a little bit of work.
- 02:39 Let's start by right clicking formatting our Axis.
- 02:43 And we'll go and we'll plot our data here on tick marks.
- 02:47 And this at least will take it out to the edges.
- 02:50 I'll even make this a little bit wider,
- 02:51 so my labels hopefully will show up where they're suppose to.
- 02:55 Now the trick behind this one is actually around playing with this fill area.
- 03:00 I'm gonna select that particular series, go to the fill in line, and
- 03:05 check out the Fill.
- 03:07 Now we have a couple of options here.
- 03:08 We can use gradient Fill if we want to, and this is actually kinda cool.
- 03:13 But we have to make sure if we're gonna do this that we set our gradients to actually
- 03:16 go the right direction with the darker colors at the top.
- 03:20 If we don't, we can actually have the colors fade out, and
- 03:23 not be able to see them.
- 03:24 So that's maybe not ideal.
- 03:26 I'm gonna go with the solid Fill.
- 03:28 But I'm gonna change the color to a much lighter blue.
- 03:32 And in addition, I'm also gonna go and set the Transparency up,
- 03:36 because at this point, now I can actually see a little shading behind it.
- 03:40 So I can see my grid lines, which is actually useful.
- 03:44 I'm also going to grab the border, and I'm going to set a solid line on the border
- 03:49 in a very light color, a nice light, say gray.
- 03:53 How about this one here, and
- 03:55 that essentially acts as another line behind things.
- 03:58 And what I actually have now is I've got a beautiful chart that has my budget,
- 04:02 my static budget information, behind it should I need it,
- 04:06 we can see the line there.
- 04:08 But for the most part, I'm actually gonna be focused on the two lines in front.
- 04:12 So I can now go and say, hey, this actual line should actually be in front.
- 04:16 So let's go select the Charts go to Select Data.
- 04:21 We'll bring the actual line down to the bottom underneath flexible budget.
- 04:27 And let's see if that's actually plotted in the right area.
- 04:29 It looks like the blue line's up front now, that's good.
- 04:32 And I can even grab it, go and add, say, a Marker.
- 04:37 For a built-in marker just to add a little bit
- 04:40 more information to this particular chart.
- 04:42 And at this point, I've got a pretty cool and useful chart.
- 04:45 It allows me to focus on the blue and the gray lines right away,
- 04:48 the actual versus the flexible budget, which are the most important to me.
- 04:52 But the information for
- 04:53 the static budget is still there when I need to call into view.
- 04:57 When I don't need it in view though, it kind of acts like a nice, serene,
- 05:01 mountain backdrop,
- 05:02 just to give a little peaceful nature to the chart that I'm trying to consume.
Lesson notes are only available for subscribers.