Excel’s Stacked Bar and Stacked Column chart functions are great tools for showing how different pieces make up a whole. Unfortunately, the are somewhat limited, since they don’t automatically provide totals for the stack, and they don’t let you show the percentage contribution that each piece provides to the whole (like you can with pie charts in Excel). The good news is, there are work-arounds for displaying total volumes or dollars at the top of a stacked chart and percentages for each of the pieces. This tutorial will walk through the steps to get you results like you see in the image.
Building a Stacked Chart
To get started, we need to build a stacked chart. Most of the time, a vertical Stacked Column chart is the best option, but you can also use a Stacked Bar chart if your data (or data labels) require it. For this example, we’ll look at a few years of regional sales data.
To build a chart from this data, we need to select it.
Then, in the Insert menu tab, under the Charts section, choose the Stacked Column option from the Column chart button.
Your first results might not be exactly what you expect.
In this example, Excel chose the Regions as the X-Axis and the Years as the Series data. We want the exact opposite, so click on the Switch Row/Column button.
Now, we have something closer to what we want – a Stacked Column chart with Years on the X-Axis.