Building Line Charts

Quick reference

• 00:04 In this video, we're going to look at building a dual series line chart.
• 00:08 And we've got the perfect set of data to work with to do just that.
• 00:12 We've got two series here, actual and budget revenue.
• 00:16 And we have twelve different points in time with values for those revenue months.
• 00:21 This is perfect because that's what line charts are great at,
• 00:24 is showing how certain points of time can compare to each other.
• 00:27 So let's take a look at what we need to do to build an effective dual series
• 00:31 line chart.
• 00:33 So the first thing that we need to do is actually build the line chart.
• 00:37 So what we're gonna do is select our data, and we'll go to the Insert tab.
• 00:43 And choose the line chart, and
• 00:45 we'll start with the line chart in the top left hand corner.
• 00:48 And that builds this, monstrosity.
• 00:52 All right.
• 00:52 So let's start to reformat this and make it look a little bit better because
• 00:56 this really could use a lot of work right now.
• 00:59 The first thing that we're gonna do is we're gonna drop the legend down
• 01:01 to the bottom.
• 01:02 So I'm gonna grab that, and we're gonna go to the Chart Tools Layout tab.
• 01:05 We're gonna say Legend and Show Legend at Bottom.
• 01:09 That's gonna give us a little bit more white space to work with.
• 01:12 The next thing that we should really do is give our chart some context.
• 01:15 So we're gonna throw on a chart title above the chart.
• 01:19 And while we've still got these little boxes around the outside,
• 01:22 we're gonna type in Equals and link it back to a cell so
• 01:26 that we now know that this is an actual versus budget revenue chart.
• 01:32 I'm not really thrilled with the angular representation of the dates on here.
• 01:36 Now it's true that I could make the chart a little bit wider to try and
• 01:39 get past that, but that's not really ideal.
• 01:41 And it's gonna take a lot of space to get that right.
• 01:43 So, the other way that I could deal with this is I could go back and
• 01:47 modify the original source data because it's all linked to the chart.
• 01:51 I could right-click.
• 01:52 Choose Format Cells.
• 01:55 And then I could go into the date settings, and
• 01:58 I could find one that's gonna work a little bit better for me.
• 02:01 And how about this one here?
• 02:03 Mar01 would represent with my data to January 13, representing 2013.
• 02:07 So, it's a much shorter way of looking at this data.
• 02:11 And now I've got something that actually represents horizontally in the chart
• 02:14 which looks a little bit better.
• 02:15 I also really don't need all these double zeros at the end so let's go and
• 02:19 drop those off.
• 02:20 So we'll go and select those and we'll just drop.
• 02:23 Back couple of spots there.
• 02:25 Again, that brings those things back.
• 02:27 Perfect.
• 02:29 Like always, I'm gonna go through and I'm gonna try and
• 02:31 remove a lot of the ink that I don't think is necessary.
• 02:34 So I'm going to right-click on the Axis title here.
• 02:37 Format Axis.
• 02:39 I'm going to remove these major tick marks, and we'll say None.
• 02:44 I'm also gonna remove the border that runs up this way here by
• 02:47 clicking on Line Color and saying No Line.
• 02:51 And you can see when I select the date axis that that line has gone away.
• 02:56 We've now got, that unnecessary stuff has been removed.
• 02:59 I'm gonna remove the tick marks down here as well, so we'll just go and
• 03:02 say None on those.
• 03:04 And, you know, one of the things that I really don't like about line charts is
• 03:08 that we end up with January having a data point plotted in the middle.
• 03:11 I'd really rather see this on the left hand side.
• 03:13 So the way that I do that is in this format axis,
• 03:16 I can position my axis on the tick marks to slam it over to the left.
• 03:20 And that makes that chart look a little bit more full, I think, filled out.
• 03:25 It doesn't have white space on the outside, which is good.
• 03:29 I'm also gonna take these grid lines and soften them a bit.
• 03:31 So I've grabbed the grid lines.
• 03:33 We'll go to Line Style, Dash type.
• 03:37 And drop them into this dotted line.
• 03:39 So that just softens it back a little bit.
• 03:41 That's better.
• 03:41 And the last thing that I probably wanna do is add some markers to this so
• 03:46 that I can see which points really add up with this.
• 03:49 We'll add a little bit of ink back.
• 03:50 We'll select our line.
• 03:52 And we're gonna go to Marker Options.
• 03:56 I'm gonna choose Built-in.
• 03:57 And we'll put the little bullet on here.
• 04:00 So now you can see when I select somewhere else on my chart that I've got these
• 04:03 little bullets.
• 04:04 So one last thing that I wanna change on this.
• 04:07 The most important number on this chart to me is revenue.
• 04:11 And what you can see happening here is that I've got orange lines, budget, that
• 04:14 are crossing in front of my revenue and that's not ideal.
• 04:18 My revenue should be the first thing that I see.
• 04:19 That's more important than budget because it's actually what I got.
• 04:23 So I wanna bring the blue line forward.
• 04:26 Now the way that we do this is we need to change the data, select the data.
• 04:31 But the problem is it's grayed out.
• 04:33 And the reason it's grayed out is because this dialogue is still active so
• 04:36 we're gonna close that.
• 04:37 And you'll see that now we can get into Select Data.
• 04:40 If it's still not available just click outside the chart.
• 04:42 Select it again and now you can.
• 04:45 Now what happens is the Select Data will show you the marching
• 04:48 ants around the border of the data that you have used.
• 04:50 But the other thing it tells you, something really important,
• 04:53 is what order the series are actually drawn on the chart.
• 04:57 Revenue actual is currently being drawn on the chart first and
• 05:00 then revenue budget is being drawn on it afterwards.
• 05:03 And that pen that they use to draw it actually writes over top of the other one.
• 05:07 So the way that we fix this is we grab revenue actual and we click the move down.
• 05:12 And you can see already that the blue line has actually popped above the orange line.
• 05:16 So, that's it.
• 05:18 And at the end of the day we've got a chart that looks pretty nice and
• 05:21 is pretty effective.

Lesson notes are only available for subscribers.