Md. Asaduzzaman
A stacked column chart is an Excel basic chart that can show comparisons over time or categories. It can stack one data on top of the other in vertical columns and make a graphical comparison of data of different categories. In this article, we will see how to create a stacked column chart in Excel.
Below, we presented a sample method using which you will understand how we can create a stacked column chart in Excel.
Download Practice Workbook
You can download the Excel workbook from here.
What is a Stacked Column Chart in Excel?
Definition: A stacked column chart in Excel is a type of chart that is used to display multiple sets of data in a single chart, stacked on top of each other. It is to compare the contribution of different categories or groups to a whole or to show the trend of multiple series over time.
Types of Stacked Column Charts in Excel
There are 5 types of Column Charts in Excel, they are below.
- 100% Stacked Column Chart: In this chart, the height of each segment represents the percentage of the total value for that category or group, with each segment are on top of the other to form a full column.
- 3-D Stacked Column Chart: This chart is similar to the clustered stacked column chart, but adds a 3-dimensional effect to the chart to give it a more visually striking appearance.
- Multi-Level Stacked Column Chart: This chart displays nested categories or groups, with each level stacked on top of each other within the previous level.
- Variance Stacked Column Chart: This chart displays the difference between two data series stacked on top of each other, with one representing the target value and the other representing the actual value.
- Clustered Stacked Column Chart: This type of chart displays multiple data series side-by-side within each category or group, with each series stacked on top of each other.
These different types of stacked column charts can be used to display data in various ways and can help you to more easily analyze and understand your data.
Purpose and Uses of a Stacked Bar / Column Chart
- Comparing parts of a whole: A stacked bar/column chart is useful when you want to see how different categories or groups contribute to a total, such as sales revenue by product, or expenses by department. By stacking the bars/columns, you can easily see the total value and compare the contribution of each category or group to the total.
- Comparing trends over time: Stacked bar/column charts are also useful for showing trends over time for multiple data series, such as sales revenue over several years. By stacking the bars/columns, you can easily see the total value for each year and compare the contribution of each data series to the total over time.
- Analyzing changes over time: A stacked bar/column chart can be used to analyze changes in the relative contribution of different categories or groups over time. For example, you can use a stacked column chart to analyze changes in the relative market share of different products over several years.
- Identifying patterns and anomalies: A stacked bar/column chart can be used to identify patterns and anomalies in data. By examining the relative heights of the bars/columns, you can easily see which categories or groups are contributing the most or the least to the total, and identify any patterns or anomalies in the data.
- Giving an Eagles eye over the data cluster: When dealing with large or dense datasets, it can be difficult to identify patterns and trends in the data, especially when using traditional tabular formats. By using a column chart, a “helicopter view” of the data can be presented, allowing the user to quickly identify which categories or groups are contributing the most or the least to the total, and to identify any patterns or anomalies in the data.
Elements of the Stacked Column Chart
- Axis: The chart has two axes, the horizontal axis (also called the x-axis) and the vertical axis (also called the y-axis). The horizontal axis usually shows the categories or groups , while the vertical axis represents the comparing values
- Bars: The main visual element of the stacked column chart is the bars. Each bar represents a category or group, and the height of each segment within the bar represents the value of a sub-category or data series within that category or group. The segments are stacked on top of each other. Making it easy to compare the contribution of different sub-categories or data series to the total.
- Legend: The legend is a key element of the chart that helps to identify the different sub-categories or data series represented by the bars. It is usually located on the right-hand side of the chart. And is color-code to match the colors of the bars.
- Labels: Labels provide additional information about the data. Such as the values of the sub-categories or data series, or the total value for each category or group. Labels can be an addition to the bars, axis, or legend to provide additional context for the data.
- Title: The title of the chart provides a clear and concise description of the data being displayed. It is usually located at the top of the chart and should be clear and descriptive.
- Gridlines: Gridlines provide additional context for the display data. They are horizontal or vertical lines that run across the chart. And are to highlight specific values or intervals on the axis.
How to Create a Stacked Column Chart in Excel: 4 Suitable Examples
In order to create a stacked column chart in Excel, we will need a dataset. There are four ways to create a stacked column chart in Excel. Now we will see all the ways to do the job. If you face any kind of compatibility issue, try to use the Excel 365 edition.
1. Create a Basic Stacked Column Chart in Excel
This is the most commonly used stacked column chart in Excel. We can easily stack data of different categories in one column and compare the data with this stacked column chart. Let’s see the steps to create a basic stacked column chart in Excel.
Steps:
- First of all, open the worksheet which contains the dataset.
- Secondly, select the (C5:E8).
- After that, click on the Insert tab >> Insert Column or Bar Chart drop-down >> Stacked Column Chart option like the below image.
- Finally, you will see the stacked column chart of your data in the following image.
Read More: Column Chart vs Bar Chart in Excel (6 Useful Examples)
2. Make a 100% Stacked Column Chart in Excel
Sometimes, we want to compare our data out of 100%. A 100% stacked column chart can be helpful for this purpose. Now we will see how to create a 100% stacked column chart in Excel.
Steps:
- First, open the worksheet which contains the dataset.
- Next, select the (C5:E8) cells.
- After that, click on the Insert tab >> Insert Column or Bar Chart drop-down >> 100% Stacked Column Chart option like the below image.
- As a result, you will see a 100% stacked column chart of your data like the following image.
Read More: Show Percentage in 100 Stacked Column Chart in Excel
3. Generate Excel 3D Stacked Column Chart
We sometimes want to compare our data of different categories in a 3D chart. At that time, creating a 3D stacked column chart in Excel could be an option for us to serve the purpose. Here is an example of how to generate a 3d stacked column chart in Excel.
Steps:
- First, open the worksheet which contains the dataset.
- Next, select the (C5:E8) cells.
- Subsequently, click on the Insert tab >> Insert Column or Bar Chart drop-down >> 3D Stacked Column Chart option like the below image.
- Hence, you will see the 100% stacked column chart of your data in the following image.
Read More: How to Insert a 3D Clustered Column Chart in Excel (with Easy Steps)
4. Create 3D 100% Stacked Column Chart
If we want to compare our data out of 100% in a 3D chart, then a 3D 100% stacked column chart can be a good choice. Now we will see how to create a 3D 100% stacked column chart in Excel.
Steps:
- First, open the worksheet which contains the dataset.
- Then, select the (C5:E8) cells.
- Furthermore, click on the Insert tab >> Insert Column or Bar Chart drop-down >> 3D 100% Stacked Column Chart option like the below image.
- Finally, you will see the 3D 100% stacked column chart of your data like the following image.
Stacked Column Chart with 5 Separate Variables (Or More)
In the previous example, we have seen how we can create a stacked column chart using 3 or 4 separate variables or data levels. In this example, we will talk about how we can create a stacked column chart with 5 separate sets of data.
Steps:
- First, select the range of cells B4:G8. And then go to Insert tab > 2D Column and click on the Stacked Column option.
- Right after clicking on the Stacked Column, we will see that there is a chart with the stacked column present. After some modifications, the chart will look like the below image.
- The chart now contains all the year’s revenue in each column which denotes each company.
Frequently Asked Question
How does a stacked column chart work in Excel?
A stacked column chart in Excel is a type of chart that displays data in vertical columns that are stacked on top of each other. It is to compare the contribution of different series or categories to a whole.
In a stacked column chart, each column represents a series or category, and the height of the column represents the total value of that series or category. The columns are stacked on top of each other. So the height of each column is determined by the value of the previous column plus the value of the current column. The chart can be useful for comparing the total value of each series or category. As well as the relative contribution of each subcategory to the whole.
How do I create a stacked column chart in Excel with multiple columns?
If you have a single string of data with multiple levels of subsequent data, then you can create a stacked column chart out of it quite easily. But if you have multiple strings of data with each string containing multiple levels of data then you also can create a stacked column chart with multiple columns. Examples of single-column and multi-column stacked charts are given below.
- An example of a single stacked column chart is given below. Where we have the single data entry of a company named Company A.
What are the disadvantages of a stacked column chart?
- The segments in a stack column chart being on top of each other can make it difficult to compare individual values. This becomes particularly challenging when the segments are narrow or there are many categories or series in the chart.
- A column chart that is in stack condition also has the potential to create a misleading visual representation of the data depending on how the data arrange. If the segments within a column are not in descending order by value. It can create the impression that a smaller segment is larger than it really is.
- Furthermore, comparing values across categories in a column chart that is in stack condition with multiple columns can be difficult. As the columns are not align condition with each other.
Overall, while stacked column charts can be useful for certain types of data and comparisons. It’s important to consider the potential disadvantages. And choose the chart type that best represents your data and effectively communicates your message.
Things to Remember
- If you want to represent your data and compare them in the 2D chart. Then a stacked column chart or 100% stacked column chart would be a better option.
- If you want to represent your data and compare them in the 3D chart. Then a 3D stacked column chart or a 3D 100% stacked column chart would be a better option.
- A stacked column chart becomes visually complex when categories or series are increased.
Conclusion
Hence, follow the above-described methods. Thus, you can quickly learn how to create a stacked column chart in Excel. We showed 4 different types of stacked column charts. And also a Column chart with 5 different sets of data. Among the methods, the 3D stacked column chart is the most common one. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.
