Focus video player for keyboard shortcuts
Auto
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Bar Charts are useful for comparing values to each other. This lesson examines features that can be tweaked to optimize their consumption.
Exercise files
Download this lesson’s related exercise files.
Bar Charts29.2 KB Bar Charts - Completed
33.7 KB
Quick reference
Bar Charts
Working with Bar Charts.
When to use
Bar 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 Bar chart
- Select your data
- Go to Insert --> Charts -->Bar Charts and select the 2D Bar 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 (bars) and choose Format Data Series
- Adjust the Gap width down to make the bars wider
- Select the numbers in the axis
- Click the bar 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 Let's focus on building a good effective bar chart with no noise to
- 00:09 really clearly convey our story.
- 00:12 So we're gonna start by grabbing our product line sales data here.
- 00:16 And we're gonna go to the Insert tab and you'll notice that in Excel 2013 and
- 00:21 higher you have this option for recommended charts.
- 00:24 It's not there in Excel 2010.
- 00:26 If you were to click it, you'll notice that it gives you some options for
- 00:30 some pretty decent charts.
- 00:31 But I'm gonna say Cancel on this one and
- 00:33 go back to create a bar chart the manual way.
- 00:36 Underneath my Columns here I can see 2-D Column and
- 00:39 I can see the 2-D Bar Chart, which I'm gonna choose.
- 00:42 The only difference between these two, the bar chart is horizontal,
- 00:45 the column chart is standing up.
- 00:48 Now, depending on the version of Excel you're in,
- 00:52 you'll have some different elements that might show up on the canvas here.
- 00:55 If you're missing anything under the chart tools,
- 00:57 you can actually find the ability to add chart elements.
- 01:00 I know for sure that in Excel 2010,
- 01:02 you used to have a legend that would show up here.
- 01:06 Well we don't actually need this, so I'm gonna select it, and
- 01:09 I'm gonna delete it by pressing the delete key.
- 01:11 And here's your first charting tip, is that any element on your chart,
- 01:15 you can select it, press the delete key, and it'll go away.
- 01:17 So that's how we start making some of this chart noise disappear when it's no longer
- 01:21 relevant.
- 01:21 The second thing is, I want to adjust the title.
- 01:25 Now if you don't have one, you can add your chart element under Chart Title, and
- 01:29 you can have None, Above the Chart, or Centered Overlay.
- 01:32 I prefer Above Chart.
- 01:34 But I'd like to link that back to a chart title in a worksheet here.
- 01:37 A chart title looks something like this, =May&" Sales", Enter.
- 01:46 So this is now a formula so
- 01:47 that every time May updates it'll update the sales here.
- 01:51 Rather than have this hard-coded value in a chart, I'm going to say equals.
- 01:55 And this is key.
- 01:56 Notice the equals shows up in your formula bar.
- 01:59 It does not show up in the text label.
- 02:01 If it shows up in the text label, it's not working right, you're typing text.
- 02:04 But if it's in the formula bar only, we can now select the cell and say enter.
- 02:10 And now we get a nice little chart title that will update.
- 02:12 As you'll see, I'm just gonna change my formula here.
- 02:15 Instead of going with sales, we'll go with revenue.
- 02:18 That updates nicely, so that's pretty cool.
- 02:21 Now, what else can I do to make this more effective?
- 02:24 I know, let's add data labels.
- 02:26 This is one of my favorite things to do to a bar chart.
- 02:28 I'm gonna right click and
- 02:29 say add data labels here, and you'll notice it gives me values.
- 02:33 Now, not only is the chart already intuitive because I can
- 02:36 see immediately that tents and shelters is the biggest item, but
- 02:39 I can also see apparel is pretty close behind.
- 02:41 I now have the exact labels to work out the precise difference
- 02:44 between them should I actually need to do that.
- 02:46 So that's pretty nice.
- 02:48 But with that, I've got extra stuff,
- 02:51 like this awful legend down here that I can barely read.
- 02:53 So I'm gonna select it and press delete.
- 02:55 I don't need it anymore cuz I've got the chart with the data labels.
- 02:59 I'm gonna select the grid lines.
- 03:01 Careful here that you get the ends of each grid line selected with the dots.
- 03:06 If you only have four corners, that's the whole chart, and
- 03:08 when you press the Delete key the whole chart will go away.
- 03:10 You don't want that, right?
- 03:11 So when you have the grid lines selected we can press Delete, they're good to go.
- 03:15 Now, I'm gonna right click and I'm gonna format the data labels.
- 03:22 This, again, is a difference from Excel 2010,
- 03:24 you'd have a task bar, a dialogue box pop up.
- 03:27 In Excel 2013 and higher, you get task panes.
- 03:30 They have the same commands, the difference is, in a task pane,
- 03:34 we have icons to call out things like label options.
- 03:36 What I'm gonna look at is my data labels here, I'm gonna scroll down,
- 03:40 I'm gonna look at the number.
- 03:41 And I want to change the number format instead of working with the link to source
- 03:46 here, maybe I'll just, well I don't know, let's change the category on this one.
- 03:51 Maybe we'll just make it a number and we'll say, you know what, I don't want any
- 03:56 decimals on this thing at all and that shortens them up and they look nicer.
- 04:00 I could also format the raw data if it were still linked, but
- 04:04 I don't really need to do that.
- 04:05 I can change the Label Position, Inside Base would move the numbers over,
- 04:09 Outside End is where I prefer to have them for right now.
- 04:12 The other thing I might want to do is select one of my bars now, and
- 04:15 you'll notice that the Task pane, or in Excel 2010,
- 04:18 the Dialog, updates to use the current element, Format Data Series.
- 04:22 I'm gonna change this gap width here to 75%.
- 04:26 We'll hit enter.
- 04:28 And you'll notice the bars get bigger,
- 04:30 because the gap width between them is actually much shorter.
- 04:33 So that's kind of cool, too.
- 04:36 One more thing I might want to do to this chart is sort the data so
- 04:39 that the largest number is at the top.
- 04:42 The key for this is that you actually sort the underlying source data.
- 04:46 So, we're going to come back to our data table here.
- 04:48 And we'll grab this and we'll say, data.
- 04:51 And we'll sort.
- 04:53 And it says all right, what would you like to do?
- 04:55 Let's say, well,
- 04:56 you know what, I'm going to sort by May on values from smallest to largest.
- 05:00 Let's see what happens.
- 05:03 And notice that when you sort the data table from smallest to largest,
- 05:06 the chart starts from largest to smallest.
- 05:09 So it's not exactly super intuitive but
- 05:11 that's the way we actually get a nice source order in play.
- 05:14 And we've now got a pretty clean and
- 05:16 effective looking chart that tells our story in a quick way.
Lesson notes are only available for subscribers.