Stacked Chart in Excel

What are Stacked Charts in Excel?

Stacked charts in excel compare parts of a whole or show how the parts change with time. There are different types of stacked charts in Excel. They are stacked bar charts, stacked column charts, and 100% stacked bar/column charts. Stacked column charts have data series stacked one on top of the other vertically. 

In stacked bar charts, each bar has sub-bars that show how the secondary categorical variable has contributed to the total. A 100% stacked chart shows the relative percentage of multiple data series stacked as bars/columns, where the stack’s total is 100%.

For instance, let us consider the scores of a few students in Maths, Science, and English. Now, we plot a stacked bar chart to compare their different scores to each other and the total. First, select the entire cell range from A2 to D10. Next, go to the Insert tab, and in the group Charts, click on the “Insert Bar or Column Chart” option. From here, you can select the Stacked Bar option under 2-D Bar.  You get a bar stacked chart in Excel as the output. 

How to Create Stacked Chart in Excel?

As explained above, multiple stacked chart options in Excel include 2-D and 3-D bars and columns, 2-D and 3-D 100% stacked bars/columns, etc. Here’s an example of how you can use a stacked chart in Excel, more specifically, a stacked chart in Excel with multiple columns.

Below is a table containing sales details of some products at a store chain for November and December.

Examples

Let us look at some examples where we apply the concept of creating a stacked chart in Excel, which can be used to understand a particular entity’s share in the total number.

Example #1

Let us look at the details regarding the construction of a building. The number of workers employed during construction in various months has been listed in the table below.

• Step 1: Now, we can represent the above details in a stacked column chart. It helps compare multiple categories when the data series are stacked over each other. In addition, it can show the change in the requirement of different worker types over time.

Select “Insert Column and Bar Chart” in the Insert tab and choose “Stacked Column” under 2-D Column.

• Step 2: Once you select it, you get a stacked column graph.

• Step 3: You can reorder the series to plot the “Construction Workers” in any position, for instance, the middle, by clicking on the light blue squares representing construction workers at the top of the column where they are plotted. You get these tiny circles at their edges.

• Step 4: Right-click on this square and choose “Select Data” from the menu.

You can take the “Construction Workers” to the middle by clicking on the arrow in the pop-up window. Press OK.

You can observe that the “Construction Workers” series is in the middle of the graph now.

From this chart, we can see the total workers of each category required are stacked, one on top of the other. Here, the data series and the categories are limited in number; hence, these charts prove helpful.

Example #2

The employees of an organization have their performance reviewed half-yearly for two years for an important project. The company wants to observe the trend of whether the overall performance of its employees is increasing/decreasing with time. For this purpose, the 100% stacked column chart is a very handy tool. Here’s how the data is represented in a table.

• Step 1: Select the cells from A3 to D7, and as seen before, choose the option “Insert Column and Bar Chart” in the Insert tab and select “100% Stacked Column” under 2-D.

A 100% stacked column chart shows the relative percentage of many data series in stacked columns. Here, the total of stacked columns is 100%. In the case of stacked column charts, the relative size of each component in a column is difficult to compare.

• Step 2: To get more detailed information, you can right-click on the top right corner and add chart elements per your requirement.

1. The above is a 100% stacked column chart.
2. Here, we classify the performance of the employees of an organization as “Did not meet expectations,” “Met expectations,” and “Exceeded expectations.”
3. From the graph, around 50% of the employees did not meet expectations in the last half year.
4. The trend over two years is seen helping us understand that the number of employees not meeting expectations has been growing over the two years, requiring swift remedial measures to improve the company’s performance.

Example #3

A company developed a few apps and wanted to survey the percentage of men and women using their apps. A 100% stacked bar chart helps visualize the percentage of men to women using the apps.

• Step 1: We can use the 100% stacked bar chart. It shows the relative percentage of men and women using the apps in the form of stacked bars. Select the above table from A1 to C6. Now, go to the “Insert Column or Bar Chart,” and choose the option “ 100% Stacked Bar” under 2-D Bar.

• Step 2: You get a horizontal stacked chart in Excel portraying the percentage of men and women using the various apps. Now, to change the color of the bars, click on the blue side of the bar and then right-click and choose the option “Format Data Series.…”

• Step 3: Choose a color of your liking and press OK. You can make similar changes for other features of the bar too.

Now, we get a 100% stacked bar chart for the data series.

You can observe from the chart the percentage of women using the different apps, which helps the company decide which app needs to add more women as its users.

Stacked Chart in Excel – When to Use?

Stacked charts can compare parts of a whole and show how they change with time. In stacked charts, we extend the standard bar or column chart, comparing the values of only one category’s variables to multiple categories.

• Stacked charts in Excel are best suited for comparing part-to-whole changes with time.
• Instead of a single category, these stacked charts can be used with multiple categories and data series.
• The 100% stacked chart in Excel can represent data as a percentage, that is, parts of a whole, displaying their relative percentage. These are very useful for identifying survey results.
• 100% stacked charts are most helpful when you compare two segments, for instance, Approve or Decline, male or female, etc.

When Not to Use?

• Comparison of values – With the stacked chart in Excel, it isn’t easy to compare all estimates except the first data series next to the axis. So, we should not use stacked charts for scenarios that require comparing values.
• In the case of 100% stacked bars, we lose the values when displayed as a percentage, and these charts sometimes do not display patterns of change over time as clearly other types of charts like lines do.

Important Things to Note

• If you are comparing the values of sub-bars/columns, it is better to opt for the line chart, since comparing them within a primary bar/column is difficult.
• The above issue can be fixed by adding annotations to the bars/columns indicating the size of the sub-bars.
• You can change how the chart is displayed, like color, fill, etc., by right-clicking on the chart and choosing the option “Format Data Series.”

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the Stacked Chart in Excel, with its charts and examples. We can download the template here to use it instantly.

Recommended Articles

Guide to Stacked Chart in Excel. Here we learn to create stacked column and bar charts, with examples & downloadable template. You can learn more from the following articles –

• Dynamic Chart In Excel
• Funnel Chart In Excel
• Comparison Chart In Excel