Locked lesson.
About this lesson
How to create an effective bar chart by reducing ink and noise that distract from the story.
Exercise files
Download this lesson’s related exercise files.
Bar Charts - Begin.xlsx28.8 KB Bar Charts - Complete.xlsx
32.2 KB
Quick reference
Building Bar Charts
How to create an effective bar 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
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 Bar chart
- Select cells A4:C24
- Go to Insert --> Charts --> Bar Charts and select the 2D Bar chart
Adding context
- Expand the chart so that all rows are readable
- Select the chart title, press the equals key, click on A2, and press Enter
- Right-click the data series (bars) and choose Format Data Series
- Set the Gap width to 75%
- Select the numbers in the axis
- Click the bar chart icon in the Format Axis task pane
- Expand NUMBER and set the decimal places to 0
Re-sort the data
- Select A4:C24
- Go to Data --> Sort
- Sort by Revenue --> Smallest to Largest
- 00:04 All right, I've got some food and beverages sales data here and
- 00:07 I'd like to chart it.
- 00:08 So what I'm going to do is I'm going to select the data table, go to Insert, and
- 00:12 I'm going to check out what kind of chart does Microsoft recommend.
- 00:16 And right away they come back with a clustered bar chart.
- 00:19 Now this is, in my opinion, the right choice.
- 00:22 Why?
- 00:23 Because I've got lots of data labels and it's very easy to read them when they're
- 00:27 spaced out like this horizontally, versus a column chart where they would actually
- 00:31 rotate everything 90 degrees and I have to turn my head to read the report.
- 00:35 So we're going to go back over to our clustered bar chart here and say OK.
- 00:40 Now it puts it on the worksheet for me, and the first thing you're going to notice
- 00:44 is it has a very insightful chart title called Chart Title.
- 00:48 I'd like to fix that.
- 00:50 So I've selected it.
- 00:51 I'm going to press equals and I'm going to link this back into a cell so
- 00:55 that I can get it to say food sales June 22, perfect.
- 00:59 The next thing you're going to notice about this is there are a whole lot
- 01:03 of data points, but not many category labels, and
- 01:06 that's kind of problematic because we're missing some things.
- 01:10 I see avocado bot, where's my buffalo chicken pizza?
- 01:13 It is not showing up here.
- 01:15 This happens when there are too many items to show on the axis versus selectively
- 01:19 placed them.
- 01:20 So there's two different tricks that we can use here.
- 01:22 The first of course, is just make the chart wider, so we're taller rather so
- 01:25 that it actually brings all these in.
- 01:27 The other option you have is you can configure the font sizes here.
- 01:31 So we could dial this down to say eight or even type in six or
- 01:34 four if we need to make these a lot smaller.
- 01:38 With any chart that is a bar or a column,
- 01:40 one of my favorite things to do is to add data labels to it for precision.
- 01:44 However, this one is going to leave me a little bit of a problem.
- 01:47 Because when I go and say add data labels, I get data labels on all the blue points,
- 01:51 I would now need to go and add data labels to the orange points.
- 01:54 And this is going to make this cart very, very, very cluttered.
- 01:58 So that's not really awesome.
- 02:00 In this case, I'm going to press Control Z,
- 02:02 and I'm going to leave this with the approximation bars, but I'm not loving
- 02:05 the access with these extra decimal points, they just don't seem necessary.
- 02:10 So what am I going to do?
- 02:11 I'm going to grab this, right click on it, and choose to format axis.
- 02:16 And within here, I'm just going to collapse the Axis options for a second,
- 02:21 I'm going to open up Number.
- 02:23 Currently this is formatted as a counting with two decimal places, and
- 02:26 that's because that's the way the data is formatted, but I have the option of coming
- 02:30 back and saying zero and actually dropping that off.
- 02:33 Or even if I want to I could actually write my own custom number format.
- 02:38 I'm not going to bother with that right now,
- 02:39 I'm happy enough that I've dropped my zeros off of this, so that looks better.
- 02:44 But now the next thing is I want to be able to actually sort my data so
- 02:48 that my highest sellers are at the top.
- 02:51 I know that's chicken nachos, but
- 02:52 I want to see what everything else is that's going on here.
- 02:55 So how do you do it?
- 02:56 Well, the answer is you sort the data.
- 02:59 So let me grab the data.
- 03:01 And what I'm going to do is go to Data and Sort.
- 03:05 And I'm going to sort so that my revenue has, of course, I want the largest
- 03:09 number on the top, so I'm going to sort from largest to smallest.
- 03:14 And what I'm going to do is I'm going to press OK.
- 03:16 And indeed it sorts the data table with revenue at the top and
- 03:20 the chart with the smallest one at the top.
- 03:23 And this is just a little bit of a mind bender, but the reality is your chart
- 03:27 plots opposite to what your data is, which is a little bit frustrating.
- 03:31 So to fix this, what you can do is you can come back just sort this to say smallest
- 03:35 to largest, and now we'll have our largest sales at the top of the chart, with our
- 03:39 orange bar representing the costs that have actually been put in place for these.
- 03:43 So that's not bad, I'm pretty happy, that chart is starting to tell the story
- 03:47 that I want to see, particularly that halibut and
- 03:50 chips the cost is just not worth the revenue, far too much there.
Lesson notes are only available for subscribers.