Locked lesson.
About this lesson
Combination charts open a whole new world of charting. In this lesson we combine column and line charts together to make a compelling chart.
Exercise files
Download this lesson’s related exercise files.
Combination Charts - Column & Line.xlsx49.1 KB Combination Charts - Column & Line - Completed.xlsx
53.6 KB
Quick reference
Combination Charts - Column And Line
Combining column and lines charts into a single chart.
When to use
When you want to graph multiple series and want to show them differently than just lines.
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
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
- When adding data labels to the columns, you may wish to force them Inside Base to avoid showing them with your lines
- Adding markers to the lines is a great way to show how they compare to the column values
- 00:05 We're now gonna jump into building some more complicated chart types and
- 00:08 in particular, we're gonna build combination charts.
- 00:11 These are charts that don't come out of the box by default
- 00:14 we have to actually customize them to get them the way that we want.
- 00:17 And for the context of doing this, we're now gonna start to lay out this dashboard
- 00:21 for the Outdoor Store To fill in all of the different sections of this thing,
- 00:24 as we build through the next few modules.
- 00:27 We'll start by going to the source data page.
- 00:29 Now on the source data page, you'll find that all of the source data that we need
- 00:32 for doing the next few charts is all here.
- 00:35 And that actually kind of explains a little bit why it may not be laid out
- 00:39 perfectly for charting is because we've got stuff grouped out so we can read it.
- 00:43 That's where we end up with things like this,
- 00:45 a header row that goes in between our dates and our values.
- 00:48 But regardless, we can make this work, so that's exactly what we're gonna do.
- 00:52 We'll start by selecting the data we want, actual through budget for
- 00:55 January through December.
- 00:57 Now we'll go to insert and
- 00:59 we're gonna start by building a regular 2-D Line chart.
- 01:04 Now we need to add our axis on here so we'll go to select data and
- 01:08 we'll go tell it that indeed we don't want numbers from one to 12,
- 01:12 we actually want the series that is in row 4 in order to actually lay these out and
- 01:16 get our month theme headers in here, which is perfect.
- 01:21 Now what else do we want in here?
- 01:23 Well, we can put in a nice chart title.
- 01:25 I don't know that this really needs to be dynamic at this point.
- 01:27 So we'll just say revenue versus budget.
- 01:31 There we are, that's a nice chart title.
- 01:33 That'll work out fairly well for us.
- 01:35 And now we need to go through and make a modification.
- 01:38 The steps here are going to be the same no matter what version of Excel you're in.
- 01:42 What I want to do is I wanna make my actual be a column chart and
- 01:47 my budget be a line chart.
- 01:49 So the way that we do this is we go and we select the series that we want to change
- 01:53 and we're gonna right-click on it and say, change series chart type.
- 01:58 Now, depending on the version of Excel you get,
- 02:00 this is where things get a little bit different.
- 02:03 In Excel 2010,
- 02:03 you would have to pick the type of chart that you want without having a preview.
- 02:08 In Excel 2013 higher though,
- 02:10 we've got this cool little Expression Builder here that we can go through
- 02:13 now and say, you know what, I wanna change Actual to be just a standard column chart.
- 02:19 And at that point now we get a nice little preview of what the data actually looks
- 02:22 like, there's even some pre-formatted ones that we might wanna work with.
- 02:26 Personally I build all mine from scratch so I know exactly what I want and
- 02:29 I'm good to go.
- 02:30 When I say okay,
- 02:31 I now have a nice little column chart that actually has my budget line behind it.
- 02:37 Now I do find when you're working with a chart like this that it's helpful
- 02:42 to go and actually put markers onto our lines.
- 02:45 So I'm gonna right-click on this guy here, we're gonna go to Format Data Series.
- 02:50 I'm going to go to the Fill and Line options, find my markers, and
- 02:54 I'm gonna use a Marker Option, I'm gonna use a built in one and why not,
- 02:59 the square is just fine for me right now thanks very much so we'll say okay.
- 03:05 And now I can select somewhere else on the worksheet and I've actually got a chart
- 03:08 that's pretty good looking chart, actually there's really not a lot else I wanna do.
- 03:12 I could add data labels to it, although to be honest with you,
- 03:16 when I have a line chart, I'm not a big fan of using data labels.
- 03:20 And the reason being, is that if I do go and add them, let me just go and
- 03:23 try it right now, Add Data Labels.
- 03:25 The challenge is, is which data point does this actually belong to?
- 03:29 And that's sort of the challenge here.
- 03:30 So I'd almost rather not do that in this case because it could be misleading.
- 03:34 So what I'm gonna do is I'm gonna press Ctrl+Z, and we're gonna get rid of those.
- 03:38 At this point I've got a chart that I'm pretty happy with.
- 03:41 Now I just need to store it somewhere.
- 03:43 So what am I gonna do?
- 03:44 Well, the easiest way, we click outside the worksheet grid,
- 03:47 make sure that it's not selected anymore.
- 03:49 At this point, I can grab my chart, I can cut it.
- 03:53 The easiest way to do that, Ctrl+X.
- 03:55 Although you could use the scissors up here or even right-click on it.
- 03:58 We're gonna go to the dashboard.
- 04:00 I'm now gonna come over to my cell here.
- 04:02 Ctrl+V will paste or of course we can use the paste or right-click and paste.
- 04:07 And now I just need to resize it to make it work correctly.
- 04:10 So I'll grab my chart, hold down my ALT key.
- 04:13 We'll make sure that we're moving it into the spaces that I
- 04:15 actually want to have it.
- 04:17 There we are, so it's gonna snap in there nicely.
- 04:19 And we'll now snap it into the grid nicely as well.
- 04:23 And I've got that fleshed out in my dashboard.
- 04:25 So that looks pretty good.
- 04:26 I've now got two visuals that are setup, ready to go.
- 04:29 Easy to create this particular chart,
- 04:31 looks nice, gives me some good information.
- 04:34 I'm happy with that so far.
Lesson notes are only available for subscribers.