14. Knowing When Someone Is Lying to You with a Chart
Several settings in Excel allow you to either inadvertently or intentionally create charts that are misleading. Many of the popular charting styles do a poor job of representing the underlying data. Don’t get me wrong; the charts often look great, but they do not provide an accurate picture of the data.
This chapter illustrates many of the chart lies that are easy to incorporate into an Excel chart. My hope is not that you will use these to confuse the chart consumer. Instead, I hope you will avoid these types of charts. I also hope you learn to look with caution on charts created by others that use the techniques included in this chapter.
Lying with Perspective
Excel’s 3-D charts attempts to show a two-dimensional object on a two-dimensional computer screen and give the perception that the objects are rendered in 3-D. Even though Excel provides a lot of support for the 3-D charts, they are not covered in great detail in this book because they often misrepresent your data. Instead, I hope you will resist the urge to use 3-D charts.
When I took a photography class, during the lesson on wide-angle lenses, the instructor pointed out that a wide-angle lens makes everything in the foreground look proportionally larger than it really is. If you are shooting a portrait of someone who has a big nose, you don’t want to use a wide-angle lens for that portrait.
Similarly, with 3-D pie charts, any wedges at the front of the pie look much larger than wedges at the back of the pie. For example, in Figure 14.1, both charts show a labor component of 30 percent. If you want to impress the union during contract negotiations, you should use a 3-D chart where labor is in the front because the labor wedge in that chart is 2.8 times the size of the one in the top chart.
Figure 14.1 Wedges in the front of a 3-D pie appear much larger than those in the back.
NOTE
If want to lie with a 3-D pie chart, you can accomplish that by changing the rotation of the pie. Right-click the pie, select Format Series, and then change the rotation angle.
In addition, perspective causes problems with 3-D column charts. For example, often the reader is not sure whether to look at the front or the back of the column.
Look in the top chart in Figure 14.2. Are any of the quarters over 3,000? Count the number of gridlines. The sixth line is 3,000. None of the bars ever touches the 3,000 line. However, in the bottom chart, both the Q3 and Q4 columns are over 3,000.
Lying with Shrinking Charts
The pyramid and cone charts should be banned from Excel. You could do without these 14 different charts, which inflate the data in the bottom series and deflate the data in the top series.
Consider a standard stacked bar chart like the top one in Figure 14.3. In this chart, a charity’s administrative expenses are fairly high at 35 percent. If the charity wants to minimize its administrative expenses in a chart for its donors, it can change the chart to a cone or pyramid chart since this chart is naturally smaller at the top. In this example, the 35 percent wedge for administration in black looks like virtually nothing! This occurs because items at the top of the chart get significantly less pixels than items at the base of the chart.
Figure 14.3 To misrepresent the 35 percent category in black at the top, you can put it in a cone chart.
Lying with Scale
In various chapters of this book, you have seen how to change the minimum and maximum values for the scale along the vertical axis. Zooming in allows you to spot variability in tightly clustered values, such as shown in Figure 2.23 in Chapter 2, “Customizing Charts.” Zooming out allows you to isolate data to a certain zone of the chart. In Figure 6.10, the volume scale was set two to three times larger than normal to keep the volumes in the lower third of the stock chart.
Although both of those concepts are valid reasons to change the scale, there are also consequences from changing the scale. Changing the scale allows you to paint a very different picture of the data. Figures 14.4 through Figure 14.6 show the same demand curve.
In Figure 14.4, the manufacturing plant zoomed the scale in to have a minimum of 4000 and a maximum of 7500. With a title proclaiming that sales are erratic, they can argue that the cost of staffing-up and staffing-down in order to meet the ripsaw demand for product is causing problems.
Figure 14.4 The chart produced by the manufacturing chart focus on the variability.
If the sales team presented their chart first, a different picture would have emerged. Figure 14.5 shows a chart where the scale has been zoomed out to have a minimum of 0 and a maximum of 20,000. With this chart, the demand looks almost constant.
Figure 14.5 By zooming out, the demand looks smooth.
In addition, note that it is much less apparent in Figure 14.5 that the demand is trending down. Figure 14.6 attempts to hide this trend even more. By switching to a 3-D chart and adjusting the 3-D rotation settings, you can create a line that appears to be trending up at the end.
Figure 14.6 The downward trend appears to reverse.
Lying Because Excel Will Not Cooperate
The chart in Figure 14.7 was presented at a board meeting. The treasurer intended to present the good news that a particular department produced a consistent profit for several years. Instead of showing one line with 42 months, he built a cross-tab table to show 12 months and a different line for each year.
Because the current year was not yet complete, data for July through December is blank in the original table. For this reason, it showed up as zeros in the chart data.
NOTE
Note that the subtitle in Figure 14.7 was not in the original chart. I penciled this editorial comment in as I was listening to the presentation.
The problem occurs from a clever formula in the charting data. This formula allowed the 48 rows of vertical monthly data to be presented in a 4-row by 12-column arrangement. The result is that the formula converts blank cells to zeroes for future months.
The solution shown in Figure 14.8 is to convert the zero results to NA(). Zeros are plotted on a chart, whereas #N/A errors are not plotted. By checking for a zero and then converting those results to #N/A errors, the future months are not plotted on the chart.
Zeros can be converted automatically to #N/A. Say that you are building a table with any formula such as =Formula. Edit that formula to be =IF(Formula=0,NA(),Formula).
Figure 14.7 Zeros in the future months make it look like all the money disappears.
Figure 14.8 Convert the formula to show futures months as #N/A.
To see a demo of this chart problem and the solution, search for “MrExcel Charts 14” at YouTube.
Avoid Stacked Surface Charts
The message of the chart at the top of Figure 14.9 is that marketing costs more than tripled in three years. If the vice president of marketing wants to defend the marketing expenses, he might mix the marketing department in with other departments to show the marketing budget as a middle series in a stacked area chart.
In a stacked area chart, you can usually figure out what is happening with the numbers in the series at the base of the chart. However, after that first series, everything becomes difficult to judge.
In the chart at the bottom of Figure 14.9, the dramatic increase in marketing spending is not immediately apparent. Some people might see the downward slope at the top of the marketing region and incorrectly infer that marketing costs are decreasing.
Bottom line, don’t use stacked area charts. Show the data as individual line charts instead.
Figure 14.9 Adding data obscures the marketing excesses.
Asserting a Trend from Two Data Points
In the top chart in Figure 14.10, sales appear to be trending up. Even the automatic Excel trendline indicates this is occurring.
If you are presented with this chart, ask the presenter if they have more data. For example, it would be good to put those two data points in the context of a larger historical trend.
If you see more data, you might realize that the increase in the last year is actually the bottoming out of a six-year skid, as shown in the bottom chart in Figure 14.10.
Figure 14.10 Inferring a trend based on a two-point line is dangerous. You need to add more data points to tell the real story.
Deliberately Using Charts to Lie
Some people are just good at lying. For example, sales are increasing in Figure 14.11. Or are they? Notice that the chart author reversed the categories along the x-axis with the most recent year appearing at the left. Most people are conditioned that charts proceed from earliest to latest. If the presenter is flipping through slides in a PowerPoint presentation, the chances are no one will notice that sales are not trending up.
Figure 14.11 It appears that all is going well.
Excel makes it easy to create the chart in Figure 14.11. The Format Axis dialog for the chart offers a simple check box for Categories in Reverse Order, as shown in Figure 14.12.
Figure 14.12 The reversed years in Figure 14.11 come from this setting.
Chart Something Else When Numbers Are Too Bad
As another example, in Figure 14.13, a city is drastically losing population. Jobs are scarce, education is bad, and people are moving out. The top chart paints a pretty bleak picture. The mayor, who has been around for 24 years, wants to put a happy spin on the message. His staff prepares the bottom chart, which shows how population growth has slowed.
Figure 14.13 When the absolute numbers are bleak, you plot the percentage rate of change.
Beware of Pictographs That Do Not Follow the Rules
The next example is definitely a non-Excel lie. Even Excel is smart enough not to allow picture markers to change in both height and width. Someone had to make the markers invisible and use clip art to pull off the lie shown in Figure 14.14.
The image on the left is 100 pixels tall and 100 pixels wide. This seems like a reasonable way to represent $100 million in exports. When the chart designer scaled the picture up to 300 pixels for the March data, he allowed both the height and width to change. The size of the final image contains nine times the area of the first image, even though the exports increased by a factor of three.
Figure 14.14 The size of the images increased in both height and width. This causes a 200 percent increase to look like an 800 percent increase.
The rule for pictographs is that you should increase the height of the picture, but never the width. Even though Excel does this properly, charts in newspapers and magazines are frequently created in Photoshop. The designers try to create a pretty chart that does not distort the image by increasing the height and width of the marker. In the process, they create a misleading chart.
These are just a few of the many ways that people might try to lie to you with charts. Now that you have read this book, you should be able to spot a deception and call it out. You should also be able to use charts to represent data accurately.
Next Steps
Appendix A, “Charting References,” lists several additional resources where you can find more information on charting.