Building Column Charts

Quick reference

• 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.