Locked lesson.
About this lesson
How to create an effective bar chart by reducing ink and noise that distract from the story.
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 Bar Charts.xlsx27.6 KB Building Bar Charts - Completed.xlsx
31 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 In this video, we're gonna look at using a Bar Chart to compare two
- 00:08 different series of information against each other.
- 00:10 And as we do it we're gonna try and make sure that we actually create a nice,
- 00:14 effective chart.
- 00:15 So let's go and see what Microsoft recommends for this.
- 00:18 We'll go to Insert, and we'll choose Recommended Charts.
- 00:23 And just like that, we actually get this nice little chart here that shows us
- 00:27 some bars and it apparently has both cost and revenues.
- 00:30 Now I'm not a big fan of the title, Chart Title.
- 00:33 But that's okay, because we know we can change that.
- 00:35 So I'm gonna say, OK, and I'm gonna go and drop this chart.
- 00:39 And we'll stick it right under the top corner of the cell here, perfect.
- 00:43 Now, the first thing that I wanna fix, this Chart Title.
- 00:46 So I'll click on it once, press =, and link it back to sale A6, and press Enter.
- 00:53 And now I have a much better Chart Title to go with my data.
- 00:56 Now at this point, I'm gonna scroll down to try and
- 00:58 get a little bit more of my data set on screen here.
- 01:01 So we can now see the entire data set that I have.
- 01:04 You'll notice that there's not nearly enough sales items here.
- 01:07 Because the sales item text is large and
- 01:09 the bars are kind of thin, it's not giving me everything.
- 01:13 So what I'm gonna do is I'm gonna expand my chart, and
- 01:16 I'm gonna hold down my Alt key to try and snap it to my rows.
- 01:20 And there we go, I should now have all of the different items,
- 01:23 even though it still looks pretty tiny.
- 01:26 Part of the issue that I'm dealing with here,
- 01:27 there's a lot of white space between these bars.
- 01:29 And I'm thinking that I should probably try and
- 01:31 get these to be a little bit thicker.
- 01:33 So what I'm gonna do is I'm going to right click on anyone of these blue bars,
- 01:35 and we're gonna choose Format Data Series.
- 01:39 At this point, you'll notice that I have my series overlap,
- 01:41 my gap width, so I'm gonna go and change the gap width here, and make it about 75%.
- 01:46 And at that point, I get a little bit more color, and I might look at this and
- 01:51 say well, maybe I wanna have a little bit more room on these guys here.
- 01:56 I have two options, I could either go on select the text, and
- 02:00 decrease the sizes, lower than 9.
- 02:03 I'm gonna make it pretty small, or I could actually go and
- 02:06 say, maybe I wanna make my chart just a little bit taller here.
- 02:10 So go on expand a couple more rows,
- 02:12 you can see that it's a little more room to work with.
- 02:16 Now there's a couple more issues with this chart as I'm looking at it,
- 02:20 I'm trying to figure out how to solve them.
- 02:22 Number one, I can't get good insights out of this because it's very, very wavy.
- 02:26 So what I'd like to do is I'd like to try and
- 02:28 sort the top selling items to the top of the chart.
- 02:32 So naturally, your thought would be, well, I can come over to the revenue column, and
- 02:36 I can go and say, Data, and we'll gonna Sort it Z to A.
- 02:40 So the highest values come at the top.
- 02:42 And it might surprise you, but
- 02:44 this is actually exactly opposite what you need to do.
- 02:48 Excel actually starts plotting the first row of data at the bottom of the chart,
- 02:52 and then every row of data, it build it at the top.
- 02:55 So if you actually wanna have this showing up with your bars at the top,
- 02:59 you need to go and Sort A to Z instead.
- 03:01 And that will change it around.
- 03:04 Now on a chart like this I'm not a big fan of using data labels.
- 03:07 There's just way too much information.
- 03:09 So we do need the approximation bars here.
- 03:11 But the question we have to ask is, do we really need all of these decimals on here?
- 03:16 And I don't think we do.
- 03:17 So what we're gonna do instead is we're gonna right-click,
- 03:19 and we're gonna say Format the horizontal Axis.
- 03:23 You'll notice that it takes us into the Axis options, and
- 03:26 when I scroll down to the bottom, I have this collapse here I called Number.
- 03:30 I'm gonna open it up, and then I have to scroll down again.
- 03:33 And you'll notice that I can actually play around with
- 03:36 custom number formatting codes if I want to.
- 03:38 Fortunately this one's easy.
- 03:40 I'm gonna go and just say decimal place is 0,
- 03:42 and at that point when I hit my tab key it actually knocks the 0s off of it.
- 03:48 So now I can close my task pane and the chart that I'm looking at is probably
- 03:52 about as good as I'm gonna get for what I'm dealing with.
- 03:55 But I think it still clearly shows the message that the Chicken Nachos are our
- 03:58 biggest seller, West Coast Pizza the smallest.
- 04:01 And we can see that Halibut and Chips, even though it's not a bad seller,
- 04:05 the cost of this one is really high compared to the margin that we make on it.
Lesson notes are only available for subscribers.