Locked lesson.
About this lesson
How to create an effective line chart through careful manipulation of chart elements to enhance its story telling ability.
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 Line Charts.xlsx9.5 KB Building Line Charts - Completed.xlsx
12.1 KB Building Line Charts - Extra Practice.xlsx
22.8 KB
Quick reference
Topic
Building line charts in Excel 2010.
Description
How to create an effective line chart through careful manipulation of chart elements to enhance its story telling ability.
Where/when to use the technique
When you want to compare a series of data points and compare them over time, making use of techniques to help you convey your message to your readers as quickly as possible.
Instructions
Creating a Line chart
- Select cells B6:N8
- Go to Insert --> Line Chart --> the top left 2D line chart
Enhancing the Chart context
- Reformat cells C7:N8 so that they have no decimals
- Reformat cells C6:N6 so that they use a mmm-yy date format
- Select the Legend --> Chart Tools --> Layout --> Legend --> Show Legend at bottom
- Select Chart Tools --> Layout --> Chart Title --> Above Chart
- While the chart title is selected --> press = --> select cell A1 --> press Enter
Remove noise
- Right click the vertical axis --> Format axis --> Major tick mark type --> None
- Format axis --> Line Color --> No Line
- Select the chart gridlines --> Line Color --> Line Style --> Dash type --> First row of dots
Adding markers for easy reading
- Select the Revenue series --> Format Data Series --> Marker options
- Selection Built-in and set the type to the bullet icon
Fine tuning the elements
- Right click the date axis --> Format axis
- Set Axis Position --> On tick marks
- Set Major tick mark type --> None
- Go to Chart Tools --> Design --> Select Data
- Select the Actual series and click the Move Down icon
- 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.