Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. While working with datasets, we often need to represent the data in charts. Excel offers various types of charts. With the help of these built-in charts, we can formulate some other charts too. In this article, I am going to show how to create stacked and clustered bar chart in Excel.

Download Practice Workbook

Download this workbook and practice while going through this article.

3 Easy Steps to Create Stacked and Clustered Bar Chart in Excel

This is the dataset for today’s article. We have quarterly sales in 4 regions for the years 2020 and 2021. Using this dataset we will create a stacked and clustered bar chart.

Step 1: Change the Layout of Dataset

Since we have to formulate a new type of chart, we have first to rearrange the dataset. We will rearrange it in such a way that,

• There is a blank row at the top.
• There are two blank rows below each row.

Something like this.

Let’s see how to do it.

• Put the serial number to the left of the rows. Since we need 2 blank rows below each row, copy the numbers twice. Also put 0 for the blank row at the top.

• Then, select A5:J17. After that, go to the Data tab >> sort from lowest to highest.

• Excel will sort on the base of the serial numbers and you will have a blank row at the top and 2 blank rows below each row.

• Now, delete the serial numbers and format the dataset the way you wish. I have done it in this way.

• Now, select the quarterly sales for 2021 and drag them below one row.

Your dataset is ready for the next steps.

Step 2: Create a Stacked Bar Chart

This time, we can go to create our chart.

• Select B4:J17. Then go to the Insert tab >> select stacked bar chart.

• Excel will create a stacked bar chart.

Step 3: Reduce Gap Width to Cluster Chart

Now, I will increase the width of the bars and reduce the gaps to look the chart visually more attractive.

• Select any bar. Then right-click your mouse to bring the menu. After that, select Format Data Series.

• From the Format Data Point option, reduce the Gap Width to 10%.

• Your chart will now look like this.

Things to Remember

• Excel has built-in clustered charts and stacked charts. But these are separate and Excel does not have a chart type that is a combined one.

• You can format and design your chart the way you want from the Chart Design and Format

Conclusion

In this article, I have explained how to create stacked and clustered bar charts in Excel. I hope it helps everyone. If you have any kind of suggestions, ideas, or feedback, please feel free to comment down below.

The post How to Create Stacked and Clustered Bar Chart in Excel (with Easy Steps) appeared first on ExcelDemy.