"Mr Excel" Bill Jelen shows how to choose the most effective type of chart and which settings to use to show various trends over time. He also discusses the strengths and limitations of Excel's available chart options.

Choosing a Chart Type

You have two excellent choices when creating charts that show the progress of some value over time. Because Western cultures are used to seeing time progress from left to right, you are likely to choose a chart where the axis moves from left to right—whether it is a column chart, line chart, or area chart.

NOTE

The new Sparklines feature is another way to show trends with tiny charts. See Chapter 9, "Using Sparklines, Data Visualizations, and Other Nonchart Methods."

Column Charts for Up to 12 Time Periods

If you have only a few data points, you can use a column chart because they work well for 4 quarters or 12 months. Within the column chart category, you can choose between 2-D and 3-D styles. To highlight one component of a sales trend, you can use a stacked column chart.

NOTE

This book recommends not using pyramid charts or cone charts because they distort your message. For an example, see the "Lying with Shrinking Charts" section in Chapter 14, "Knowing When Someone Is Lying to You With a Chart."

Line Charts for Time Series Beyond 12 Periods

When you get beyond 12 data points, you should switch to a line chart, which can easily show trends for hundreds of periods. Line charts can be designed to show only the data points as markers or data points can be connected with a straight or smoothed line.

Figure 3.1 shows a chart with only nine data points, which that a column chart is meaningful. Figure 3.2 shows a chart of 100+ data points. With this detail, you should switch to a line chart in order to show the trend.

Figure 3.2 When you go beyond 12 data points, it is best to switch to a line chart without individual data points. The middle chart in this figure shows the same dataset as a line chart.

Area Charts to Highlight One Portion of the Line

An area chart is a line chart where the area under the line is filled with a shading or color. This can be appropriate if you want to highlight a particular portion of the time series. If you have fewer data points, adding drop lines can help the reader determine the actual value for each time period.

High-Low-Close Charts for Stock Market Data

If you are plotting stock market data, use stock charts to show the trend of stock data over time. You can also use high-low-close charts to show the trend of data that might occur in a range such as when you need to track a range of quality rankings for each day.

Bar Charts for Series with Long Category Labels

Even though bar charts can be used to show time trends, they can be confusing because readers expect time to be represented from left to right. In rare cases, you might use a bar chart to show a time trend. For example, if you have 40 or 50 points that have long category labels that you need to print legibly to show detail for each point, then consider using a bar chart. Another example is illustrated in Figure 3.3, which includes sales for 45 daily dates. This bar chart would not work as a PowerPoint slide. However, if it is printed as a full page on letter-size paper, the reader could analyze sales by weekday. In the chart in Figure 3.3, weekend days are plotted in a different color than weekdays to help delineate the weekly periods.

Figure 3.3 Although time series typically should run across the horizontal axis, this chart allows 45 points to be compared easily.

Pie Charts Make Horrible Time Comparisons

A pie chart is ideal for showing how components that add up to 100% are broken out. It is difficult to compare a series of pie charts to detect changes from one pie to the next. As you can see in the charts in Figure 3.4, it is difficult for the reader's eye to compare the pie wedges from year to year. Did market share increase in 2008? Rather than using a series of pie charts to show changes over time, use a 100 percent stacked column chart instead.

100 Percent Stacked Bar Chart Instead of Pie Charts

In Figure 3.5, the same data from Figure 3.4 is plotted as a 100 percent stacked bar chart. Series lines guide the reader's eye from the market share from each year to the next year. The stacked bar chart is a much easier chart to read than the series of pie charts.

Figure 3.5 The same data presented in is easier to read in a 100 percent stacked bar chart.