Creating Charts in Excel 2013 That Show Trends
- Choosing a Chart Type
- Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts
- Communicate Effectively with Charts
- 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
- Next Steps
Read Excel 2013 Charts and Graphs or more than 24,000 other books and videos on Safari Books Online. Start a free trial today.
This chapter is from the book
This chapter is from the book
This chapter is from the book
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.
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. If your data set contains 12 or fewer data points that represent a time period, choose a column chart to illustrate the trend over time.
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, where a column chart is appropriate. Figure 3.2 shows a chart of 100+ data points. With this detail, you should switch to a line chart 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 middle chart in this figure shows the same data set 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, consider using a bar chart. Another example is shown 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.
){kind=link}
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 2013? Rather than using a series of pie charts to show changes over time, use a 100 percent stacked column chart.
)
Figure 3.4. It is difficult to compare one pie chart to the next.
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 Figure 3.4 is easier to read in a 100 percent stacked bar chart.