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 Charts27.2 KB Building Column Charts - Completed
32.2 KB
Quick reference
Building Column Charts
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 --> Recommended Charts and select the Clustered Column chart
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
- 00:04 We're now going to focus on charting in Excel.
- 00:08 One of the things that we always want to keep in mind as we're developing charts is
- 00:12 that every chart should tell a story as quickly and effectively as possible.
- 00:17 Every extra chart element can create noise and get in the way of that story.
- 00:21 And it's therefore a best practice to remove as much excess ink or
- 00:25 noise as we can.
- 00:27 So that the reader can actually look at the chart,
- 00:29 get in, get the information that they need, and get out as quickly as possible.
- 00:34 Now, we're gonna start here by charting Old MacDonald's sales.
- 00:37 And to do that we're gonna click inside our data and
- 00:40 we're gonna choose insert Recommended charts.
- 00:44 Microsoft comes back with some recommendations and
- 00:47 while they're not always perfect, they give a good starting point.
- 00:50 Notice that this time,
- 00:51 it's chosen a clustered column chart, which happens to be exactly what we want.
- 00:56 So we're gonna say okay.
- 00:57 And it creates not a bad looking chart.
- 01:01 Now, there are some challenges with it though.
- 01:03 The first one, it's given us a series name for a title, May.
- 01:06 That's not super helpful.
- 01:08 I would like to link this back to the cell A5.
- 01:11 Now, the key for this If you select this box and you press equals, and
- 01:15 if you see the equal sign showing up here, you haven't done this right.
- 01:20 So what you should do is press Escape, click outside the chart and
- 01:23 then try again, one click.
- 01:26 Now, when we press equals, notice that in the formula bar,
- 01:29 we see the equal sign, we can now go select cell A5.
- 01:33 And hit Enter, and it will link the chart title directly to the cell.
- 01:37 So if the cell ever changes it flow right into the chart.
- 01:41 Some other things that I like to do to my charts, I'm not a big fan of all these
- 01:45 labels down the side and all these bars, that's a lot of extra ink.
- 01:49 So instead I'm gonna right-click on my chart here and
- 01:52 I'm choose to add data labels.
- 01:54 And that puts nice precise data labels on the top which removes the need to
- 01:58 approximate based on these bars.
- 02:01 And now that I have these values,
- 02:03 I can select this axis, press the delete key, and it goes away.
- 02:08 I can also click on one of the vertical or horizontal bars here, and
- 02:13 delete those too.
- 02:14 Because they're no longer necessary.
- 02:17 Now, what about these data labels?
- 02:19 I know, I'm gonna right click on them here, and
- 02:22 I'm gonna choose format data labels.
- 02:25 I don't think they really need to be at the top, so I'm gonna change it.
- 02:29 To be inside base and that will put them down near the bottom.
- 02:33 But now they're hard to see.
- 02:35 So, you know what, we'll go to home tab and this is just a font so
- 02:39 we'll change the font to white.
- 02:41 And maybe we'll make it bold and I can even increase the font size by a couple
- 02:45 points to really make them stand out.
- 02:48 But now, these bars they're kinda narrow, so why not do this?
- 02:53 Right click, format data series,
- 02:55 when you do this you're taken into this area where you can change the series.
- 02:59 Degrees overlap, which isn't relevant here,
- 03:01 because we've only got a single series of data.
- 03:03 But we can also change the gap width, and this is the gap between these bars.
- 03:09 So if I reduce this, it should make the bars a little bit wider.
- 03:12 So let's try that, we'll knock this gap right down to, say, 60%.
- 03:16 And just like that, the chart opens up a little bit more, so
- 03:20 I can see more information.
- 03:23 At the end of the day, this is a pretty good chart.
- 03:25 I can now grab it, I can move it around.
- 03:27 If I hold down my alt key when I'm moving it,
- 03:29 I can snap it to the corners of the actual cells.
- 03:32 And I can do the same thing here when I left click.
- 03:34 If I hold down my alt key now when I move it around you can see that it's gonna snap
- 03:38 into grid quite nicely.
- 03:40 And at the end of the day I've got a beautiful chart that will update
- 03:43 when the data changes including the title.
Lesson notes are only available for subscribers.