- 720p
- 540p
- 360p
- 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 - 20139.1 KB Building Column Charts - 2013 - Completed
14.2 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 > 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 Right now we're going to look at building an effective column chart inside Microsoft excel 2013.
- 00:10 Now, since Excel 97 and earlier, Excel has always made charts but it's made horrendous looking charts.
- 00:18 Now that is definitely getting better as time moves forward but even with all the changes that Microsoft have done
- 00:22 and some significant ones as far as recommended charts in 2013
- 00:27 there's still some work that can be done.
- 00:29 What we want do is make our chart truly effective,
- 00:32 and the way that we do that is we reduce of all the extra ink that is on the chart and try
- 00:37 and get down to where we're just conveying key information that the user needs to actually be able to get the story that we're trying to convey.
- 00:44 So, what we are going to do is create an effective column chart here
- 00:49 based on animal sales and the quantity of the animal sales in May.
- 00:52 To do that I'm going to select the range of data that I need
- 00:57 I'm going to go to the Insert menu which is where I create all my charts,
- 01:00 and I'm going to click the Recommended Charts button.
- 01:04 And what you'll see is that we get 3 different recommended charts:
- 01:07 We get pie chart,
- 01:07 we've got a bar chart here,
- 01:10 and we've got a column chart.
- 01:11 So we're going stick with the column chart for right now.
- 01:13 I'm going to click on Clustered Column, say OK,
- 01:17 and it will create - not a bad looking chart - but one that can still be tweaked and modified.
- 01:23 So, the first thing we're going to do it is we're going to go and we're going to add data labels.
- 01:28 I'm going to add a little bit of ink to this.
- 01:30 So I'm going to right click on the chart here, and I'm going to choose add data labels.
- 01:34 When I do that, it goes and puts some numbers above my individual columns.
- 01:40 Well that's cool. With those in place, I don't really need
- 01:45 these drawing lines across the back to give me scale.
- 01:48 I also really don't need my axis down the left hand side here because I've got the values right with my bars.
- 01:54 So I'm going to get rid of all these. What I'm going to do is I'm going to click on one of these axis lines.
- 01:58 Oops, not that one, let's get this one here.
- 02:00 See, the difference here is that now I've got these little dots on both sides...
- 02:04 the other one was the whole chart area; we don't want to delete that.
- 02:06 Buts these little dots? I'm going to press Delete, and they're going to go away.
- 02:12 Now, the other thing I'm going to do is
- 02:13 I really don't need this any more, especially without those lines, there's no context.
- 02:16 So we're going to click on our vertical axis
- 02:20 select it and click delete, and it will go away.
- 02:25 Now this is great but you know what
- 02:26 I'd rather have these little data labels inside the bottom of my data bars. I think that would look better.
- 02:31 It's not
- 02:32 it would compact it a little bit, make it a little bit cleaner so...
- 02:35 We'll select our data bars -- or data labels --
- 02:38 we're going to right click on them and I'm going to say "Format Data Labels."
- 02:43 At that point Excel will fly out the 2013 dialog that allows to work with these things.
- 02:48 There's all kinds of different formatting and fill options and what not.
- 02:51 But basically where we want focus on right now is... what happens in this area.
- 02:56 So, we can choose different values here. We can put "Series Name" on... that would give us the date.
- 03:01 We could put the "Category Name" on as well, which would tell us the categories as well.
- 03:05 I'm going to leave the values in here. I'm not so worried about the leader lines, as it doesn't have any effect here.
- 03:10 And what I'm going to do is I'm going to change the label position to "Inside Base"
- 03:14 and that snaps it down inside the chart.
- 03:17 Now, the only challenge I've got with this is that it's really hard to read these things because they're black on blue...
- 03:22 so to deal with that
- 03:24 what I'm going to do is go to the Home Tab
- 03:28 go over to Font, choose a white font, and make it Bold.
- 03:35 And that makes it stand out a little bit more.
- 03:38 The last thing I want to do I want to make these bars... well, the second last thing is...
- 03:41 I want to make these bars a little bit thicker here.
- 03:42 Give a little bit more context to what I'm working on.
- 03:45 So when I click on those bars, you'll see that the "Format Data Series" tab pops open
- 03:50 And what I'm going to do is I'm going to change the "Gap Width", and I can change it by changing the number right beside me here
- 03:55 or I can move this and see what ends up happening.
- 03:58 So this makes them thinner... this way here makes them thicker
- 04:01 And now I've got some nice thick bars there
- 04:03 which will scale a little bit for me when I actually go and move my chart and make it a little bit more compact.
- 04:09 Now the last thing that I want to do is I want to give this chart a title that actually has some good context
- 04:14 and right now I've got Number of Animals Sold (2012)
- 04:18 What I'm going to do is I'm just going to change this right now
- 04:21 a simple way here -- you can write this with a formula if you like -- Number of Animals Sold in May 2012.
- 04:27 I'm going to link this to my chart
- 04:29 by clicking on the title
- 04:31 I'm going to press equals
- 04:33 and this drives me crazy... nothing happens up here but it does show up in the formula bar
- 04:39 and at that point I can click on the cell reference
- 04:42 and press Enter
- 04:43 and we now have "Number of Animals Sold (May 2012)"
- 04:47 When I go and update this later on
- 04:49 -- and I would use a formula to drive this --
- 04:52 but when I update it later on that will feed to the chart just fine as well.
- 04:57 That's how to go through and create a truly effective column chart in Excel.
- 05:01 There's no extra noise, no extra anything, it just gets right to the meat of the data that we're after.
Lesson notes are only available for subscribers.