Building Bar Charts

Quick reference

• 00:04 You can see that in this file we have a nice listing of revenue and
• 00:09 cost by product.
• 00:10 And what's the perfect way to actually demonstrate the relationship between these
• 00:13 things 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 how
• 00:24 these products are comparing against each other.
• 00:27 So the first thing that we're gonna do is we're going to select our list of data.
• 00:33 And then we're gonna go to the INSERT tab and we're going to grab a bar chart and
• 00:37 we'll grab the 2D bar chart in the top.
• 00:39 And you'll notice that it gives us this less than ideal looking bar chart but
• 00:44 I'm gonna expand the bottom of this right away and you'll see what's gonna end up
• 00:49 happening here is we should end up with more items showing up on the left.
• 00:53 Because it was compressed,
• 00:54 we didn't see all of our items that we have in our list here.
• 00:56 So that was our first problem we had to deal with.
• 00:59 Now Excel 2013 comes out in much better shape than Excel 2010,
• 01:02 in that it's already got a legend down at the bottom.
• 01:04 It's got a Chart Title, which, well the Chart Title could use some improving.
• 01:08 So why don't we select that and say equals and then link it to cell A2.
• 01:14 And that'll give us a chart title that actually draws back to our worksheet.
• 01:19 It's not bad, it's actually getting to be somewhat readable but
• 01:22 there's still some improvements that I could make.
• 01:24 I don't really need all the extra decimal places in the in my legends down here.
• 01:29 So the way,
• 01:30 the easiest way to deal with that is to select my original source data here.
• 01:34 Go back to the Home tab.
• 01:36 I'm gonna click on the comma and just drop the decimal places down by two and
• 01:39 you'll see that automatically affects the chart because the chart is actually
• 01:43 linked back to the Excel cells, which is great.
• 01:46 Now, the next thing that I'd like to do is I'd actually like to make these
• 01:51 bars a little bit wider.
• 01:52 And this is in contrast to the normal rules where I'm trying to reduce ink.
• 01:56 But in this case, I think it might help if I just had a little bit more to show and
• 01:59 see what's actually happening in the trends.
• 02:02 So I'm going to right-click on one of the bars here and say,
• 02:06 Format Data Series.
• 02:07 And immediately, it will come up with this, and
• 02:10 it shows me that it has a gap width of 182%.
• 02:13 So I'm gonna go and I'm gonna drop this down.
• 02:16 And we're gonna try just an experiment, to see what happens, if we put it at 75% and
• 02:20 hit Tab.
• 02:21 And there we go. Now we've got a little bit more
• 02:23 saturation, so I can see that my bars are a bit thicker and it'll help me understand
• 02:26 or, or at least see what's going on there without having them completely thin.
• 02:30 Sometimes thin bars are great, sometimes they're not so much.
• 02:33 I'd prefer to see a little bit more here.
• 02:36 And at the end of the day, really at this point in time,
• 02:39 there's not a lot more that I have to make in customizations to this chart to,
• 02:43 to really try and make it pop or talk to me.
• 02:46 There's a couple of different things I want to do but
• 02:48 you'd be going through a lot more work with Excel 2010 than you would in 2013.
• 02:52 The pieces that I do wanna change though, is it's really hard, based on this chart
• 02:57 right now to see, not necessarily who's got the highest amount of sales.
• 03:02 That would be the Chicken Nachos down here with the longest bar.
• 03:05 But it's hard to see how things are in comparison to each other.
• 03:08 So, what I'd like to do is, I'd like to resort this data so
• 03:12 that the longest bars are at the top and the shortest bars are at the bottom.
• 03:16 This is actually really easy to do.
• 03:18 What we're gonna do is we're gonna go click back in our original set of data.
• 03:23 I'm gonna go to the Data tab and I'm gonna click Sort.
• 03:27 You'll notice that because I actually have a nice set of titles at the top here,
• 03:31 with no blank rows in between them, it comes up and it says hey,
• 03:35 I know that you have the Type, the Revenue and the Cost fields in your data.
• 03:39 What would you like to sort by?
• 03:41 And I'll say Revenue.
• 03:42 And smallest to largest is fine.
• 03:46 And when we do that, you'll notice that the chart now sorts largest to smallest.
• 03:50 And yeah that's right you didn't hear wrong,
• 03:52 we sort the data table smallest to largest and the chart sorts largest to smallest.
• 03:56 You might think that's backwards but
• 03:58 basically what ends up happening is whatever the order is that's in the table
• 04:02 the reverse is true on on the actual chart itself.
• 04:05 So, if you wanted the small bars at the top,
• 04:07 you'd sort this one with the largest numbers at the top.
• 04:10 Now, the cool thing here is that I can immediately see which products are in,
• 04:16 are selling better in relation to the others.
• 04:19 But I can also see something really important right here, in this data set
• 04:23 the food cost should be approximately 30 to 35%.
• 04:27 I can see that the Halibut & Chips here is having a real problem.
• 04:30 That one is way higher.
• 04:31 So this chart is actually serving my purpose beautifully in that I now have
• 04:35 some management information that's coming out to tell me that something has gone
• 04:40 off the rails and I can now spend more time going to try and
• 04:43 find out what that is so, it's good stuff.

Lesson notes are only available for subscribers.