- 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
Understanding the Shortcomings of Stacked Column Charts
In a stacked column chart, Series 2 is plotted directly on top of Series 1. Series 3 is plotted on top of Series 2, and so on. The problem with this type of chart is that the reader can tell if the total is increasing or decreasing. The reader might also be able to tell if Series 1 is increasing or decreasing. Because all the other series have differing start periods, it is nearly impossible to tell whether sales in Series 2, 3, or 4 are increasing or decreasing. In the top chart in Figure 3.37, which regions are responsible for the increase from 2001 to 2006? It is nearly impossible to tell.
)
Figure 3.37 In the top chart, no one will draw any conclusions about the West, Central, or South regions.
Stacked column charts are appropriate when the message of the chart is about the first series. In the lower chart in Figure 3.36, the message is that the acquisition of a new product line saved the company. If this new product line hadn't grown quickly, the company would have had to rely on aging product lines that were losing. Because the message here is about the sales of the new product line, you can plot this as the first series, and the reader of the chart will be able to see the impact from that series.
){kind=link}
Using a Stacked Column Chart to Compare Current Sales to Prior-Year Sales
The chart in Figure 3.38 uses a combination of a stacked column chart and a line chart. The stacked column chart shows this year's sales, broken out into same-store sales and new-store sales. In this case, the same-store sales are plotted as the first series in white. The new-store sales are the focus and are plotted in black.
)
Figure 3.38 The current-year sales are shown as a stacked column chart, with last year's sales as a dotted line.
The third series, which is plotted as a dotted line chart, shows the prior-year sales. While the total height of the column is greater than last year's sales, there is some underlying problem in the old stores. In many cases, the height of the white column does not exceed the height of the dotted line, indicating that sales at same store are down.
The process of creating this combination chart involves a few steps during which the chart looks completely wrong. You need to overlook the chart in those steps and keep progressing through the steps, as follows:
- Set up your data with months in column A, old-store sales for this year in column B, new-store sales for this year in column C, and last year's sales in column D.
- Select cells A1:D13 and create a stacked column chart. Initially, Excel stacks prior-year sales on top of the other sales, and you have a chart that is not remotely close to the expected outcome.
- Click the top bar to select the third series. Choose Design, Change Chart Type, Line Chart. An important distinction here is that the first two series are plotted as stacked charts. The third series is plotted as a regular line, not as a stacked line.
- Use the Format ribbon to format the third series as a dotted line. Format the colors of the first two series as shown in Figure 3.38.