Focus video player for keyboard shortcuts
Auto
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Learn how to create an effective column chart by reducing ink and 'noise' that distract from the main messages.
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 Column Charts - 20109.1 KB Building Column Charts - 2010 - Completed
11.8 KB Building Column Charts
22.6 KB
Quick reference
Topic
How to create an effective column chart by reducing ink and noise that distract from the story.
When to use
Use to remove distracting elements from your data whenever you are charting. Doing this will help you convey your message to your readers as quickly as possible.
Instructions
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 column chart
- Select cells A5:B9
- Go to Insert > Charts > Column and choose the 2-D column in the top left corner
Adding context
- Right click the data series (columns) and choose Add Data Labels
- Select the data labels, right click and choose Format Data Labels
- Set the Label Position to Inside Base
- Change the font (Home tab) to bold and white
- Select the chart title, press the equals key, click on A3 and press Enter
- Right click the columns, choose Format Data Series and set the Gap Width to 60%
Reducing ink
- Select the horizontal gridlines and press Delete
- Select the vertical axis and press Delete
- Select the legend and press Delete
- 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.