Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, Dennis Taylor shows how to analyze and communicate the value of data with charts in Excel. The course starts with the foundations: what the parts of a chart are, what the different types of charts are, and which charts work best for your data. The course then shows how to create a presentation-ready chart in minutes and offers dozens of in-depth tutorials on formatting and fine-tuning charts so they represent data clearly and accurately.
Column and bar charts comprise the bulk of chart types for many Excel users. Understanding the differences between the terms clustered, stacked, and 100% stacked helps you understand how best to use these chart types. The default chart type in Excel, unless you have changed it, is what's called a clustered column chart. I am not sure if that's a term that everybody uses there freely, but this chart to the right of the data here on this worksheet called Column Chart is called a clustered column chart. It is the default.
And many, many times it's a good choice. We see clearly what the height of each column, what it refers to. We have got a legend that refers back to the data. We can look there if it's on the same sheet as it is here. Easy to read. But sometimes what we're aiming for is to show the data perhaps not only in a simpler, cleaner look, but also to get information that shows us the total for each month. Now can you tell for sure, would you bet the ranch, is April bigger than February? Yeah, I think it is. It looks like it, but how much, and how clear is it? The chart to the left here, below the data itself, is what's called a stacked column chart and we can see at a glance that April is bigger than February.
It's like April and May are about the same. We could go back to the data to check that out. But the advantage, certainly one of the advantages of a stacked chart, as this example here is, we do see the grand total properly. The downside, for sure, is reading the specific pieces. Now the blue, the example here, represents domestic, and we can compare those columns, their height, pretty readily. We can see the values clearly. But try comparing the greens from month to month, particularly the months that are not next to each other.
Is the January green? That's for Asia entry. Is it bigger than June? Can we tell that clearly? Once we have clicked the chart, we can hover the mouse over it and see that that's 110 for January, and then go slide the green over into June. There is 130, but was that so obvious until we saw the numbers? And over here back on the previous chart, when it's clustered, we do see that breakout. But at different times you're trying to show different kinds of information in the chart. And a third type of chart--and by the way, you will see these not only for column, but you'll also see them for line charts and bar charts, and also area charts, the term clustered again for the basic type, stacked over here to the left and a third type called 100% stacked.
I myself don't use this very often. I don't see them being used a whole lot, but they certainly do have their place, and in many situations they take the place of multiple pie charts. So as you probably could glean from the data here--and let me make this a little clear by actually dragging this chart upward, so that we can see it next to the data. So for the moment, it's overlapping another chart, and now we have got our data off to the left here. So for January, just a glance here, you can see, based on the colors, that Asia has the biggest percent of choice, and we don't necessarily know that number till we point to it and what happens on the chart itself when we point to the green.
It gives us the value but not the percent. In this case, we actually have to look at the chart itself. Now, did I misspeak there or what? Can we see that percent? It's not in the data, so we don't even know what the percentage is. But at least we get some idea of the breakout. So all the January data, which totals 290, this is the breakout percentage-wise. The February total is larger. It's 390 and so the breakout is by percent. So it's not correct to compare, for example, these two green bars here and say automatically that the January entry is bigger.
This is 110 for January, right there, and then to the right of it, this is February. That's 120. So I think you can see the downside of this, and yet at the same time, for any given month by itself, a stacked 100% chart makes sense. So when you are changing chart types, in other words when you selected a chart and you go to the design tab in that left button for changing chart types, you will see, for example, under Column, first choice is called Clustered, second one is called Stacked, third one is called the 100%. Under Line, we see a line chart then we see what, Stacked line, and then we see 100% Stacked line.
And with bar chart, as you might imagine, it's very similar to column. I'm going to go to clustered and stacked, and 100%. And similarly with area: Area chart, Stacked Area, 100% Stacked Area. Another type of chart that you'll see from time to time is 3D, and you recognize a slight difference here. There is something called a 3D clustered column. I'll double-click to make this change right now, and you'll see that look.
And there is another choice under Design > Chart Type called simply 3D Column. Watch the difference here. And I think this is the one that has for some people some initial visual appeal because it's kind of dramatic, and I'm going to press Ctrl+Z to undo, so we can see the previous one and then Ctrl+Y to go back and forth here a little bit, but you can again begin to see why one of these is better than the other for most people, but not necessarily everybody. Back and forth here a little bit. I'd stay away from, not this 3D chart, but the other 3D chart. I think from time to time that's the one that's not as clear as it could be.
But do recognize the standard terminology here that we see in different chart types. If we do switch this to a bar chart, you would think that we might have two corresponding similarities there when it comes to 3D, but there is actually only one that makes sense here. On bar chart you will see, for example, 3D Clustered, but you won't see so-called plain 3D bar chart. So we see this one. That's the only 3D variation for bar charts. So without letting of your knowledge of all these different chart types become too cumbersome here, just do recognize the terms Clustered, Stacked, 100% Stacked, you'll see them from time to time when using column and bar charts--less so with the line charts and area charts, but you'll see them there as well.
So gaining a clear understanding of these terms really helps to master the many uses, particularly of column and bar charts, in Excel.
There are currently no FAQs about Excel 2007: Charts in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.