How to Create Stacked Bar Charts in Excel with 6 Examples- Download Template

Bar charts are one of the fantastic features of Microsoft Excel. All of your datasheets can be presented visually with these Bar charts. You can prefer horizontal or vertically stacked representation with this widely customizable feature. A bar chart allows you to compare the parts of different categories with their subcategories by stacking them side by side or vertically with different colored indications that are very visible at a glance.

In this article, we will learn about bar charts and how to use them.

Types of stacked Bar Charts in Excel

You have three types of the chart to choose from, offered by Microsoft Excel

• 2D stacked Bar Chart
• 2D 100% stacked Bar Chart
• 3D stacked Bar Chart
• 3D 100% stacked Bar chart

The types of bar charts differ and serve different purposes

How to Create a Stacked Bar Chart in Excel?

Consider the following simple data table of months and sell quantity for any company.

Now you want to have a bar chart for the data mentioned above.

Just follow the steps.

Step 01: Select the data range B3:C14 you want to represent in the chart.

Step 02: Click on the Insert tab. The chart appears after clicking in the Chart section, as marked in the image below.

The desired outcome will be as follows:

Customizing the appearance and elements of Bar charts

Step 01: Select the Bar chart you already created by double-clicking on it. 

Step 02: The Chart Design tab will appear. In the Chart Styles section, you will find a plethora of options for customizing your chart.

Change Colors: Just click on this option to set or change the color of your chart 

Quick Layout: You can choose different built-in layouts with just one click

Add chart element: You can customize every element of the chart by clicking on this option.

In the upper-right corner of your selected chart, Microsoft Excel also provides shortcut chart styling options.

6 Examples of the Application of Stacked Bar Chart in Excel

Example #1: Create a Stacked Column Chart with Two Sets of Data

The following datasheet contains two sets of data: one for children aged 0 to 14, and the other for people aged 15 to 64.

 Now let’s create a stacked chart with two sets of data

Step 01: Select the data range.

Step 02: Click the inset tab.

Step 03: From the chart section, select the 2D stacked Bar chart as follows: 

Your desired chart will appear as below

Example #02: Horizontal Stacked Bar Chart in Excel with Subcategories

Assume you’re given a data table with the sales and profits of five different products for companies A and B.

You are asked to create a stacked Bar Chart for the subcategories.

Step 01: Select the data range.

Step 02: Click the inset tab. Then go to the chart section.

Step 03: Select the 2D stacked Bar chart as follows.

Here is the final result you will get:

Example #03: Cumulative Stacked Bar Chart in Excel

The following table shows the sales quantities for each of the four seasons. You want to create a stacked bar chart for the cumulative sum of selling quantities.

To calculate the cumulative sum, insert the following formula in the output cell D3.

=SUM($C$3:C3)

Press Enter, and AutoFill the remaining cells in column D.

Let’s start step by step.

Step 01: Select the data range B3:D6 that you want to show in the chart

Step 02: Click on the “Insert” tab. Then, from the chart section, select the stacked column chart.

The output will be a stacked column chart as usual.

Step 03: Then, with the mouse pointer still on the chart, click the right mouse button.

Step 04: Click on “Change Chart Type.”

Step 05: A new window will be opened. Click on the combo option. Then click on the Stacked Area option. And then click on the OK button.

The end result will be as follows:

You can edit the Chart Title, series 1, and series 2 by just clicking on those texts.

Example #04: Stacked Bar Chart in Excel with Multiple Series

Suppose a company wants to deliver different products to different countries in four different quantities. In the dataset below, Q1, Q2, Q3, and Q4 demonstrate the quantity set the company wants to maintain.

Now let’s make a stacked bar chart step by step:

Step 01: Select the range of data in the sheet. After that, press the inset tab.

Step 02: From the chart section, select the horizontally stacked bar chart.

See the final result in the image below.

Example 05: Stacked Bar Chart in Excel with Percentages

Assume you have some information on the number of products sold. You want to show the percentage of sold quantities in the stacked bar chart.

Step 01: To convert the selling quantities into a percentage, input the following formula in the output cell D3:

=C3/SUM(C$3:C$7)

Step 02: Hit the Enter key and AutoFill the remaining cells of column D.

Step 03: Then select the data range D3:D7 and click the Percentage style option or press Ctrl+Shift+%

All data in column D will be converted to percentages at round figure.

Now that our data is prepared, let us begin creating charts.

Step 01: Select the B3:B7 column range. Then, while holding down the Ctrl key on your keyboard, select the data range D3:D7.

Step 02: Click on the inset tab. From the chart section, select the horizontally stacked chart.

See the desired output where percentages are shown along the X-axis.

Example 07: Diverging Stacked Bar Chart in Excel

Assume a company is collecting customer feedback, as shown below.

To create a diverging stacked bar chart, we need to set the data in the column named “neutral argument” to zero. Then the left and right sides of the “neutral argument” are to be considered 100%.

To separate the left side from the right side of the “Neutral” argument, we will multiply all the left-side data by -1.

Now, select the data range and press the inset tab.

Then, from the chart section, select the 2D stacked bar chart.

The output will be as below.

Then, double-click on the chart. A new tab will appear named “Chart Design.”

Again, click on the “Switch Row/Column” option.

This will alter the data shown on the X and Y axes.