Locked lesson.
About this lesson
Column Charts are useful for comparing values to each other. This lesson explores ways to help make them as effective as possible via some of their available options.
Exercise files
Download this lesson’s related exercise files.
Column Charts.xlsx29.3 KB Column Charts - Completed.xlsx
33.9 KB
Quick reference
Column Charts
Working with Column charts
When to use
Column Charts are useful for comparing values to each other
Instructions
Reminder of 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 your data
- Go to Insert --> Charts -->Column Charts and select the 2D Column chart
Adding context
- Expand the chart so that all rows are readable
- To link a title to a cell, select the chart title, press the equals key, click on a cell and press Enter
- Right click the data series (Columns) and choose Format Data Series
- Adjust the Gap width down to make the Columns wider
- Adjust the Overlap to bring the individual series closer together
- Select the numbers in the axis
- Click the Column chart icon in the Format Axis task pane
- Expand NUMBER and change the number formats
Re-sort the data
- Select the source data from the original table
- Go to Data --> Sort
- Sort [Your chosen field] --> Smallest to Largest
Hints & tips
- Remove items and elements that you don’t need by selecting them and pressing the Delete key
- A common addition is to right click a series and choose Add Data Labels
- Change font sizes by selecting an element and formatting it from the Home tab
- When removing gridlines, make sure you are selecting the gridlines and not the entire chart
- 00:04 Like bar charts, column charts are good for comparing values to each other.
- 00:09 I prefer to use them, though, when we get multiple series like this, where we've got
- 00:13 May and June and we want to compare the values from the individual product lines.
- 00:17 I prefer a column chart versus a bar chart because it ends up with them standing up,
- 00:21 which makes them look kinda cool.
- 00:23 So we're gonna go grab this data.
- 00:25 We'll go to Insert and
- 00:27 this time we're gonna go grab our column chart, 2D column chart.
- 00:31 And what we end up getting is something that looks like this.
- 00:35 Now this is not ideally the way that I would like to see this.
- 00:39 I'd actually like Tents and Shelters and Apparel and Food group together.
- 00:43 So the first thing that I'm gonna do is I'm gonna say, well that was close but
- 00:46 I'm gonna switch the row and column data.
- 00:49 And when I do that it switches these things out so they look good.
- 00:53 So, if your data is about 90 degrees off, just click that switch row column data and
- 00:57 everything will start to look a little bit nicer.
- 01:00 So that's a cool start.
- 01:01 Now, again, there's some other things that I don't like about this though.
- 01:05 I'm happy with the legend down the bottom.
- 01:07 Chart title not so much.
- 01:08 But let's give it a cool chart title.
- 01:10 But this time, I wanna add a twist.
- 01:12 We're gonna say this is gonna be called =Revenue Summary.
- 01:17 But I also wanna add the year, but on a new line.
- 01:19 So here's a secret.
- 01:20 We're gonna say &Char(12),
- 01:24 like the number of months in the year, &"2017 Sales".
- 01:31 Now you can get away with different chart characters as well, these are ASCII
- 01:34 characters, but what I like about this one is it actually shows a little box with
- 01:37 a question mark so you can see that there is something funky in there.
- 01:40 But why do you want that?
- 01:42 Well, check this out, equals grab our chart title and
- 01:47 Enter, and it actually puts in a line break for header, which is pretty cool.
- 01:52 So again, we could base this off of other cells or whatever else which would make it
- 01:55 look good, too, but it's a nice way to get that line break header where we want it.
- 01:59 Pretty nice.
- 02:00 Now, let me see, what else can I do here.
- 02:02 Let's go right click.
- 02:03 We'll add data labels to the blue series and to the orange series.
- 02:08 That looks good.
- 02:09 We can get rid of the data labels here that we don't need any more.
- 02:12 So, let me go click on these grid lines, they're no longer relevant.
- 02:15 We'll get rid of those as well.
- 02:17 And overall, it's actually not a bad looking chart.
- 02:21 Could I do some other things with it?
- 02:23 Sure, I mean, I could make the chart a little bit wider,
- 02:26 that's gonna make it a little bit bigger.
- 02:27 I could go in, say, let's say, format the data series.
- 02:31 Maybe I could make the series overlap.
- 02:34 Let me see, what happens if we go one way, it'll actually put them closer together.
- 02:39 So and actually overlap.
- 02:40 I'm gonna put a small gap in here.
- 02:42 About 11%, but I could also shorten again the gap width and
- 02:45 make these bars a little bit wider.
- 02:47 This gives me the option to grab the data labels.
- 02:50 And if I want I could actually go and say, you know what?
- 02:54 Why don't we go and click on the label options and
- 02:58 this time, we'll force these ones to be inside base.
- 03:03 Now, I'm gonna do the same thing with the other ones because I wanna show a little
- 03:07 bit of a challenge here.
- 03:08 Working with these things, the color doesn't always work.
- 03:10 The nice things about this is that when you're working with these data labels,
- 03:14 they actually can take your pieces from the font tabs.
- 03:17 So you could go and you can set them to white and bold and
- 03:20 actually make them a little bit more presentable if you wanted to do that.
- 03:24 So again every chart gets customized to look the way that you want it to look.
- 03:31 But ultimately you can play around with these things and
- 03:33 really make them look special.
- 03:34 And that's kind of a nice key when working with charts.
- 03:37 So this is a nice dual column chart that should work nicely for
- 03:42 a dashboard as well.
- 03:43 Cuz it gives you once again nice clear columns to show you that
- 03:48 in May the largest value was tents and shelters.
- 03:51 In June it was apparel and then we can actually look at the values and
- 03:54 get exactly how much out of them.
- 03:55 We got a nice title that I'll dynamically update.
- 03:58 This is a pretty clean good chart that I would be really happy to put on
- 04:01 a dashboard.
Lesson notes are only available for subscribers.