Bar Charts

Quick reference

• 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.