How to Create a Stacked Bar Chart in Excel (Easily) - Spreadsheeto (2024)

What is a stacked bar/column chart?

As evident from the name, a stacked bar chart is the one that represents data series stacked on top of one another.

It is a cumulative bar chart that represents data in adjacent horizontal bars only slightly more advanced. The bar chart allows users to compare the values of different categories individually and their contribution to the whole data in one place 🤯

Each bar represents a category of data and the height of the bar represents the total value of that category. The bar is divided into subcategories of the data shown by shaded parts of the bar.

There are four commonly used variations of a stacked bar chart, these include a 2D chart, a 100% 2D chart, a 3D chart and a 100% 3D chart 📈

These are not very different from each other, a 2D graph gives you the view of a place whereas a 3D shows you the graph in three dimensional.

The 100% bar charts show all bars of the chart reaching the 100% mark which is helpful when you want to compare the relative size of each sub-section in a category.

When to use a stacked bar/column chart?

The answer to this question depends on what you require from the chart. A stacked bar graph offers multiple uses, a few of which include visualizing large data sets, performing data analysis, quick comparisons and more 💹

Let’s see some common uses of a stacked bar chart below.

• Overview of a large data set
• Comparison and contrast between categories of a dataset
• Distributing data in different classes
• Visualization of data grouped in different categories
• Dynamic data visualization showing changes made over time

You would see stacked bar charts being used in the corporate world to show the current trends or changes made in monthly or annual sales.

That’s because data visualization makes the task of explaining a dataset of over a thousand entries way easier 😀

How to create a stacked bar chart in Excel?

Let’s now see how to create a stacked bar chart in Excel below 🔽

Say, we have the following data set which has the quarterly sales of different categories across four regions. We want to plot his data on a stacked bar chart to see which product made the maximum sales.

To do that,

Step 1) Select the entire data set on the worksheet or press CTRL + A to select data.

Step 2) Go to the Insert tab and click on Recommended Charts.

Step 3) The Insert Chart dialog box will appear on the screen.

Step 4) On the dialog box, go to the All Charts tab.

Step 5) Select bar from the categories.

Step 6) In the bar category, click on the second option and select the stacked bar chart.

Step 7) Press Ok.

The chart will appear on the screen as:

Note that the product categories are plotted on the Y axis whereas the regions and quarters are on the X axis ❌

Let’s now see what more you can do with this chart.

Now that you have your chart, you can make edits to it as you like. At the top right corner of the chart, there is a plus (+) icon that takes you straight to Chart Elements.

You can select different chart options from here to alter your chart. If you uncheck the Axes box, the name of the axes will disappear from your chart.

Similarly, if you want to have separate titles for each axis, click on the Axis Titles option and add a title for each axis.

You can also add the chart title, the data labels, and error bars. If you want to remove the lines behind the stacked bars, uncheck the Gridlines option.

Unchecking the Legend box at the bottom of Chart Elements removes the Names of regions and Quarters given at the bottom of the chart.

As the picture suggests, the last icon works like a funnel. It filters out elements of the chart that you want to eliminate. This is useful when you want all the attention on a certain aspect of the chart.

For example, in our chart above, we unchecked the categories Clothing and Home Goods and region of North in all quarters. As a result, Excel shows the chart with these components removed.

How to create a stacked column chart?

Creating a stacked column chart is pretty much the same as creating a stacked bar chart in Excel. The only difference is that the stacked column chart represents data in vertical bars 📊

Below are some easy steps to follow to create a stacked column chart.

We have the same sample data as earlier.

To create a column chart,

Step 1) Select the data set in your worksheet.

Step 2) Go to the Insert tab and click on Recommended Charts.

Step 3) The recommended charts dialog box will appear on the screen.

Step 4) On the dialog box, go to the All Charts tab.

Step 5) In the column category, click on the second option and select the stacked column chart.

Step 6) Press Ok.

The chart will appear on the screen as:

How cool is that?

The customization options are the same for both charts. You can make edits to your chart like we did with the stacked bar chart.

You can change the style of the chart, the colors of bars and the number of items appearing on the chart using the options given at the top right corner of the chart.

Troubleshooting stacked charts

Creating a tacked bar/column chart is an extremely easy task since Excel does most of the behind-the-scenes work. However, if you are encountering problems projecting your data on the chart as desired, you might be making some mistake 😟

Let’s see the common errors in creating a stacked chart and what you can do to fix them.

Not selecting complete data set

The prime reason why your chart might be misbehaving or not showing results as desired could be because you are selecting incomplete data.

The sample data in the example above was rather miniscule so even leaving that out won’t make much of a difference.

But for organizations dealing with large data, you need to double-check the data selection because each value is important – let alone the numerical values of an entire field. In case you forgot to select some part of the data, simply click on the chart.

You will see the data set highlighted in red and blue for the x and y axis, respectively. Simply drag the Fill Handle to include the remaining data set to your chart and it’s done 😃

Choosing wrong chart type

Stacked bar/column charts are used widely to represent trends and comparisons of data but this does not mean they are well suited for every data set.

Your chart type should be in line with the amount of your data. A stacked chart can effectively represent a moderate size of data 📗

However, projecting a huge data set will not only make the chart an ineffective way to present the data but will also make it difficult to understand the data.

To avoid these complications, choose different types of charts that complement your data set.

Conclusion

In this tutorial, we saw how to create a stacked bar chart in Microsoft Excel. We also saw how to create a stacked column chart and how you can customize it as you like 🧐

To learn more about charts in Excel, give the following articles a read:

How to Create a Chart Area in Excel (2024)

How to Add Axis Labels in Excel Charts (X and Y Titles)

We hope you enjoyed reading this article as much as we did crafting it! 🤗