Stacked Column Chart in Excel

What is Stacked Column Chart in Excel?

A Stacked Column Chart in Excel compares parts of a whole over time or across categories. Here, the data series are arranged one on top of the other in vertical columns. There are different types of stacked column charts you can create in Excel.

For instance, below is the sales revenue for four quarters of three units of an organization. We plot a stacked column chart to compare their sales performances across each quarter. First, select the cell range from A2 to A5. Next, go to the Insert tab, and in the group Charts, click on the “Insert Bar or Column Chart” option. You can select the Stacked Column option under the 2-D Bar from here.

5 Main Parts of Stacked Column Chart

1. Title: Gives an overview of what the chart is about.
2. The X-axis (horizontal): Different individual values to be presented.
3. Column Bars: The height of each column is the total of all the individual dataset values.
4. The Y-axis (vertical): Depicts the intervals between values.
5. Legend: It shows the category of the datasets in the columns.

Types of Stacked Column Chart

The types of stacked column charts available in Excel are:

• Basic Stacked Column – This type of chart compares parts of a whole and shows how they change with time. It is found in the Insert Tab under the 2-D Column in the Charts group.

• 3-D Stacked Column – This is similar to a basic stacked column but shows a three-dimensional column graph.

• 100% Stacked Column –  A 100% stacked chart represents the relative percentage of the data series stacked as columns, totaling 100%.

• 3-D 100% Stacked Column – This again is a 3-D version of the 100% stacked column chart. It compares the percentage each value contributes to the total, displayed in the 3-D form.

How to Create Stacked Chart in Excel?

There are various types of charts like the line and stacked column chart in excel performing different functions. Let us look at some examples of how to create these stacked charts in excel.

Example #1 – Basic Excel Stacked Column Chart

Let us look at an example of studying the male-female ratio of an organization. They want to know if there is a steady growth in the male-female ratio. Given below are the details of the employees across four years.

• Step 1: Select the excel cell references B2:C5. In the tab Insert, click the “Insert Bar or Column Chart” option in the Charts group.

• Step 2: Now, choose the option, Stacked column under 2D Column.

• Step 3: You can observe the stacked 2D column chart as shown below. Notice how the X-axis does not specify the years but the numbers 1 to 4.

• Step 4: Click on the x-axis and right-click again. Choose the option “Select Data.”

• Step 5: In the dialog box, click on “Edit” under “Horizontal Axis Labels.”

Choose the cell range which you want for the x-axis in this case it is A2:A5.

• Step 6: Press OK. Your horizontal axis is changed to the years. Thus, you can observe that the male: female ratio in the company is moving towards being equal, thereby showing that the company is striking the right balance between the genders.

Example #2 – 3D Stacked Column Chart

Here’s an example of how to use a 3D stacked column chart in Excel.

We have a table containing the scores of some students across three semesters. Next, we must create a stacked column chart in Excel to check their progress.

• Step 1: Let us plot a stacked column chart to compare their scores across semesters.

In the tab Insert, click the “Insert Bar or Column Chart” option in the Charts group.

• Choose the stacked 3-D column chart from the available charts.

• Step 2: Now, we get a chart as shown below. The stacked column chart compares the scores of the students showing their relative performance each semester. Once the stacked 3-D column chart is inserted, the Chart Elements and Chart Filters options on the top right help change its axis details, layout, styles, etc.

• Step 3: Let us try changing the stacked blocks’ color. Click on any of the grey blocks. They show circles along their edges, as seen below. Now, right-click and choose “Format Data Series.”

In the Format Data Series pane, choose Fill & Line, and change the color in the Color option.

• Step 4: Thus, the block is changed to pink. Likewise, you can select the other blocks and change their colors.

Example #3 – Steps to Create 100% Stacked Column

The table below shows the revenue details of different products of a company over three quarters. The company wants to observe which product has contributed more to its quarterly revenue.  For this purpose, the 100% stacked column chart in Excel is a convenient tool. Here’s how the data is represented in a table.

• Step 1:
  • Select the cells from A2 to D6.
  • Choose “Insert Column and Bar Chart in excel” in the Insert tab.
  • Select “100% Stacked Column” under 2-D.

A 100% stacked column chart shows the relative percentage of the data series in stacked columns. Here, the total of stacked columns is 100%.

• Step 2: In the above graph, you get the percentage contributed by each drink towards the revenue per quarter. However, we require the percentage contributedby each product per quarter. It will help us determine which product is doing well. Go to the “Chart Design” tab and choose the “Switch Row/Column” option in the Data tab.  

• Step 3:  You get the 100% stacked column chart below.

1. The above is a 100% stacked column chart.
2. Here, we classify the performance of the products per quarter from the graph.
3. From the graph, it can be observed that Fruit Punch contributed to the maximum revenue in Qtr 1.  
4. The grape drink repeatedly shows low revenues over three quarters, and some changes should be made to improve sales.
5. Strawberry sales have been steadily increasing across the quarters.

Example #4 – Steps to Create 3-D 100% Stacked Column

Let us look at the above example and try to turn it into a 3D 100% stacked column chart. It is effortless. Just select the chart by clicking on it.

• Step 1: Go to the Insert tab and choose the 100% stacked 3D Column chart option shown below.

• Step 2: The graph changes to a 100% 3D stacked column chart. The data can be interpreted similarly to a 100% stacked column chart.

Pros and Cons of Stacked Column Chart

Pros

• Multiple data series and categories are implemented in a single chart that would otherwise be complicated to study.
• You can study the relationship of each sub-category with respect to the whole category.

Cons

• As each segment of the column is not arranged along a common baseline, they are more difficult to read and understand.
• It is challenging to understand the size of each column segment.

Important Things to Note

• Among column charts in Excel, a stacked column chart in Excel works best when comparing a part against the whole.
• Stacked column charts show change over time as the total column lengths can be easily compared. 
• The more categories, the more complicated the chart becomes.
• A double-stacked column chart in excel carries out a comparison between two series of data. A stacked column chart with line in excel known as trendline is used for analytical purposes.
• The stacked column charts are beneficial when you simultaneously compare part-to-whole relationships and the change in the data series over time.
• For instance, you can compare the growth in revenue across different quarters in an organization and check which department contributed the most towards revenue building simultaneously.

Frequently Asked Questions (FAQs)

Download Template

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

Recommended Articles

This has been a guide to Stacked Column Chart in Excel. Here we learn how to create 2D, 3D & 100% stacked columns with examples & downloadable excel template. You can learn more from the following articles –

• Bubble Chart In Excel
• Interactive Chart in Excel
• Clustered Column Chart In Excel