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.
Read More: How to Sort Bar Chart Without Sorting Data in Excel (with Easy 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.
Read More: How to Make a Stacked Bar Chart in Excel (2 Quick Methods)
- Excel Add Line to Bar Chart (4 Ideal Examples)
- How to Make a Bar Graph in Excel with 3 Variables (3 Easy Ways)
- How to Color Bar Chart by Category in Excel (2 Easy Methods)
- Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)
- How to Show Percentage in Bar Chart in Excel (3 Handy Methods)
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.
Read More: Excel Chart Bar Width Too Thin (2 Quick Solutions)
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
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.
- How to Plot Stacked Bar Chart from Excel Pivot Table (2 Examples)
- How to Make a Bar Graph in Excel without Numbers
- Excel Bar Chart with Line Overlay (Create with Easy Steps)
- How to Create Stacked Bar Chart with Dates in Excel (3 Examples)
- How to Make a Diverging Stacked Bar Chart in Excel (with Easy Steps)
- Excel Stacked Bar Chart with Subcategories (2 Examples)