Excel Stacked Bar Chart with Subcategories (2 Examples) 

Get FREE Advanced Excel Exercises with Solutions!

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.

2 Simple Ways to Make 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.

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

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.

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

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.

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

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.

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

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.

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

  • 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.

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

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

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

Read More: How to Make a Stacked Bar Chart in Excel


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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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

Read More: How to Create Stacked Bar Chart for Multiple Series in Excel


Practice Section

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


Download Practice Workbook


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo