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.
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- Save a Broken Chart with Switch Columns/Rows in Chart Tools
- Build a Better, Cleaner, More Professional Line Chart
- How To Add an Average Value Line to a Bar Chart
- How to Add a Vertical Line to a Horizontal Bar Chart
- How to Add Totals to Stacked Charts for Readability
- How to Show Percentages in Stacked Bar and Column Charts
- Building Charts with Multiple Series and Custom X-Axis
- How to Create Waterfall Charts in Excel