- 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 values in the individual series is increasing or decreasing. The reader also might not 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 2010 to 2015.
)
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 effect from that series.
Case Study: 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. Although the total height of the column is greater than last year’s sales, some underlying problem exists 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. To create this chart, follow these steps:
- 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. Choose Insert, Recommended Charts. Click the All Charts tab in the Insert Chart dialog. Choose Combo chart from the left panel.
- In the bottom of the Insert Chart dialog, change the Chart Type for Old Stores to Stacked Column. The Chart Type for New Stores will automatically change as well. Leave Last Year as a Line chart. Do not check Secondary Axis for any of the charts. Click OK.
- 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.