Locked lesson.
About this lesson
Useful for showing trends over time, this lesson explores line charts and some of their options.
Exercise files
Download this lesson’s related exercise files.
Line Charts.xlsx29.4 KB Line Charts - Completed.xlsx
33.8 KB
Quick reference
Line Charts
Working with Line charts.
When to use
Line charts are useful for showing trends over time.
Instructions
Creating a Line chart
- Select your data
- Go to Insert --> Charts --> select the first Line Chart
Enhancing the Chart context
- Reformat your source data number format and it will carry through to the chart
- To link a title to a cell, select the chart title, press the equals key, click on a cell and press Enter
Adding markers for easy reading
- Right click a series --> Format Data Series
- Click the bucket icon in the taskbar --> Marker --> Marker options
- Select Built-in and choose your preferred marker style
Removing whitespace when plotting dates
- Right click the date axis --> Format axis
- Set Axis Position --> On tick marks
Changing the display order of the lines
- Open the Chart Tools --> Design --> Select Data dialog
- Lines are drawn on the chart in the order shown in the dialog, with new lines drawn over the existing ones
- To change the order, select your series and click the Move Down (or Move Up) icon
Hints & tips
Right clicking any data series will allow you to add Trend Lines to the chart
Data labels can also be added, but are difficult to read on multi-series line charts
- 00:05 Line charts work particularly well when we're trying to compare
- 00:09 multiple data points over a time based scenario.
- 00:13 So in this case, I use these oftentimes for charting monthly sales or
- 00:17 performance to the budget, things like that.
- 00:20 Now before we jump into this, the one thing I wanna call out quickly is you'll
- 00:23 notice that I've got a little bit more complicated chart title for this one.
- 00:27 We've got the ="Revenue vs Budget" as text, &CHAR(12) for our funky little line
- 00:32 break, and then I'm stripping the year out of B4 and appending tax year to it.
- 00:36 So, if we look, that's because we actually have a cell here that's January 31,
- 00:41 2017, and next to it, we've got a little function that's called EOMonth which
- 00:46 looks at the January date and advances it by one month to the next month end.
- 00:51 So this would be February 28th, March 31st, and whatnot.
- 00:55 Now this is cool because this one, as soon as we update this to a different year,
- 00:59 if we were to go to January 21st, 2018, would automatically update our chart and
- 01:05 all of our individual dates as well, which is great.
- 01:07 So I'm just gonna roll that back to 2017 and
- 01:10 we're now gonna go and create a nice looking line chart.
- 01:13 So in this case the challenge that I've got is that I've actually got data.
- 01:18 I've got a piece of information in between.
- 01:20 So I'm gonna go to insert.
- 01:22 We're gonna go to our 2D line chart, which gives me data that looks like this, but
- 01:27 you'll notice the periods are 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12.
- 01:29 In order to fix something that isn't working right with a chart,
- 01:34 you can go to select data on the chart tools design tab.
- 01:39 You'll notice that we have the data series, and I could
- 01:41 edit either one of these if I wanted to point my title to a different cell.
- 01:46 But in this case, the issue that I've got is my horizontal axis labels,
- 01:49 the guys down at the bottom here, have not picked up my dates.
- 01:52 So I'm gonna say Edit and I'm gonna pick up my axis label range which
- 01:56 goes from January to December and we'll say OK and OK.
- 02:01 And now those show up quite nicely in my chart.
- 02:05 I'm going to link my chart title.
- 02:06 We'll say equals for Revenue vs Budget and
- 02:09 you'll notice the line break works quite nicely there.
- 02:11 So that looks pretty good.
- 02:13 Now some of the stuff that I may wanna play around with on this.
- 02:17 I notice there's a big gap in whitespace here between 25,000 and
- 02:20 20,000 and it doesn't look like my data ever gets over that area.
- 02:24 So I'm gonna right-click on this and say Format Access.
- 02:28 When I'm formatting the axis I can see that the bounds are a minimum of 0 and
- 02:32 a maximum of 25,000.
- 02:34 This is one of those areas where you need to play with caution,
- 02:38 because if your data does go outside of your realm, at that point in time,
- 02:42 it will go right off the top of the chart.
- 02:44 So if I were to set this to say 10,000,
- 02:46 you'll see that at this point in time things go off the top,
- 02:50 which is not ideal, but it does give you the ability to control the scale.
- 02:53 I wish we could link this to a cell but unfortunately we can't.
- 02:57 The other thing that we can do is we can change the units for the minimal or
- 03:02 major and minor axis as well so if we want to gradiate these by different things and
- 03:09 then add a grid line here for minor horizontal.
- 03:12 These minor horizontal red lines that you're seeing show up on the chart right
- 03:16 now are based on the values in this area.
- 03:18 Now I don't particularly need them here, so I'm going to skip them, but
- 03:20 that's the way that we can actually work with it.
- 03:24 Something that does drive me a little bit nuts with my time charts, is that January
- 03:27 is plotted with an indent coming from the side and December also has an indent here.
- 03:33 So, when I'm working with my access here and its dates,
- 03:37 what I really like to do with this case is I like to plot my dates on tick marks.
- 03:42 Again, this is one of the options.
- 03:44 It's found under the access options and if you move it to on tick marks,
- 03:48 you'll notice that between, actually has it indented on tick marks,
- 03:52 actually shows things nicely.
- 03:54 If I wanted to flip the dates around so that it was the most recent date first,
- 03:58 I can check dates in reverse order and it would flip the whole thing around.
- 04:01 That's not generally the way that I like to look at my data so
- 04:04 I'm going to flip it back into this format.
- 04:07 What if I want to apply things like data markers?
- 04:10 Well at this point, I can go over to my line series and under the fill options,
- 04:16 you'll notice that near the top we actually have the ability to set markers.
- 04:21 So if I go in here, go to marker options, I can use a built-in marker for
- 04:25 this one here.
- 04:26 I like the nice dot, and when I click outside of the chart,
- 04:30 you can see that got applied nicely.
- 04:32 If I go back and click on the Options here,
- 04:34 again into the Fill, we'll go to Marker on this guy here and
- 04:38 maybe under these Marker Options, I'll set this to be a built-in marker with an x.
- 04:45 The marker shows up on the legend.
- 04:47 It also shows up on the chart.
- 04:50 One thing that is problematic with this chart though is the number
- 04:53 one focus that I really want to see here is revenue and
- 04:56 notice how the budget line is actually in front of revenue.
- 04:59 That's not really ideal.
- 05:00 I want the blue line in front.
- 05:02 So again, on this one, we go back to the old Select Data because in Select Data,
- 05:08 the order of the lines, this is the order they're drawn on the chart.
- 05:11 Revenue first and then budget gets drawn over top.
- 05:14 So I can go and select this guy here, move him down and
- 05:17 when I say okay, notice the blue line is now in front.
- 05:21 One other thing I might wanna do, grab revenue, right-click, and
- 05:24 maybe add a trendline.
- 05:28 And at this point in time it gives me a nice little trendline here but
- 05:31 I can come back and I can actually play around with these trendlines as well.
- 05:35 I could say, you know what?
- 05:36 Maybe I want a moving average and I want that moving average to be for
- 05:40 three periods.
- 05:41 And now I get a nice little trendline that's actually showing me my moving
- 05:44 average for the last three periods as well.
- 05:46 Based on the series I selected which was revenue.
- 05:49 So lots of different options to play around with when we're actually building
- 05:53 our charts to customize them and get them to look exactly right for what we need.
Lesson notes are only available for subscribers.