Building Bar Charts

Quick reference

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