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.
Bar Charts.xlsx10 KB Bar Charts - Completed.xlsx
13.3 KB Building Bar Charts - Extra Practice.xlsx
23.1 KB
Quick reference
Topic
Building bar charts in Excel 2010.
Description
How to create an effective bar chart by reducing ink and noise that distract from the story.
Where/when to use the technique
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 (top left)
Adding context
- Select the legend and go to Chart Tools --> Layout --> Legend --> Show Legend at Bottom
- Go to Chart Tools --> Layout --> Chart Title --> Above Chart
- While the chart title is selected: press the equals key --> click on A2 --> press Enter
- Right click the data series (bars) --> Format Data Series
- Set the Gap width to 75%
- Right click the numbers in the axis --> Format Axis
- Set Axis Options --> Major tick mark type --> None
- Expand Number and set the decimal places to 0
- Right click the menu items in the vertical axis
- Set Axis Options --> Major tick mark type --> None
- Right click the gridlines in the chart --> Format Gridlines
- Choose Line Style --> Dash Type --> the first row of dots
- Expand the chart so that all rows are readable
Re-sort the data
- Select A4:C24
- Go to Data --> Sort and Sort by Revenue --> Smallest to Largest
- 00:04 You can see that in this file we have a nice listing of revenue and
- 00:08 costs by product.
- 00:10 And what's the perfect way to actually demonstrate the relationship between
- 00:13 these things.
- 00:14 And how they're comparing against each other for for this particular month.
- 00:17 Well, there's really no better way than using a bar chart.
- 00:20 So let's take a look at how to create a bar chart to effectively show
- 00:24 how these products are comparing against each other.
- 00:27 The first thing that we're gonna do is we're going to select the series of
- 00:31 data that we have.
- 00:31 So we're gonna start at A4 and drag all the way down to C24 to try and
- 00:36 grab the entire list of data.
- 00:38 And then what we're gonna do is we're gonna go to the Insert tab, and
- 00:43 we're gonna go to Bar Chart.
- 00:44 And we're gonna grab the 2-D bar in the top left-hand corner.
- 00:49 And what you can see is that we get this, you know, less than ideal bar chart here.
- 00:53 It's got all kinds of stuff that's stacked all over top of each other and what not.
- 00:56 So, let's do a little reformatting and
- 00:58 get this looking a little bit prettier a little bit more effective.
- 01:01 The first thing I like to do is drop the legend down underneath the chart.
- 01:04 So we're gonna select the Legend, go to Layout > Legend > Show Legend at Bottom.
- 01:12 Well that helps a little bit already.
- 01:14 The next thing that I like to do is I always like to make sure I have a chart on
- 01:17 my title, or a title on my chart rather.
- 01:19 So we're gonna go to Chart Title > Above Chart and
- 01:23 that gives us one that's extremely
- 01:24 Extremely helpful, it says Chart Title.
- 01:26 So while it's still got these little dots around it we'll just press equals and
- 01:30 link it back to cell A2.
- 01:32 So equals A2 and Enter.
- 01:33 Now that's a little bit better, gives us some context.
- 01:38 I'd also like to make some changes to some of these things.
- 01:40 I don't like the way the numbers are showing here, too many decimal places.
- 01:43 So, what I could do is I can actually go back to my original set of data here,
- 01:47 select the values.
- 01:50 And then I can go up and hit comma and drop the decimal two places, and
- 01:53 you'll see that that takes effect on the chart right away.
- 01:58 Now, for the next trick, you'll notice that
- 02:00 only half of our items are showing up in here, in this legend here.
- 02:04 So what we're gonna is we're gonna just expand the, the height on our chart.
- 02:09 By dragging the bottom border down.
- 02:11 And that's gonna allow more items to show up here.
- 02:13 But it also gives us a little bit more white space between these things.
- 02:16 And it's a little bit hard to read some of these.
- 02:17 So, I'm gonna expand the width of these bars, so
- 02:20 that they show just a little bit more color.
- 02:22 This is, breaks the normal rule that I have,
- 02:24 where I'm actually trying to get rid of color.
- 02:26 I'm gonna add a little bit more here, just to help draw the draw the, the eye to it,
- 02:31 so we can see what's going on.
- 02:32 So we're gonna grab one of these guys.
- 02:34 We'll just right-click on the the series and say Format Data Series.
- 02:39 And let's move this across side so we can see what' s happening.
- 02:42 I'm gonna change the gap width to instead of 150, I'm gonna experiment.
- 02:47 We'll try 75%.
- 02:48 Hit Tab.
- 02:49 And that makes it a little bit more, a little more dense,
- 02:52 a little bit more saturated there so it helps me figure out what's going on.
- 02:56 Now, there's some other pieces of it I don't really like.
- 02:59 I've got these extra little tick marks on the side of one these axis.
- 03:02 I don't really need those.
- 03:03 I'm gonna get rid of them.
- 03:04 So we'll just we've clicked on Format Axis.
- 03:06 We're coming over to Major Tick Marks, we're gonna say None.
- 03:09 Don't need those there.
- 03:10 I really don't need the major tick marks on the bottom here either so I can go and
- 03:13 grab those.
- 03:15 And you'll notice that my tick marks are gone from left, and
- 03:17 I'm gonna get rid of these guys that are going across here now as well.
- 03:21 None. I'm gonna soften these lines
- 03:24 a little bit, too.
- 03:25 So we're just gonna click on the grid lines.
- 03:26 You'll notice I'm not changing,
- 03:27 I'm not getting rid of this, this format access dialog box.
- 03:31 It's still, still staying there and as I click through all the different elements
- 03:34 it's sticking around, which is kinda nice.
- 03:36 So I can click on Line Style and I'm gonna change this dash type to this dotted line.
- 03:42 And that just softens it up a bit so it's not quite so in your face there.
- 03:45 So we add ink where we need and we take it away where we don't.
- 03:48 So that starts to look a little bit better.
- 03:52 Something that I could actually use and put in front of someone.
- 03:54 But you know, one of the things I'd really like to see is which is my
- 03:57 item that has the biggest amount of sales.
- 03:59 I can see it's chicken nachos in here, but
- 04:01 it's kinda hard to compare who's bigger than all the rest of them.
- 04:05 So the way we deal with that is to sort our original table of data.
- 04:08 So we'll click somewhere in the table, we'll go to the Data tab >Sort.
- 04:13 And you'll notice that because I've got some good headers at the top of my list
- 04:17 here it actually puts my titles right into the sort by fields.
- 04:20 So we'll choose revenue from smallest to largest.
- 04:22 And watch what happens to the bars when I click OK.
- 04:25 They sort from largest all the way down
- 04:28 to the smallest which is in exact reverse order of what my data table shows.
- 04:32 So obviously if I flip this up and sorted it from largest to smallest.
- 04:36 These bars would actually be showing larger ones down here and
- 04:38 the smaller ones at the top.
- 04:40 The cool thing about this is that with this particular data set
- 04:44 The food cost, which is relative to the red line here,
- 04:46 should have been around 30 to 35% for the restaurant in question.
- 04:51 So now we can see that we've got a real good indicator of what the top
- 04:54 sellers were.
- 04:55 But we can also see that one of our products is
- 04:58 way out of line now that we've got it sorted in.
- 05:00 So there you go.
- 05:01 Some good management information out of our chart.
Lesson notes are only available for subscribers.