Building Combination Line and Area Charts

Quick reference

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