Typically, data that's presented in a chart should be presented in its proper context. Figure 12-1 shows an example. The top chart displays data for three months and leaves the impression that a downward trend in the numbers exists. But, when viewed in the context of the entire year, the last three data points do not seem at all out of the ordinary.

Figure 12-1. Plotting only the last three data points does not tell the entire story.

The scale settings for the value axis of a chart can have a major effect on a chart's overall impact. Figure 12-2 shows two charts. Both charts display the same data, but they present dramatically different messages.

In the top chart, the value axis has a range of 14, 000 units. In the bottom chart, the axis has a range of only 2, 500 units, which causes the minor deviations in the data to be magnified.

Figure 12-3 shows another example of a misleading value axis scale. The casual viewer might conclude that the median home price had more than doubled in a six-month period. In fact, the July value is only 0.67% higher than the January value.

Figure 12-2. A chart's value axis can exaggerate differences in data points.

Figure 12-3. Fast-paced housing market, or misleading chart?

A chart's aspect ratio can also affect the chart's overall message. The term aspect ratio refers to the chart's width-to-height relationship. A "normal" aspect ratio is 4:3, which means that the chart is four units wide and three units high (this also corresponds to common computer display resolutions and non-widescreen TVs).

Figure 12-4 shows three line charts that display the same data. Even though the value axis scale is identical for the three charts, they present very different impressions of the data.

Figure 12-4. A chart's aspect ratio can affect the viewer's perception of the data.

Time-based data is often summarized by calculating a percent change from one period to another. These percentage calculations do not take into account the magnitude of the values and can therefore mislead the viewer.

The chart in Figure 12-5 displays the percentage change for three products. Product C, of course, stands out in the chart — even though its values are almost insignificant when compared to the other products. The data is completely accurate, yet the chart is very misleading. Creating a chart from the data in columns B and C would be a much better choice.

Figure 12-6 shows another example of a misleading chart. Both charts display a histogram of the same data: the ages of a group of 100 people. The only difference in the charts is the size of the bins. Although both charts are technically accurate, the top chart uses bins of unequal size and conveys the impression that younger people dominate the group. The bottom chart uses equal-size bins and presents a more accurate picture of the age distribution.

Figure 12-5. Plotting changes in terms of percentage can lead to incorrect conclusions.

Figure 12-6. Using unequal bin sizes in a histogram can present a false impression of the data.

A common problem with charts stems from confusion between a category axis and a value axis. This topic is covered in Chapter 4, but it's worth revisiting.

Common chart types, such as a column chart, line chart, area chart, and bar chart, all use a standard category axis. Usually, a category axis does not convey any numerical information. A scatter chart, on the other hand, uses two value axes, both of which convey numerical information. In addition, a line chart can use a date-based scale which, in effect, gives the chart two value axes.

Figure 12-7 shows a worksheet with a company's net income data for seven periods of time, along with a line chart that plots the numbers. Note that the year increments are not equal. The chart is misleading because the last three data points use a different time interval, and the chart gives the false impression that net income growth has slowed considerably. The use of a line chart usually implies a continuous stream of data — which is not the case with this chart.

Figure 12-7. Using a line chart for this data presents an incorrect picture of the growth.

Figure 12-8 presents a more accurate picture of the growth. The chart in this figure is a line chart, but I specified that the horizontal axis is a date axis. Therefore, Excel treats the horizontal scale as numeric. To change the horizontal axis type of a line chart, select the axis and press Ctrl+1 to display the Format Axis dialog box. Click the Axis Options tab, and make the change in the Axis Type section.

Figure 12-8. Using a date axis for the line chart treats the years as values.

Because the years aren't entered as dates, Excel treats these year values as days. In actual practice, it makes no difference. The key point is that the values are treated as numbers rather than arbitrary text.

Pie charts are one of the most commonly used chart types. A single pie chart is often appropriate, as long as the number of categories is kept to a reasonable number. Often, however, people present multiple pie charts to make a comparison.

Figure 12-9 shows an example. This group of charts suffers from several problems. The charts essentially look identical. With no numerical labels, the viewer cannot draw any conclusions. But more important, the charts provide no clue as to the total "value" of each pie.

Figure 12-9. It's virtually impossible to make comparisons by using multiple pie charts.

Three alternatives are shown in Figure 12-10. Any of these charts is superior to the four pie charts. The stacked column chart (lower left) provides all the information in the pie charts; it also allows the viewer to make overall comparisons by year. The two clustered column charts also present all of the data. One is useful for comparing expenses across years; the other is useful for comparing expenses within each year.

A common problem is that pie charts often have too many slices. Figure 12-11 shows a pie chart that was published on the Web by a federal government agency.

Figure 12-10. These three-column chart variations are preferable to four pie charts.

Figure 12-11. This pie chart has too many slices.

This chart certainly conveys the information, but most would agree that 17 slices in a pie chart is excessive. Figure 12-12 shows a possible makeover that retains all the original information, yet doesn't appear as cluttered. The pie chart displays a slice only if the country has a production of 5, 000 or more. The other countries are grouped together. I inserted a shape (named Right Brace 3) to the right of the table of data so that the reader can easily see the countries that make up the Others group. That table of data, by the way, is a range that was copied and then pasted as a picture (using Home

Figure 12-13 show another alternative to the cluttered pie chart: A bar chart. This chart shows all the numerical information from the original chart and is very uncluttered. In fact, the large area of white space is a perfect spot for the chart title and note. The vertical axis labels (country name and production amount) consist of a range that was copied and pasted as a picture. Excel cannot handle multicolumn vertical axis labels very well, but creating pseudo axis labels with a picture of range worked well in this case.

Figure 12-12. Reducing the number of slices in a pie chart makes it more legible.

Figure 12-13. Using a bar chart instead of a pie chart.

Whenever you create a pie chart, ask yourself a question: Is a pie chart really necessary? Figure 12-14 shows a handsome pie chart, complete with a "plastic" 3-D look. It looks great (especially in color), and certainly attracts attention. Rather than focus on the data, perhaps the first question viewers will have is, How did you make that chart?

The figure also shows the source data for the pie chart — including conditional formatting data bars. The small range of data actually conveys more information than the fancy pie chart.

Some chart types don't handle negative numbers as you might expect. For example, a pie chart (or a doughnut chart) simply converts all negative values to positive values. This, of course, is rarely what you want. Figure 12-15 shows a pie chart that presents an incorrect view of the data. The only way the reader would know that Net Domestic Migration is negative is by making note of the negative sign in the data label.

Figure 12-14. A pie chart with lots of eye appeal.

Figure 12-15. The pie chart converts negative values to positive values.

Figure 12-16 shows the same data presented in a column chart and in a stacked column chart. Although the stacked column chart does put the negative value below the category axis scale, the chart is still somewhat misleading because the stacked columns imply that the total is just under 200, 000. The clustered column chart clearly presents the most accurate picture of this data. However, it requires a text box to indicate the net growth amount.

Figure 12-16. Negative values are handled better by a standard column chart (right).

When you create a scatter chart, you have an option to connect the points with a straight line. Another option is to create a "smoothed line." When the Smoothed Line option is in effect, the data markers are connected with a line that contains curves.

Figure 12-17 shows two scatter charts that plot the same data. One uses straight lines and the other uses the Smoothed Lines option. Note that the chart on the bottom is deceiving. The chart shows the data extending below zero, even though none of the Y data values is less than zero. In addition, the line extends above the 1.5 value, even though the largest value is 1.5.

Excel's 3-D charts have a special appeal because they often seem more artistic and add a bit of pizzazz to an otherwise boring presentation. But, when all is said and done, 3-D charts offer few real advantages — and lead to quite a few potential problems. I won't say that you should never use 3-D charts, but you should understand their weaknesses.

Three-dimensional charts are often acceptable if your goal is to show general relationships and complete accuracy is not required. But for technical charts in which the viewer may want to make detailed comparisons, a 2-D chart is always preferable. Figure 12-18 shows a 3-D line chart with three data series. What's the value of the Qtr-3 data point for the WA series? Which series has the highest value in Qtr-2? These questions cannot be answered by looking at the chart.

Figure 12-17. The Smoothed Line option can distort the data.

Figure 12-18. A 3-D chart can depict general relationships — sometimes.

With 3-D charts, you always have the possibility of hidden data (see Figure 12-19). Can you determine the Qtr-1 value for CA? You can rotate the chart or change the order of the series, but changing the chart type is probably the best solution — unless, of course, your goal is to keep the data hidden!

Figure 12-19. 3-D charts have a tendency to obscure data.

And for those who really want to obscure their data, Excel enables you to add perspective distortion, as shown in Figure 12-20. This setting, in effect, displays the chart as if viewed through a very wide-angle lens.

Figure 12-20. Adding perspective to a 3-D chart makes it even more difficult to understand.

We've all seen charts like the one in Figure 12-21. This chart is so bad that it doesn't even deserve further discussion.

Figure 12-21. No comment.

Edward R. Tufte, author of several books that deal with the visual presentation of data, refers to the principle of data ink maximization. Simply put, this principle states that the most effective charts use their "ink" to display data — not chart accoutrements such as grid lines and labels. Consequently, nonessential chart elements can often be deleted with little or no adverse affect on the readability of the chart.

Figure 12-22 shows a typical column chart, and a "minimalist" version of the chart below it. There's certainly nothing wrong with the original version of this chart. The chart on the right, however, removes all nonessential elements — and the basic message remains. The chart on the right underwent the following modifications:

Figure 12-22. A column chart before and after removing all nonessential elements.

Another chart makeover is shown in Figure 12-23. This chart started as an area chart, and the modified version is a line chart. Adding color to the area below each line really adds nothing to the chart. Other modifications are as follows:

Figure 12-23. An area chart before and after removing all nonessential elements.

The modified chart conveys all the information of the original chart in an (arguably) more efficient manner.

Quite a few chart elements can contain text: titles, axis labels, legends, data labels, and so on. Perhaps the most common problem is too much text on a chart. A chart should stand on its own, and lengthy explanatory text should not be necessary (see Figure 12-24).

A very common problem with Excel charts is displaying text correctly on the category axis. The problem is that lengthy text often doesn't fit, and Excel automatically rotates the text.

Figure 12-25 shows an example of a chart in which the category axis text has been rotated. The main problem is that the text takes up an inordinate amount of space in the chart, at the expense of the plot area.

Figure 12-24. There's a chart in here somewhere.

Figure 12-25. A common problem is text in category axis labels.

Excel adjusts the orientation automatically, based on its algorithms. For more control over the axis text, access the Format Axis dialog box and click the Alignment tab. You can then force the text alignment to be a specific angle (0 degrees results in normal horizontally oriented text).

After making this change, you might find that Excel skips some of the axis labels. You can force all axis labels to be displayed by using the Axis Option tab of the Format Axis dialog box. Specify a value of 1 for the Interval Between Labels setting. Then, you can adjust the font size, plot area width, and chart width to ensure that all category axis labels are displayed properly and not at an angle (see Figure 12-26). Note that I had to make the chart much wider. Excel does not wrap the text in the category labels unless the chart is wide enough to accommodate the labels. Otherwise, the text is displayed at an angle.

Figure 12-26. Adjusting a few settings ensures that the text is displayed horizontally.

Font mistakes generally fall into one or more of the following categories:

The chart in Figure 12-28 demonstrates all these problems.

Figure 12-27. A bar chart is a better choice if you have lengthy category labels.

Figure 12-28. This chart has several font-related problems.