Join Dennis Taylor for an in-depth discussion in this video Selecting the right chart type, part of Excel for Mac 2011: Charts in Depth.
- Excel has many different chart types, and when we say, "chart type," we mean, for example, a Column Chart like the one below the data here. To the right of the data we see a Line Chart. To the right of that we see a Pie Chart. Below the Pie Chart we see a Stacked Bar Chart, to the left of that a 3D Column Chart. The question invariably comes up, "Well, which chart type should I use? Which one is best?" And the answer is, well, it all depends. And some of it is just a judgment on your part as to which looks best.
If not rules, there are at least certain traditions about different kinds of charts. The most common chart type in Excel is what we call a Column Chart, the one below the data here. As I move one sheet to the right, called Column Chart, let's look at some of the variations here. To the right of the data is what we call a Clustered Column Chart, perhaps the most common chart type in Excel, and the one that you will get automatically if you've highlighted data and you press F11. That gives you a chart on a new sheet, but it will be a Clustered Column Chart. Column Charts are usually considered best if you're showing volume, and because they are so widely known and used, you don't have to explain usually what they mean, and that's always something to be thinking about.
If you're spending a lot of time explaining how to read a chart, it's probably not the best chart type. Below the data here we see what's called a Stacked Column Chart. Its immediate advantages, when you look at it, you can see the total for the month. Now even though February and March are pretty similar there, February is slightly larger. You wouldn't see that so obviously in the chart to the right. Both of these charts, the blue-bordered chart to the right of the data, the tan chart below the data, are showing the exact same data, but as we look back and forth at them, we see the advantage of a Stacked Column, at least in one sense.
A disadvantage of the Stacked Column is that if you're trying to compare the various red segments here that refer to Europe, this isn't so easily done. But on the standard Clustered Column Chart to the right, as we look at the red columns, also showing Europe, it's much easier to compare the month-to-month. At different times, you've got different reasons, different rationales for choosing one kind of Column Chart over another kind. One that you probably will not use as often, below this, it's referred to as a 100% Stacked Column Chart. That has its specialized uses, sort of like a Pie Chart in a sense.
To the right the pink chart, the pink-bordered chart, is simply a variation on a 3D Column Chart, gives it a little more visual flair, perhaps. That's one slight advantage in some cases. Below that we see a 3D Column Chart. They certainly get people's attention, but they are difficult to read. Can you tell me what that green is without sliding over it? Not so easily done. That's 120. Could you guess that by looking at the lines off to the left? I don't think so. But overall it allows you to compare columns with other columns.
At different times you might want to consider a Line Chart, that's the next choice over, the next sheet over. Generally if you're trying to show a trend or lack of a trend, Line Charts are considered best. Nearly always a Line Chart has, across the bottom of the screen, we call this the x-axis, nearly always that is a time frame. It could be months, days, years, quarters, half-years, whatever, even down to the minute level depending upon what the data might be. For whatever reason we tend to read these left to right.
Could this be a Column Chart? It certainly could. And it might be better from your perspective, but Line Charts are frequently used to show trends. Pie Charts, also fairly common, are best at showing how pieces of data add up to a whole. And one of the limitations of Pie Charts are that typically you can't show a lot of data. In both of these examples here, we're only showing the data for domestic. In other words, we really can't have multilayered pies. 3D Pies are somewhat popular, but extremely misleading.
Look at the purple segment up above, look at the tan segment above that. The tan segment represents the value 180, purple one 130. Now, to me they look pretty close, and yet the purple one, on the other hand, is 130, and the one in the background is 180. The two-dimensional version of that below, I think, shows the various portions a little bit better. Next sheet over is a Bar Chart, simply nothing other than a Column Chart rotated 90 degrees. Some people prefer these when they're setting up goals, if you can imagine, maybe like the idea of the finish of a horse race, where these numbers, the higher they are, they're moving toward the finish line.
Something like that, perhaps. But many times it's just a question of what looks better to you. And again if you find yourself spending too much time explaining how to read these, you might consider another chart type. As with Column Charts here, we see the same kinds of variations, sometimes Stacked, sometimes Clustered. If you only use charting occasionally, stick to the so-called Big Four; Bar Chart, Pie Chart, Line Chart, Column Chart. They're widely used, and often you don't have to spend a great deal of time explaining how to read them.
An Area Chart, on the next sheet over, in a certain sense is a variation on a Line Chart. It might suggest volume, perhaps, better than a Line Chart, but essentially it's the same idea with the color filled in. And I tend to see a lot of these, you might not. Next sheet over is called a Scatter Chart, sometimes referred to as a XY Chart. These are certainly less common, but keep in mind that in your particular work or your particular industry, some of these chart types might be more common than I'm suggesting. There certainly is a lot of variation here.
In the example here we're simply showing a relationship between the time it took to finish a race and the age of the person. Is there any correlation? Across the bottom here we're seeing the age of the people who ran the race, and up and down the left-hand side we're seeing how much time it took to finish the race. Other kinds of chart maybe wouldn't show that so well, like the chart below it. On this next sheet called Combo, I've got a chart that actually mixes a Column and Line. This is just to bring out the idea that there will be times when using different chart types within the same chart are appropriate.
If you look at this only briefly you can begin to figure out what's going on. The two measures in column B and column C are widely divergent in terms of quantity, and yet we want to show them both on the same chart. And so as you look at the chart, read the columns off the left axis. We're seeing numbers seven, eight, 9,000. Look at the line and the points there, we're reading off the right side, we're seeing numbers between 15 and 40 representing miles per gallon. You actually can combine different kinds of charts together.
At no point are we saying, "This chart is definitely better than that one," although there certainly will be some times, for example, if you change some of these to Line Charts, they might not be so good. There always is an element of subjectivity, and we'll show you in later movies how easy it is to change a chart type. The four most common ones again; Column, Line, Pie, and Bar. Stick with those unless you have special needs.
- Creating charts with one click
- Selecting the right chart types
- Changing chart type or source data
- Choosing a chart layout and style
- Saving charts as templates
- Editing titles, legends, and labels
- Adding a data table
- Analyzing data with trendlines
- Formatting the chart
- Customizing different chart types
- Creating Gantt and frequency charts
- Creating in-cell charts
- Moving charts