Line Charts

Quick reference

• 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.