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.xlsx27.8 KB Building Line Charts - Completed.xlsx
32.7 KB
Quick reference
Building Line Charts
How to create an effective line chart through careful manipulation of chart elements to enhance its story telling ability.
When to use
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
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 a Line chart
- Select cells B6:N8
- Go to Insert --> Recommended Charts --> select the first recommended chart
Enhancing the Chart context
- Reformat cells D7:N8 so that they have no decimals
- Reformat cells C6:N6 so that they use a mmm-yy date format
- Select the chart title --> press = --> select cell A1 --> press Enter
Adding markers for easy reading
- Right click the Revenue series --> Format Data Series
- Click the bucket icon in the taskbar --> Marker --> 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
- 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 focus on line charts.
- 00:08 And where line charts really shine is when you have a series of data points that you
- 00:12 want to compare over time.
- 00:13 That's where the line chart is probably the best chart choice
- 00:16 over all to use in most cases.
- 00:19 And of course,
- 00:19 like all other charts, we're gonna do the standard thing in charting.
- 00:22 And customize them to make look the best way possible.
- 00:26 We'll start of course by clicking our data, going to the insert tab,
- 00:30 clicking on recommended charts and seeing what Microsoft offers us.
- 00:34 And actually give us a lot of really cool looking charts.
- 00:36 But for this one we'll stick with our line and say ok.
- 00:39 Now, of course it creates this helpful chart title.
- 00:44 We're gonna link that back to a cell, C5 here, to get a nice title.
- 00:48 This is Actual Revenue versus Budget.
- 00:51 At this point I think I'm gonna slide the chart down underneath my data.
- 00:54 So that I can take a look in a little bit more detail here.
- 00:57 Maybe make it a little wider.
- 00:59 Now, that it's big enough I can look at it and say, is it answering the question,
- 01:03 is it showing it in the best way possible?
- 01:05 The first thing I notice is my access of dates is starting with January 1st,
- 01:09 February 1st, March 1st and yet I'm trying to plot month ends.
- 01:13 And even within that, it's plotting the day in the middle of that period.
- 01:16 That's a little strange.
- 01:18 So the first thing that I wanna do is give this a better format on my date.
- 01:22 So the easiest way to do that is actually just to customized right in the cells.
- 01:26 So right-click format cells.
- 01:28 And I think I'm gonna write my own custom number format
- 01:32 this ones pretty close to what I want.
- 01:34 But instead of having that dash in there I'm going to go with space or
- 01:40 little apostrophe there.
- 01:41 That looks good Jan 18.
- 01:43 Just to indicate that's not the 18th day of the month.
- 01:46 That's the 18th year of 2000 We'll say okay.
- 01:50 You'll notice that it changes the cells.
- 01:52 It also links right through the chart immediately, which is pretty good.
- 01:56 Now, the next thing I'm not a big fan of,
- 01:57 is that this is plotting in the middle of these columns.
- 02:00 And that's not really cool.
- 02:02 So I'm gonna do this, right click and say format axis.
- 02:08 On the axis options, I have the ability now to scroll down to the bottom and tell
- 02:13 it that I want to plot on tickmarks, and I do this for virtually every date access.
- 02:18 And you'll notice that it actually expands it a little bit wider, and
- 02:21 I now have January plotting on January, February on February, and March on March.
- 02:26 And yet
- 02:26 that's actually still kinda hard to see because these lines are kinda smooth.
- 02:30 So let's grab the line.
- 02:33 It takes us into format data series in the task pane here.
- 02:37 What I'm gonna do is I'm gonna click on the little paint bucket.
- 02:40 And you'll notice that in here I have the ability to format the lines.
- 02:44 And also have the ability to format markers.
- 02:48 So in the marker options, I'm gonna chose to use a built in marker.
- 02:53 And just like that, with this circle, I'll end up with,
- 02:57 a nice dot on each data point.
- 02:59 So that's pretty good, let me try this again for
- 03:02 the orange line, grab him, go back to the paint bucket.
- 03:06 To marker and on these marker options, I think I'll even try something different.
- 03:11 I'll go with built in,
- 03:13 maybe we could try a diamond instead just to have something different.
- 03:17 And if you want to,
- 03:18 you could even go in control the borders on these things as well.
- 03:22 So we could put a solid line around this that shows it in say, black.
- 03:27 And now when I click outside,
- 03:29 you'll see that we have little black diamonds on this as well.
- 03:31 Now, there's still some other challenges I might want to change.
- 03:35 Maybe I'm not happy with the format of the axis here, but
- 03:38 I don't want to change the cell formats themselves.
- 03:41 It's no problem.
- 03:42 When the axis actions come up over here I can go down to my number format,
- 03:48 and I can say hey let's knock off my decimals and put in zero.
- 03:52 And that will knock these down while preserving the cell.
- 03:55 So I don't have to format in the cells themselves.
- 03:59 The final thing that bothers me is that the revenue actual,
- 04:02 the blue line, is actually being plotted behind the orange line.
- 04:06 And I think that their actual revenue is the most important.
- 04:09 To fix this, I actually get out of the axis pane here altogether.
- 04:14 What I'm gonna do is go to Chart Tools, and we'll go on to the Design tab and
- 04:18 select data.
- 04:20 The challenge is the first row it's plotted first and
- 04:23 the next one draws on top of it.
- 04:24 So what we need to do is take actual and move it down.
- 04:28 And as soon as we do that, you'll notice that the blue line is not showing in
- 04:33 front and that is a pretty good-looking chart as far as I'm concerned.
Lesson notes are only available for subscribers.