Creating Excel 2007 Charts That Show Trends
- Choosing a Chart Type
- Understanding a Date-Based Axis Versus a Category-Based Axis
- Using a Chart to Communicate Effectively
- Adding an Automatic Trendline to a Chart
- Showing a Trend of Monthly Sales and Year-to-Date Sales
- Understanding the Shortcomings of Stacked Column Charts
- Shortcomings of Showing Many Trends on a Single Chart
- Using a Scatter Plot to Show a Trend
This chapter is from the book
This chapter is from the book
This chapter is from the book
IN THIS CHAPTER
- Choosing a Chart Type 81
- Understanding a Date-Based Axis Versus a Category-Based Axis 84
- Using a Chart to Communicate Effectively 104
- Adding an Automatic Trendline to a Chart 113
- Showing a Trend of Monthly Sales and Year-to-Date Sales 115
- Understanding the Shortcomings of Stacked Column Charts 116
- Shortcomings of Showing Many Trends on a Single Chart 118
- Using a Scatter Plot to Show a Trend 119
- Next Steps 120
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's a column chart, line chart, or area chart.
If you have only a few data points, you can use a column chart. Column charts work easily for 4 quarters or 12 months. Within the column chart category, you can choose between 2-D and 3-D styles. If you want to highlight one component of a sales trend, you can use a stacked column chart.
When you get beyond 12 data points, you should strongly consider switching to a line chart. A line chart can easily show trends for hundreds of periods. Line charts can be designed to show only the data points as markers or to connect the data points with a straight or smoothed line.
Figure 3.1 shows a chart of 9 data points. This is few enough data points 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.1 With 12 or fewer data points, column charts are viable and informative.
)
Figure 3.2 When you go beyond 12 data points, it is best to switch to a line chart without individual data points. The bottom chart in this figure shows the same data set as a line chart.
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.
If you are plotting stock market data, you can 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 (for example, if you have to track a range of quality rankings for each day).
You might think that a bar chart could be used to show time trends. However, that would confuse your readers because they expect time to be represented from left to right. In very rare cases, you might use a bar chart to show a time trend—for example, if you had 40 or 50 points, all with very long category labels, and you needed a printed chart to legibly show detail for each point. As an example, Figure 3.3 shows sales for 45 daily dates. The chart would not work as a PowerPoint slide, but if it were printed as a full page on a letter-size piece of paper, the reader could analyze sales by weekday. Note that in the chart in Figure 3.3, weekend days are plotted in a different color than weekdays.
)
Figure 3.3 Although time series typically should run across the horizontal axis, this chart allows 45 points to be compared easily.
Pie charts are great for comparisons. If you are thinking about using a series of pie charts to show changes over time, however, you should instead use a 100% stacked column chart. Consider 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 2005?
)
Figure 3.4 It is difficult to compare one pie to the next.
In Figure 3.5, the same data is plotted as a 100% 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 In a 100% stacked bar chart, the same data from Figure 3.4 is easier to read.