A factor you"ll want to consider as you choose the best Excel chart type is the relative strengths and weaknesses of each chart type. One could, of course, write an entire book on this subject.
But you may find it useful to consider the strengths and weaknesses outlined in the following paragraphs as you choose a chart.
Let"s Start with Area Charts
Area charts plot data point values using lines. Optionally, they stack the lines so they show cumulative data point values, and color the areas between the lines.
Accordingly, area charts have two noteworthy strengths: They can show both the trend in the first data series and also the total of all the data series, and they can often create implicit total data series.
For example, suppose that you plot two data series in an area chart: one for total expenses and one for profits. In this situation, you actually have a third data series--revenue--because the total of these data series implicitly create a third data series for total revenues.
Area charts also suffer from several noteworthy weaknesses, however.
They make it difficult to see the individual data point values (although this is a two-edged sword because the reduced emphasis on individual data point values also makes it possible to plot data series with large numbers of data point values).
Area charts also make it next to impossible to compare data point values of the second and subsequent data series. (You can usually get a pretty good idea about the first data series data point values, though.)
And another weakness: As with any time-series graph, area charts tend to suggest that time explains the apparent trends. This can be misleading, first, because there may not really be any trends and, second, because even if there are trends, the simple passage of time may likely not be the cause.
Moving on to Bar Charts
Bar charts represent another popular Excel charting option. And that"s not really surprising, I guess.
Bar charts plot data point values in individual bars but arrange the bars so you calibrate them using a horizontal values axis. Accordingly, bar charts work really well when you want to compare data point values in a whole-item to whole-item data comparison and when the data categories are not time periods.
A noteworthy feature of a bar chart is that the horizontal orientation of the chart makes it possible to comfortably use more lengthy data series names.
Bar charts suffer from one weakness in particular: because they show each data point value with its own data marker, as you increase the number of data points you"re plotting, the bars themselves become narrower and less legible.
Cylinder, Cone and Pyramid Charts, Oh My
Cylinder, cone, and pyramid chart types possess the same general strengths and suffer
from the same general weaknesses as do the bar and column charts.
In addition, cylinder, cone, and pyramid chart types, because of their three-dimensionality, also suffer from an additional weakness. The added dimension, while admittedly interesting, often makes it more different to precisely compare data point values.
Hurray for Bubble Charts
Bubble charts let you visually explore the relationships between data series by treating the horizontal axis as a second values axis. To accomplish this, bubble charts plot pairs of data points.
For example, a bubble chart depicting income and contribution data might visually suggest that as people make more money, they only modestly increase their charitable giving.
Bubble charts differ from XY charts, which also show this same information, in that Excel sizes the bubbles using the values of a third data point.
While initially confusing, a bubble chart lets you explore the relationships between two data series. (The only other Excel chart type that lets you do this is the XY [scatter] chart.)
If the bubble chart suffers from a weakness, it is that the chart may suggest correlations or relationships that don"t exist.
Standing Up for Column Charts
Column charts plot data point values in individual bars but arrange the bars so you calibrate them using a vertical values axis.
Accordingly, column charts work really well when you want to view data point values in a whole-item to whole-item data comparison and when the data categories are time periods.
Hungry for Doughnut Charts
Doughnut charts work similar to pie charts, plotting data series in concentric rings and
showing each data point value as a segment, or bite, of the ring.
Compared to pie charts, doughnut charts possess an advantage: they allow you to plot more than one data series. As a practical matter, they suffer from the same weaknesses as pie charts: they don"t let you compare data point values between series (even though they paradoxically show multiple
data series).
They also limit you to small data sets. Almost always, something that appears in a doughnut chart should instead be shown with some other chart type.
Drawing Insights from Line Charts
Line charts generally plot individual data points in a line, using either different data marker symbols or different colored lines to distinguish the data series, and using a horizontal data category axis.
Because line charts de-emphasize individual data point values, they work well for large data sets. With a line chart, you can literally plot thousands of data points. What"s more, of all the Excel chart types, line charts tend to emphasize changes and trends in the data point values, which can be useful.
Predictably, however, line charts suffer from some weaknesses: a de-emphasis of individual data point values which can camouflage inappropriately small data sets and make it impossible to compare individual data points, a tendency to show time-based trends that don"t exist, and a tendency to show relationships between data series that don"t exist.
Throwing Out the Pie Chart
Pie charts, as almost everybody knows, show a single data series and depict individual data points as segments of the circle, or slices of the pie.
While this means that they allow people to compare individual data point values to the total of all the data point values—and one might argue this is a strength—in general, pie charts are without merit because they can show only a single small data series.
Almost always, something that appears in a pie chart should instead be shown in a table.
Getting a Clear Picture with Radar Charts
Radar charts plot each data category"s data point values on separate value axes and connect the data point values of each data series with a line.
The strength of a radar chart is that it may make it possible to precisely compare individual data point values within a data category.
The weakness of a radar chart is that it may make it difficult for you to compare data point values in different categories (although this isn"t always a problem). You"re also practically limited to a small set of data categories because the chart uses a separate value axis for each. (You obviously can"t, for example, plot a set of data with 200 categories.)
Succeeding with Stock Charts
Stock charts plot security prices in a common open-high-low-close format, like you commonly see in a business newspaper or on a financial television show.
While this chart type comes from technical security analysis, the chart type can be useful even to people who don"t chart security prices. One might use such a chart, for example, to plot daily temperatures.
Adding Dimensionality with Surface Charts
Surface charts plot data series in a three-dimensional grid, generally using color not to identify data series but rather to indicate value axis ranges.
The principal strength of a surface chart is that it lets you show with equal emphasis both relationships within a data series and within a data category.
A surface chart, however, also suffers from two weaknesses: One, because the chart does show a three-dimensional surface, it"s easy for the topography of the plot area to hide data—for peaks to hide valleys.
A second weakness exists, too. Although the surface uses color in its value calibrations, there really isn"t any agreed upon order to colors. Is the color red "greater" than blue, for example?
Scattering Data with an XY Charts
XY charts, also known as scatter charts, let you visually explore the relationships between data series by treating the horizontal axis as a second values axis.
To accomplish this, XY charts actually plot pairs of data points. For example, with an XY chart you could plot the relationship between people"s income and their charitable contributions.
The huge strength of an XY chart is that it lets you explore the relationships—perhaps causation or simply correlation—between two data series. In fact, the XY chart is the only Excel chart type that lets you do this.
If the XY chart suffers from a weakness, it is that the chart may suggest correlations or relationships that don"t exist. (You can use Excel"s regression analysis tools to examine whether two data series appear correlated.)