Stacked area charts make sense when you want to show changes in a part-to-whole relationship over time. 100% stacked charts are focused on proportions, at the expense of actual values. To better explain this, I’ll create a basic stacked area chart, then compare with a 100% stacked version. First, I’ll select data, excluding totals. Then I’ll use recommended charts to insert a stacked area chart. The result shows total sales, and how much each product line contributes to total sales. The vertical axis is automatically scaled to fit the data, in units of $5000. Plot order follows the source data. Next I’ll insert a 100% stacked area chart. This is a case where we don’t find the 100% stacked option in Recommended Charts. And, if I check the line chart icon, where area charts live, Excel is not recognizing the series correctly. Since we already have a stacked area chart, an easy workaround is to duplicate this chart, and then change the chart type to a 100% stacked version. Now that we have both charts on the worksheet, let’s compare some similarities and differences. Both charts show product sales trends in different ways. Unlike the basic stacked area chart, the 100% stacked chart has a vertical axis based on percentages. In each year, the sales of all four products always equals 100%. However, we no longer see actual sales numbers. In fact, looking at the 100% stacked chart, we can’t tell that sales are increasing in all 4 years. This information is simply not there. On the other hand, the 100% stacked version does show information about proportions we can’t easily see in a regular stacked chart. For example, we can see that banjos have stayed near 30% of total sales, while both hammocks and cycling bags now make up a smaller percentage of sales overall. We can also see that organic flannel has gone from near zero percent of sales to more than 20% of total sales. These are interesting observations. However, but be aware that 100% stacked charts can easily be misinterpreted. For example, some people might think hammock sales are declining, based on the shape of the hammock area. In fact, hammock sales have increased somewhat. Only their percentage is declining.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
