A 100% stacked column chart is a sub type of a regular stacked column chart. The difference is that the 100% stacked version is meant to show the proportional breakdown of multiple data series in stacked columns, where the total height is always 100%. In this worksheet, we have quarterly sales data broken down by region and quarter. To start off, I’ll create a normal stacked column chart. I’ll flip the chart with the Switch Column/Rows button to group sales by quarter instead of by region. Now before we look at the 100% stacked option, let’s review this chart. Notice the size of the stacked bars is proportional to sales. For example, Q4 sales in the South is clearly a smaller number than Q4 sales in the North and West regions. In addition, we can see that overall sales have been increasing modestly in all 4 quarters. And the vertical axis gives us a scale to estimate sales in each quarter. So that’s a standard stacked column chart. Now let me duplicate this chart and then change the chart type to 100% stacked column. Notice how the vertical scale is now percentage-based and each column represents 100% of all sales in a given quarter. The size of the bars that make up a column is proportional to sales, but it’s tricky to compare, since regional sales are somewhat similar. We’ve also lost the ability to compare overall sales in each quarter. For example we can’t see that Q4 had more sales than Q3. So how can we improve this chart without adding too much clutter? Well, one option is to add data labels to show the values in the stacked bars. And I can easily do this with the Chart Elements menu. As we’ve seen before we might want to use a custom number format to show numbers in thousands. The fastest way to this is apply the format to all values in the table. As long as the “linked to source” option is enabled, this formats all data labels at once. Now, looking at this chart, you might wonder how to show percentages instead of values? We’ll look at how to do that in a separate video.
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.
