Combination Charts - Column and Line

Locked lesson.

Upgrade

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.

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
Login to download
  • 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.

Gift this course