Join Dennis Taylor for an in-depth discussion in this video Using column and bar charts, part of Excel 2013: Charts in Depth.
Two of the most common chart types are column and bar. And they're very similar in terms of how you display them and the kinds of things you want to do when you're changing them. On this particualr worksheet called column bar we're seeing for the moment only column charts. The most common chart type probably for many people is clustered column. Its just below the data here it has a gray border. And you can see how it clearly shows each of our monthly entries for the time period in question. A variation on this, also widely used, is a stacked column chart to the right. The advantage of this is we see the total at a glance. We can see, we might not have seen it otherwise.
Without seeing the real data, February total is higher than March, however slightly. The disadvantage is, might be if, for example, we're looking at Asia. As we look at the gray portions of these, following these from month to month isn't exactly easy unless we're pointing to them. Or going back to the original source data. Now, another advantage of the stacked column chart is that it's less cluttered, perhaps easier to read as well. A variation on stacked column is a hundred-percent stacked column. This chart type is an attempt to overcome some of the shortcomings of a pie chart.
What we're seeing in each monthly series here is not the amount of the sales but the proportion. They all add up to 100%. January actually has a total of $290 million. Whereas June is $490 million, and yet the columns are the same size. And so, when you are comparing parts of the columns, it's very risky to go just by appearance. For example, in January Asia is 110. In February it's 120. But that column piece is smaller and that's because in February that 120 represents a smaller percentage of the February total than the 110 does for January.
And so, we again see how that chart can be used. And recognize that both of these charts do have a considerable amount of space between the columns. You can easily adjust that the feature's called gap width. I used it on the upper chart. Simply right click one of the columns then choose Format Data Series. This activates that dialog box, and you see the term, Gap Width. Now, the numbers sometimes are surprisingly not sure where they came from. Where is this 154? Maybe it should be 100.
Try that, and if we click on the panel right above it, the chart reacts. We see how the columns are closer together. Try 50%. You can also use the arrows to the right to make it happen more slowly. You see what's happening there. So, it gives it a different look, a different feel. Possibly, maybe that suggests volume a little better than what we saw, maybe not. Right click, Format Data Series. Series Overlap wouldn't exactly recommend but you might give that a shot, possibly on a clustered column chart, like the one below the data. And so, if we click one of the columns there, and then on the dialog box go to Series Options, we see these choices.
Series Overlap. What if we overlap these by ten. Click in the other panel. And it doesn't happen immediately maybe, but there's some overlap on the chart, just slight. Choose a bigger value there, about 50. Possibly you could be on, on the path to hiding this. So, if we look at the clustered column chart now you see how the columns overlap. So, I think that feature's primarily designed just to give it a little bit of flair, a slightly different look. It probably has the unintended affect of giving the column that's in the front a little bit more attention even though the actual amount might be smaller.
The chart to the right, a 3D column chart, certainly has some visual appeal. But also some deficiencies, some problems in display. If you're looking at a column, can you tell what the value really is? This second gray column, if you don't point to that or go back to the source data. Is above 100, is it below 100? Can you follow it backwards? Well, if we put our mouse on that point, it's 110. That surely isn't obvious from looking at the scaling on the back. Another problem here is that the Asia entries, the grey columns, tend to obscure or almost hide it in the background. The year of columns.
So, that's something we want to adjust as well. And here too, is the gap with issue that we might or might not want to deal with. To adjust the order of these, we need to right click one of the columns. It doesn't have to be the one we're about to move necessarily, but right click any of the columns and choose Not Format Data Series, but Select Data. And in this dialogue box, we'll click one of the entries we want to move, either Europe or Asia. Let's click Asia right here. And you'll see these up and down arrows here. You don't necessarily know, which one to use, but you might try one, then try the other.
So, I click this arrow, and you don't always get an immediate response, but I did move Asia. And now we see it's at the front of the chart. We want it to go the other direction. So, click it twice, you want it behind Europe, and eventually we will see that. And now it's easier to see that Europe columns. Close that dialogue box. And once again, if we were to right click any of these here and go to Format > Data series, we can also try, and I'm going to move this dialogue box leftward by dragging its title. Possibly changing the gap depth, and the gap width.
Now, sometimes this is just a fun little game to see how it's going to look. I'll change this to 50 instead of 150. And also, the gap width here. Put in 50 there and click back on the other panel. And we get that effect. Notice how it's readjusted the series too, that was unintended but that happen So, we could right-click again on Asia, choose Select Data, and once again, click Asia and move it. But that's a different lokk for the chart. I think you can see that it has maybe better visual appeal based on your perspective.
Now, why would we use a bar chart? Aren't these all good enough? Could we click on any of these and change? We surely can. On the Design tab, second button from the right, Change Chart Type. How will this look as a Bar Chart, Stacked Bar Chart? There's our preview let's double-click it. Is that better, again it could be it's just a question of what you like much of the time. And is a bar chart ever better than a column chart? Again probably a subjective call there, but there is some data off the right here where I think, maybe a bar chart would work better.
If we select this data here, and we don't even have to select it since it is surrounded by empty cells, we can simply click here. Press Alt F1 to get a quick column chart. I think you see a problem immediately here. The departments are difficult to read. We might have to make the chart wider to see them. It might slant them, but you see what's happening there. How would this look as a bar chart? With the chart selected, on the Design tab, change chart type. Let's make this be a bar chart. Go with a clustered bar.
Click OK, and we see the choices. Now, it looks as if we're not seeing every department listed there, so we might have to make this taller. But the ease with, which we can read those is better, perhaps, than we saw on a column chart where they were either slanted or vertical. The gap width here is just as easy to change as it was for a column chart. Or right click one of the bars here, choose format data series and then in the popup dialog box, change the gap width, whatever number it is, down to something like 50, maybe. See how that looks.
Or possibly 10 or maybe even 0. But we have control over the data in the same way that we do with the column charts, so sometimes it's just a question of what looks better to you. I think in this case you could make a strong case for saying bar chart works better than column charts. But the way we adjust these works in the same general way. Usually the easiest way is either to right click the item in question as we've been doing for example in this bar. Or to double click nearly always this leads us in to the appropriate dialogue box where we can make the changes.
So, as we've seen many different ways to control column and bar charts probably the most commonly used charts by most Excel users.
- Selecting the right chart type
- Choosing data to display as a chart
- Creating charts fast with the Quick Analysis tool
- Choosing a chart layout
- Changing the location of the source data
- Dealing with empty and hidden cells
- Moving and resizing charts
- Adding data labels
- Analyzing existing and future data with trendlines
- Adding shapes and arrows
- Working with column, bar, line, pie, and area charts
- Working with specialized chart types: Gantt, Pareto, and Frequency charts
- Creating dynamic charts
- Printing and sharing your chart