Excel Stacked Bar Chart with Subcategories (2 Examples)

In Excel, there are different types of charts. A stacked bar chart is one of them. The main objective of this article is to explain how to make an Excel stacked bar chart with subcategories.

Excel Stacked Bar Chart with Subcategories: 2 Simple Ways

Here, I have taken a dataset that contains the Sales and Profit of four different products for Texas and Missouri. I will use this dataset to show 2 different methods to make an Excel stacked bar chart with subcategories.

1. Using Stacked Bar Chart Feature to Create Excel Stacked Bar Chart with Subcategories

In this method, I will show you how to make an Excel stacked bar chart with subcategories using the Stacked Bar Chart feature.

Steps:

Firstly, select the dataset.
Secondly, go to the Insert tab from the Ribbon.
Thirdly, select the Insert Column or Bar Chart from the Charts option.

Now, this will lead you to a drop-down menu.

After that, from the drop-down select the Stacked Bar.

Here, you will see a chart has been inserted into the worksheet. I got the chart you can see in the following picture. But, this is not the chart I want.

Now, I will change the chart as I want it to be. I want to change the rows and columns.

Firstly, select the stacked chart.
Secondly, go to the Chart Design tab.
Thirdly, select Switch Row/column.

Here, you will see that I have got my desired chart. At this point, you can format the data series of the chart.

Firstly, Right-Click on any bar of the stacked bar chart.
Secondly, select Format Data Series.

Format Data Series dialog box will appear on the right side of the screen.

Now, you can change the gap width. Here, I changed it to 60%. You can change it to your liking.

After that, Right-Click on any bar.
Next, select Add Data Labels.

This will add Data Labels to your stacked bar chart. In the following picture, you can see that I have added Data Labels to my chart and this is how it looks at this point.

Now, you can format the chart.

First, go to Chart Styles.
After that, select Styles.
Next, you can select any chart format from there. Here, I have selected the marked format in the image below but you can select any other design you like.

Now, you can add a chart title to your stacked bar chart.

You can also change the colors of the stacked bar chart.

To begin with, go to the Color from the Chart Styles option.
After that, select the color you like. Here, I selected the marked color in the image which was the default color of the chart that’s why you do not see any change.

This is what my chart looks like after formatting.

Finally, I have got my desired stacked bar chart with subcategories.

2. Use of Clustered Bar Chart Feature to Create Excel Stacked Bar Chart with Subcategories

In this method, I will use the Clustered Bar Chart feature to create an Excel stacked bar chart with subcategories. For this method, I will use the same dataset but want to switch the row and column for the stacked bar chart.

Steps:

To begin with, select the dataset.
After that, go to the Insert tab from the Ribbon.
Next, select the Insert Column or Bar Chart from the Charts option.

Now, you will see a drop-down menu.

After that, select Clustered Bar.

Here, you will see that a clustered bar chart has been inserted into your worksheet. We don’t want that. We want a stacked bar chart.

To get our stacked bar chart,

Firstly, Right-Click on any bar.
Secondly, select Change Series Chart Type.

After that, add Profit to the secondary axis.
Next, select OK.

Now, you will see that the bars are stacked. But still, there is a problem. The two axes do not match each other.

Here, I will show you how to solve this problem.

Firstly, select the secondary axis and then Right-Click on it.
Secondly, select Format Axis.

Now, the Format Axis dialog box will appear on the right side of the screen.

After that, select the Bounds as the primary axis. Here, I selected 140 because our primary axis’ Maximum Bound is 140.

Here, you can see that the bar chart is now stacked properly.

Now, you can add data labels.

Firstly, Right-Click on any bar.
Secondly, select Add Data Labels.

After adding the data labels. You can format your stacked bar chart.

Firstly, go to the Chart Styles.
Secondly, select Styles.
Thirdly, you can select any chart format from there. Here, I have selected the marked format in the image below but you can select any other design you like.

Now, you can change the colors of the stacked bar chart.

Firstly, go to the Color from the Chart Styles option.
After that, select the color you like. Here, I selected the marked color in the image.

After formatting, this is my final stacked bar chart in the picture below.

Finally, I have got my desired Excel stacked bar chat with subcategories.

Practice Section

Here, I have given a practice sheet so that you can practice by yourself.

Download Practice Workbook

Stacked Bar Chart with Subcategories.xlsx

Conclusion

In this article, I explained two different methods by which you can make an Excel stacked bar chart with subcategories. Hope the methods were clear to you. You can download the practice workbook and practice by yourself. Finally, if you have any questions feel free to let me know in the comment section below.

<< Go Back to Stacked Bar Chart in Excel | Excel Bar Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!