Excel 100 stacked bar chart show percentage

Índice Show

What is 100% Stacked Column Chart
Points to Remember About 100% Stacked Column Charts
Inserting 100% Stacked Column Chart
Reading 100% Stacked Column Chart
Building a Stacked Chart
Get the latest Excel tips and tricks by joining the newsletter!
Other posts in this series...

In our earlier blogs, we learned the usage of Stacked Column Charts. 100% Stacked Column Chart in Excel is its subset. The chart is useful in showing the percentage contribution within the whole. Let us begin 😎

What is 100% Stacked Column Chart

100% Stacked Column Charts in Excel contain the multiple data series grouped together to show their percentage contribution in the whole. We can also use this chart to represent cumulative data series.

The Horizontal axis would represent the groups/categories that make the whole while the Vertical axis represents the percentage scale up to 100%.

Points to Remember About 100% Stacked Column Charts

The following are must-know points before using the 100% Stacked Column Chart in Excel:-

In 100% Stacked Column Chart, the height of each column would be constant as all the columns are representing a total of 100%.
Each Column is made up of a group of entities/values that sum up to form 100%.
We can add data labels on the chart to see the actual data values along with the percentages.
100% Stacked Column Chart becomes complex as we add more data series to the chart.
We cannot use this chart to compare the values of two data series of different columns as the height of all the columns would go to a constant of 100%.
You can learn about Formatting the chart and Chart Elements.
You can save your chart formatting using Chart Templates of Excel.

Inserting 100% Stacked Column Chart

Let us assume that we have the quarterly sales of a bakery in the different regions of the country. Below is its data:-

To insert a 100% Stacked Column Chart in Excel, follow the below-mentioned steps:-

Select the range of data A1:E5.
Go to Insert Tab.
In the Charts group, click on column chart button.

Select the 100% Stacked Column Chart from the 2-D Column Chart Section.

Reading 100% Stacked Column Chart

The chart inserted in the above section would be:-

Excel has an inbuilt algorithm that decides the orientation of rows and columns in the chart. You can always switch rows and columns i.e Quarters and Regions by the button Switch Rows/Columns in the Chart Format Tab on the ribbon

Read the following points to understand the chart structure:-

Q1 represents quarter 1 and comprises the sales of four regions i.e North, South, East and West.
There is a total of four Quarters each containing percenatage sum of regional sales.
Consequently, the total sales of Q1 would be sales of (40000+35000+30000+42000) North+South+East+West= 147000. This is 100% of the total sales pf Quarter 1.
Similarly, the total sales of Q2, Q3 and Q4 are 158000, 164000, 165000. These sales are different but the total is always represented by 100% for each quarter.
For instance, in Q1 we have:-
- North Sales as 40000. Percentage contribution = 40000/147000*100= 27.21%
- South Sales as 35000. Percentage contribution = 35000/147000*100= 23.81%
- East Sales as 30000. Percentage contribution = 30000/147000*100=20.41%
- West Sales as 42000. Percentage contribution = 42000/147000*100= 28.57%

We never need to calculate these percentages. However, they are implicitly calculated and represented on the chart. Since the chart is a stacked chart, the values are stacked on top of each other. The upper region would contain the sum of the percentage contribution of the regions lower to it.

This brings us to the end of the blog.

Thank you for reading. 😎

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.

Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

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!