Building Line Charts

Quick reference

• 00:04 In this video we're going to explore line charts which are absolutely fantastic for
• 00:09 showing trends and changes over time.
• 00:11 So I've got some draft beer sales here for actual and
• 00:14 budget that are running over a 12 month period.
• 00:17 I'm going to go to Insert, go to Recommended Charts and
• 00:20 see that Microsoft does indeed recommend a line chart for this,
• 00:23 although you can see there's options for column and area charts as well.
• 00:27 But right now this is kind of what I expected to get and what I wanted, so
• 00:32 I'm going to go and say, OK.
• 00:33 Now, what we can see here is that we get our nice chart title here.
• 00:38 Well, that's not awesome, let's go link that back to a cell, so we've got a little
• 00:42 more context on what we're working with awesome actual revenue versus budget.
• 00:46 The challenge that I've got here though,
• 00:48 you'll notice that all of my dates are on these weird angles here.
• 00:50 So if I want to fix that, I've got two options.
• 00:53 Number one, I can shorten what's showing in the cells so
• 00:56 that it will lay out horizontally.
• 00:58 Or I can open this up and hope that I can get to a wide enough horizontal basis
• 01:03 to display all these characters.
• 01:05 As it happens, I'm going to do both.
• 01:07 We've opened up the chart, that's one way.
• 01:09 The next thing I'm going to do is I'm going to format this so
• 01:11 that it actually comes up a little bit shorter.
• 01:13 And I have two ways I can do this, one, I can right click, go to Format Axis.
• 01:19 I'm going to collapse the access options go to numbers,
• 01:23 I could set a custom number format right here under format code.
• 01:26 Or if I want to, I can also go back to my cells themselves, right-click,
• 01:32 Format Cells, and I could write my own custom number format in this area.
• 01:37 So if I go to custom, we can go with MMM, and
• 01:41 then a little tick, and then we can go in with YY for
• 01:45 a two digit year, so I get GEN-23 or '23 rather.
• 01:50 So there we go,
• 01:51 that works you'll notice that it's linked into the chart this makes it a lot easier
• 01:55 to display a narrower version without having that weird stuff that's going on.
• 02:00 Now, the next challenge that I want to deal with is the fact that these things
• 02:04 are plotting in the middle of these bucket barrels.
• 02:06 That's not really ideal, I want to see January on the left here on this
• 02:10 tick mark, then February and I want these plot points to match those exactly.
• 02:15 So to do this, we're going to go right click.
• 02:17 Format Access.
• 02:18 And on this one, what I'm going to do is, I'm going to plot my axis position not
• 02:22 between the tick marks which is the default, but rather on tick marks.
• 02:26 And this will actually make sure that I don't have any extra white space there,
• 02:31 and we can now see that January I was here, February I was here,
• 02:34 March I was here, not bad.
• 02:36 Next up, let's add something that makes it just a little bit more
• 02:40 obvious where those points actually are.
• 02:43 So we're going to go and select our line.
• 02:46 We'll right click on it, and what we're going to choose, Add Data Labels.
• 02:51 No, that's not great, we're going to go to Format Data Series.
• 02:55 And in our Format Data Series,
• 02:56 we're going to look through the different options we have here.
• 02:59 We've got some shadow glow soft, that's not going to help me.
• 03:02 We've got a bucket over here, and under this bucket we see line and
• 03:07 we see markers, it's kind of buried.
• 03:10 Now, the nice thing about this is that I do have some marker options and
• 03:13 I can go with some built in marker options.
• 03:15 So in this case, we get a nice blue square on every one of my actual revenue lines.
• 03:20 So that looks good, I can see what's going on there.
• 03:23 I can do the same thing here, for my orange line for budget,
• 03:27 I can come over here to the bucket, go to marker,
• 03:30 and maybe I want to choose a different marker type.
• 03:33 So we'll go built in and we'll choose this little diamond right here.
• 03:38 Now, I do have the option with my markers that I can actually go and
• 03:41 change the fill color, so I can set this up with no fill, so
• 03:45 that it's going to end up being a little bit I have a little hole in the diamond.
• 03:49 And I could put a solid line around it,
• 03:51 that maybe we go with something like black just to make it a little bit different.
• 03:54 And when we go and we take a look at it,
• 03:57 you can see that we've got a different style of marker here.
• 04:00 But I normally do that, probably not but hey, this is all about features, right?
• 04:05 That is one thing that I don't like about this chart right now.
• 04:09 And that is that the most important factor that's on this chart for me is revenue.
• 04:12 I'm more interested in revenue than budget.
• 04:14 And what you can see if you look at these lines really,
• 04:17 really carefully is that the budget line is on top of the revenue line.
• 04:20 So if there's ever a conflict, I actually can't see my revenue.
• 04:22 I have to assume it's pretty close to budget.
• 04:25 I want to bring this line to the front.
• 04:28 In order to do that, we actually need to change the way the data's plotted because
• 04:31 what happens is it plots in order.
• 04:33 So the first thing it does, it plots actual from the top, and
• 04:36 then it writes the budget line on top of that, but I don't want to resort my data.
• 04:41 So what I'm going to do is I'm going to go to my chart design and
• 04:45 I'm going to go to my select data option here.
• 04:48 And what I'm going to do is I'm going to grab this, and just move this down,
• 04:53 say plot budget first and then do actual.
• 04:55 And when I go and say OK,
• 04:57 what you'll notice now is that my blue line crosses over top of the orange line
• 05:01 wherever there is a conflict where it's too close like that.
• 05:05 So this is how you can reorder your data series on your chart without having to
• 05:10 actually resort your entire data table.

Lesson notes are only available for subscribers.