Table Of Contents
Key Takeaways
- The stacked bar chart in excel helps represent data in the form of bars to facilitate a comparison between data values.
- The stacked bar chart helps study the impact of the changes in each segment on the total value across categories.
- Every bar consists of different segments (or elements) which are components that make the bar.
- In the horizontal stacked bar chart, the data segments are adjacent to each other.
- In the vertical stacked bar chart, the data segments are stacked over one another.
- It is not preferable to use stacked bar charts with large data sets that require in-depth data analysis.
How to Create a Stacked Bar Chart in Excel?
In Excel, there are multiple chart options like 2-D bars, 2-D 100% stacked bars, 3-D bars, 3-D 100% stacked bars, and so on.
Let us consider the following examples to create the stacked bar chart in Excel.
Example #1–The 2D Stacked Bar Chart
We have the monthly sales revenue generated by two countries–the United States of America and India. We want to create a 2-D stacked bar chart.
Time needed: 3 minutes.
The steps to create a 2-D stacked bar chart are listed as follows:
Select the data that you want to display in the form of a chart.
In the Insert tab, click Column Charts (in Charts section) and select “2-D stacked bar.”
A chart appears, as shown in the following image. The stacked bar chart compares the sales revenue generated in different months with respect to time.
Once the stacked bar chart is inserted, the Design and Format tabs appear on the Excel ribbon. These tabs help change the chart appearance, layout, styles, and so on.
It is also possible to switch the row or column, insert and format a shape, etc., as shown in the succeeding image.
The different formatting options, as shown in the following screenshot, help enhance data visualization.
To format the chart, click “chart options” and “text options” under the “format chart area.”
Example #2–The 100% 2D Stacked Bar Chart
Let us consider the data of the previous example again. The steps to create a 100% 2-D stacked bar chart are listed as follows:
- Step 1: In the Insert tab, click Column Charts (in Charts section) and select “100% 2-D stacked bar.”
- Step 2: The chart appears, as shown in the following image. The percentages are displayed on the x-axis (horizontal) and the months are displayed on the y-axis (vertical).
The 100% stacked bar chart shows the percentage sales of both the countries (out of total sales) from 2015 to 2018. The bars are colored contrastingly to differentiate the USA from India.
The bars representing sales of the USA are in blue while those of India are in orange. The 100% stacked bar chart allows a comparison of the total sales generated by both the countries.
Example #3–The 3-D Stacked Bar Chart
Let us consider the data of the previous example again. The steps to create a 3-D stacked bar chart are listed as follows:
- Step 1: Select the data to create a chart.
- Step 2: In the Insert tab, click Column Charts (in Charts section) and select “3-D stacked bar.”
- Step 3: The chart appears, as shown in the following image.
Example #4–The 100% 3-D Stacked Bar Chart
Let us consider the data of the previous example again. The steps to create a 100% 3-D stacked bar chart are listed as follows:
- Step 1: In the Insert tab, click Column Charts (in Charts section) and select “100% 3-D stacked bar.”
- Step 2: The chart appears, as shown in the following image.
The results of example #2 and example #4 are the same. This is because the 3-D formatting only changes the appearance of the chart.
Relevance and Uses
The stacked bar charts are created to help the users compare the different segments simultaneously. The impact of the changes in each segment on the total value across categories can also be observed.
The scenarios in which the stacked bar chart can be used are listed as follows:
- Comparison: It allows easy comparisons of data. The data provided relates to production, sales, profits, and so on.
- Ranking: It shows the rankings across time periods. This is done by using different colors for different length of the bars.
- Survey result: It helps analyze the results of a survey. This is done by highlighting the key aspects and differentiating the data values.
- Part-to-whole differentiation: It differentiates every part of data from the whole part. This is done to show the focus area of the chart.
The scenarios in which the stacked bar chart should not be used are listed as follows:
- Large data sets: It is not preferable to use stacked bar charts with a large amount of data. This is because the chart becomes complicated, which makes it challenging to analyze.
- In-depth analysis: It is not suitable for long-term decision-making. This is because conducting an in-depth analysis of the data is difficult with a stacked bar chart.
The Guidelines to Use Stacked Bar Chart in Excel
The guidelines for proper usage of a stacked bar chart are stated as follows:
- It is enhanced visually by adding the trend line, title, legend, data label, and so on.
- It is formatted by right-clicking the bar and choosing the required specifications.
- It is given a title to make it easy for the user to understand the data it is representing.
- It does not return different results when a 2-D or 3-D version is applied to the same data.
- It is not applicable when the number of segments is large and a comparison between them is involved.