- 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
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't tell whether the total is increasing or decreasing. The reader might not also be able to tell if Series 1 is increasing or decreasing. However, 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. For example, in the top chart in Figure 3.33, it is nearly impossible for the reader to tell which regions are responsible for the increase from 2004 to 2009.
)
Figure 3.33 In the top chart, readers are not able to draw conclusions about the growth of the three regions located at the top of the chart.
Stacked column charts are appropriate when the message of the chart is about the first series. In the lower chart in Figure 3.33, the message is that the acquisition of a new product line saved the company. If this new product line had not grown quickly, the company would have had to rely on aging product lines that were losing money. Because this message is about the sales of the new product line, you can plot this as the first series so the reader of the chart can see the impact from that series.
Using a Stacked Column Chart to Compare Current Sales to Prior-Year Sales
The chart in Figure 3.34 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.34 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. During those steps, overlook the chart 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. Select 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 tab to format the third series as a dotted line. Format the colors of the first two series as shown in Figure 3.34.