Building Column Charts

Quick reference

• 00:04 In this video we're going to look at creating an effective column chart.
• 00:08 Now, since about Excel 97 (well, even earlier) Excel's always done charting but it's made horrendous charts by default.
• 00:16 So one of the things that we want to do is we want to go and actually convert a chart...
• 00:21 we're going to create one first
• 00:22 but then we're going to convert it to make it more effective at telling the story that we actually want to tell.
• 00:26 The way we do that is we remove all the excess noise that Excel is sort of famous for putting in,
• 00:31 trying to get rid of all the extra ink and really get it back to
• 00:35 just the key elements that actually tell the story of what we are really trying to convey to the user.
• 00:40 So, the first step of that is we are going to go and build a column chart
• 00:43 and then we are going to go and make a bunch of modifications, and I'm going to show you how
• 00:46 many things we can actually change in our chart set up.
• 00:50 So the first step that we need to do is we need to select our data.
• 00:53 So we will go and select our data range.
• 00:55 We've got our different animals down the side here and how many we've sold in the month of May for Old MacDonald's farm.
• 01:01 What we're going to do to create a chart is go to the Insert menu.
• 01:04 And you can see that we have all of our different chart types along the way here
• 01:08 So we click on column
• 01:10 and you can see that we have all kinds of different options here.
• 01:13 Now the first thing I am going to tell you is, to make an effective chart you should always skip everything below this line right here.
• 01:18 Avoid the 3D because it distorts perspective, really hard to read here
• 01:22 so you don't want to ever really use these cone or pyramid or any of these other chart types that
• 01:27 can actually mess up your perspective, because your brain's really bad at interpreting angles.
• 01:32 Most of the time for the work I do with charting I always start in the top left
• 01:36 with a plain old boring looking two dimensional column chart because... they work.
• 01:40 So, we're going to click on that.
• 01:41 And it creates a chart that's not bad.
• 01:44 But you can see that this has got a few extra things that we really don't need.
• 01:47 Our chart says May at the top; it says May on the right hand side. This is kind of redundant information over here.
• 01:52 So let's get rid of it.
• 01:54 To do that we click on it
• 01:55 we've got little handles all the way around the outside
• 01:57 we press the delete key on our keyboard ... and it goes away.
• 02:00 Simple as that.
• 02:02 I am going to go and I am going to add some information to our bar columns that we have here.
• 02:08 I am going to right click on this and I am going to say add data labels.
• 02:12 What you'll see is that we get data labels across the top that give us the values, the precise values for these individual columns.
• 02:19 Now with that information there I really don't actually need these
• 02:24 gridlines that go across that take us back to the vertical axis to give us an idea of what these bars are actually worth or how long they are.
• 02:33 So what I'm going to do is I'm going to select the gridlines.
• 02:36 You'll notice that we've got little selection handles on the outsides of them.
• 02:42 I'm going to press delete and they go away.
• 02:45 Well without those I really don't need a vertical axis.
• 02:48 So again I can click on the vertical axis and I can delete that as well
• 02:54 So you can see that I'm really starting to take back all the elements and get away from the particular pieces
• 02:59 that get in the way of understanding the story to really hone in on the data.
• 03:03 I am going to go in and do a couple more things here to really make this sing.
• 03:07 What we are going to do is take our data labels and select them
• 03:10 and I'm going to right click on them and say "Format Data Labels."
• 03:14 You'll notice that we get a dialog box that pops up here
• 03:18 I'm just going to move this over to the side so you can see what happens with it.
• 03:21 Now I could go and add the category name to my data labels if I wanted.
• 03:25 That would give me my beef, pork and chicken and whatnot if I wanted them on there.
• 03:28 But that doesn't really add a lot of value for me here so I'm going to uncheck that right now
• 03:32 We could put the series name on, have May on everything.
• 03:35 This could be helpful if we were doing two different column series that we're trying to work with.
• 03:40 But let's get rid of that for right now.
• 03:42 What I am going to do is I am going to click inside base for the the label position now.
• 03:46 What that will do is move the numbers all down next to the axis.
• 03:50 The only challenge that we have with this though is that they are really kind of hard to read.
• 03:53 So if I hit close on this, then go back to the home tab
• 03:58 I am going to change the font to White.
• 04:01 Then I am going to make it Bold, just to make it stand out a little bit more.
• 04:05 When you look at that, now the chart is a lot more compact and it's got everything where I need it.
• 04:11 I would like to make these bars a little bit bigger though, something to give it a little more context.
• 04:15 So right now we right click and say "Format Data Series".
• 04:20 And you can see as I click on the different components that the title bar at the top here will change.
• 04:25 If I click on the data labels now it will go to "Format Data Labels"
• 04:28 if I click on a column it gives me "Format Data Series".
• 04:30 What I am going to do is I am going to scale the Gap Width down a bit and make those bars bigger.
• 04:38 And there we go.
• 04:39 So there is lots of different things you can do to the elements of the chart.
• 04:42 The last thing I want to do is give it a better title.
• 04:44 The way I will do that is I will link it back to a cell. I'm going to click on May
• 04:48 I'm going to press equals ... and nothing really happens right now
• 04:51 but if you look in the formula bar we can see the equals here.
• 04:54 I can click on the cell. Even though nothing is happening here
• 04:57 when I press Enter it now links it to the chart.
• 05:00 Of course this isn't exactly right. I do need to go back and adjust the cell:
• 05:05 Make this May, then space, then Enter.
• 05:08 And now every time I update what's in the chart here whether I drive it by a formula or whatever
• 05:13 the chart title will update and I've got a nice clean chart with no extra noise
• 05:18 that allows the user to get right to the picture of the data immediately
• 05:21 without being distracted by anything around it.
• 05:23 Now that is an effective column chart.

Lesson notes are only available for subscribers.