How to Create Stacked Column Chart or Stacked Bar Chart in Excel

Haleema Qayyum


Haleema Qayyum

How to Create Stacked Column Chart or Stacked Bar Chart in Excel

Table of Contents

In a world where everything has been so digitally oriented, we’re still searching for ways to make our data easy to understand. Charts on Excel make this possible very quickly. this article will discuss how to create stacked column chart excel.

Not only are they designed to make the task of making a chart much more straightforward, but they also help you better graphically create necessary data for easier understanding.

Stack Column Chart

The bar chart is a well-established format for displaying large pieces of data; the stacked bar is a variant of the bar chart and helps compare sub-segments of results or show which sub-total adds more to the total.

This guide will teach you what data is most meaningful to view in a stacked bar chart and construct one in Excel.

We’ll also teach you how to build a stacked bar chart and column chart in Smartsheet, an easy-to-use work-management platform that allows real-time visualization of work progress so you can make smarter data-driven decisions.

What is a Stacked Bar Chart?

A stacked column chart is a simple Excel chart that makes partial-to-whole comparisons over time or across categories. In the stacked column chart, the data series are stacked in vertical columns on top of each other.

Stacked column charts will display improvements over time, so comparing total column lengths is easy. However, except for the first data series (next to the x-axis) and the bar’s overall length, it is impossible to compare the relative size of the components that make up each bar. Data series or categories are also there, complicating stacked column charts.

Stacked bar charts allow users to see changes in a data sequence and where they occurred. For example, a stacked bar chart depicts the rise or decline in the value of shares in the stock portfolio over time.

The 100 percent stacked bar chart is a variant of the stacked bar chart. Each bar is the same height or length, and the parts are displayed as bar percentages rather than absolute values.

Moreover, the segments of the same color are equal in the stacked bar chart. For instance, the x-axis displays the stacked variable for the Chart’s horizontal variation, and the y-axis (on the bottom) shows the segments. They’re flipped in the column variant of the stacked column chart.

  • It shows data series and Multiple categories in compact space.
  • And shows change over time.
  • It is difficult to tell all but the first series.
  • A stacked bar chart becomes complex when adding categories or series to it.

How do You Make a Stacked Bar Chart in Excel?

When it comes to “how to create a stacked bar chart in Excel,” the basic steps are the same for creating other Excel charts.

For example, see below the basic steps of creating Excel’s clustered and stacked column charts. In this article, for in-detail instructions on creating charts in Excel, see how to create a clustered stacked column chart in Excel.

How to Create a Stacked Bar Chart in Excel:

To see how to make stacked column and bar charts in Excel with examples, First of all;

  • Highlight the data in Excel.
  • After that, click the Chart, and choose your chart type.

Well, you have your Chart now. You will find four basic stacked options here:

  • Stacked column
  • Stacked bar
  • 100% stacked column
  • 100% stacked bar

And each option there has 3D and 3D variations. You have to choose the one that shows your data in the most transparent style.

You can see the example of a stacked bar cylinder, a 100% stacked bar 3D, and a 100% stacked column cone.

How to Create a Stacked Bar Chart in Excel

We provide you with a real-time work execution tool that empowers teams to schedule, track, capture, automate, and report on work through a familiar, easy-to-use interface.

Teams can create stacked bars and stacked column charts that refresh automatically as the underlying data changes.

You no longer need to think about version control or copy the Chart to a presentation, our charts are live, and you can quickly generate one here and save it as widgets on our dashboards.

  • To create a stacked bar/column in Excel, open your dashboard by clicking the +tab. Then select Dashboard/portal.
  • Next, add a chart widget. To do this, click the Add Widget button on the top of your dashboard. (if you create a new dashboard, and there is no widget, the add widget button will display immediately.
  • Then, select Chart in the widget selection form.
  • And now, before selecting your desired Chart, choose your data in the Add Chart Data form that emerges.  Then search for the sheet that has your data.
  • Further, select the sheet that comprises the table that you need to chart on the form’s left side. Then select the cell range on the right side.
  • Now, after selection, click OK. If you want to change the data range later, edit the widget and click the Edit data button to select the data range.
  • After selecting the data range, choose your formatting and desired data type. We recommend you the best chart type; however, you can pick a stacked column or stacked bar chart at this stage.
  • Then, customize the Chart by formatting texts, adding titles, and choosing where to display a legend. And to add labels and change data colors, click series, select data, and color.
  • Finally, click the Done button, and that Chart will add to your dashboard.  

How to Create a Stacked Column Chart in Excel?

Now, after seeing how to make a stacked bar chart in Excel, let’s see how to create a stacked bar column.

So to create a stacked bar column chart, follow the following steps listed above. And then, select the stacked column chart within the Edit chart widget form.

Hints for Creating Stacked Bar Charts

After creating a stacked bar chart, there are many ways to make it easier to read and represent data to look better. Below are a few tips for presenting this bar chart:

  • Cluster data to create visual links between related information.
  • Manipulate the axes to improve readability. You can add or subtract grid lines or change the scale.
  • Trend lines can help draw attention to the progression of results.
  • Titles can clarify the Chart’s message. You can add both a chart title and axis titles.
  • Instructions for several of these steps are here. Also, you can find steps for clustering the data here.
Project Management 150 Templates Bundle Ad

When Should You Use a Stacked Column Chart?

Stacked column charts or stacked bar charts work for the following form of data visualization:

Survey Results: To break down the reaction of demographic groups or other variables.

Nominal Comparisons: To show variations in values, performance, or other numerical values. Also, you can use Stacked bar graphs with dual axes comparisons; more on these kinds of charts are below.

Series-Level Changes: To view changes over time in a recurring series of numbers, such as in factories’ productivity.

Part-to-Whole Comparisons: To highlight subsections that tend to illustrate the distinction, the Chart is attempting to find out.

Distributions: To show where various items are separated.

Series-Level Changes: To view changes over time in a recurring series of numbers, such as in factories’ productivity.

Deviations from the Norm: Show why the findings have changed from time to time.

Comparisons over Time: Demonstrate how different leading factors change week to week or month to month.

Rankings: Show how a set of outcomes add up over time, such as the number of sales that sellers close every month.

When to Use Stack Column Chart

When Not to Use Stacked Bar Charts

Though stacked bar charts are handy in some instances, they are not ideal for any data visualization. When deployed inappropriately, stacked bar charts can be deceptive. In the following cases, stop them:

  • Readers need to compare each segment to the same segment on other bars.
  • The bars have a lot of segments.
  • The data needs to reach a deeper level.

When to Avoid Using a Stacked Column Chart

Although the information that can be conveyed is identical in the stacked column vs. bar style, columns make better sense in some situations:

  • If the number of bars doesn’t surpass the width of the screen or page.
  • If any of the bars have negative values.
  • If it’s an appealing choice by the stacked chart creator.

How do You Create an Overlapping Bar Chart in Excel?

Overlapping bar charts are a variant of the clustered map. And they help compare two similar values, such as expected spending vs. actual expenditure or other comparisons with the goal and outcome. The overlapping bar charts function better for two variables. Here’s how to make one in Excel:

  • Firstly, click on a bar that you want to show in front. Then, right-click and choose Format Data Series.
  • And in Mac Excel 2011, click on Axis, then click on secondary Axis in the same section.
  • In most versions of Excel, to do this, click secondary bars in the Plot Series.
  • This would place the bars on top of each other, creating a single merging bar instead of two unique stacked bars.
  • Now, click on a bar’s end, right-click, then select Format Data Series.
  • This action will make the bar wider from the back to see both bars.

In most versions of Excel, change Gap Width to a smaller number.

And Mac Excel 2011, click on Options and move Gap Width to a smaller number.

How do You create a Clustered Stacked Bar Chart in Excel?

A clustered stacked bar chart incorporates the stacked bar chart’s main elements with the clustered bar chart. Clustering tells users that the data is connected.

An easy way to do this is to insert a blank row between the data sets. For example, to add space to Excel, select the “data column” where space is required, right-click it, and select “Insert.”

How to make a Clustered Stacked Bar Chart in Excel

To make a stacked column chart with two sets of data, take the following steps:

  • Firstly, highlight the data you need to cluster.
  • Then, right-click on the highlighted data and click “Insert.” Start with the Excel stacked bar chart with the blank row if you need more clustering. Then right-click on a bar. And choose “Format data series.”

In most versions of Excel, click on Series Options and turn the Gap Width.

And in Mac Excel 2011, click on options and turn the Gap Width.

What is a Stacked Area Chart?

The area chart illustrates how values shift over time. And these charts intend to represent trends rather than tracking values. Also, you can see this detail in a stacked field chart with more than one group of categories.

Moreover, you can create stacked area charts same as most Excel charts. Just enter and highlight the details first.

In Mac Excel 2011, click on Chart, then Stacked Area.

And in most versions of Excel, click on Chart, then Insert click Stacked Area.

How to Make a Stacked Area Chart in Excel

  • Firstly, enter the data in a worksheet and underline the data.
  • Next, click the “Insert” tab and click “Chart.” Then, click Area and click “Stacked Area.”

Google Music Timeline has a collaborative example of an area chart that illustrates the status of different genres of music.

Other Kinds of  Stacked Charts and When to Use Them

There are many types of charts, each with its advantages and disadvantages. And new chart types are continually being emerging. Here are a few general options and when you might need to use them. First, let’s see some standard solutions and when to use them.

Line graphs are a standard alternative in Excel and are simple to create. They are usually used to compare two data points. Line graphs sometimes include multiple bands, which resemble the same or related data (e.g., rainfall versus water reservoir level) over different periods.

Sparkline is a feature in some Excel types that displays a tiny chart in a row of spreadsheets. And it is valuable for isolating a trend from a large data set.

Kinds and Usage

Most Excel versions: Pick the info, click the Insert button, choose Line, Column, or Win/Loss, and then select where you want the Sparkline to appear on your spreadsheet. The Sparkline is going to fit into the cell.

In Mac Excel 2011: Select the data, click Chart, then under Insert Sparklines, choose Line, Column, or Win/Loss, and select where you want the Sparkline to appear by clicking in the cell.

In Dual-axis charts, merge two linked data sets to show their connection. Dual-axis charts will often have two ways of displaying data, such as a line and bars.

After making a chart, take the following actions:

  • Most versions of Excel click on the Chart to pick it. Then click Design and Change Chart Type. Then, from the All Charts tab, click Combo, and select the option you want.

Mac Excel 2011: In this version of Excel, it is impossible to display data in two different ways, but you can add a second axis. The bar chart bars will cross, so choose a different style, such as a line graph. Select the data series to be plotted on the second Axis, then press the Chart Style Sub-Tab that appears after the chosen Chart.

  • Find the Current Selection box on the ribbon’s left bar, click Format Selection, highlight Axis, and click Secondary Axis. Finally, click OK.
  • Add axis titles to clarify which data goes with each Axis.
Things to Keep in Mind

Note: Just because trends have a relationship, they may not. For example, you could plot the frequency of ice cream use and sunburns; even though they both go up in warm weather, one does not affect the other.

Split-axis bar graphs have an axis in the center rather than the bottom or side. And they help show negative values data. This is because Excel automatically places an axis in the center of the Chart if the data has a negative value.

Project Management 100 Templates Bundle Ad

Make Decisions with a Real-Time Stacked Bar Chart

With the amount of data available today, making sure the correct information is accessible and actionable to all stakeholders can be challenging.

That’s why finding a tool that enables you to manage and visualize your data in real-time is essential to your ability to communicate complex data and make quick decisions.

We provide an initial work management platform that changes the way leaders, teams, and businesses get work done. It is the best way to plan, capture, manage, automate, and report creation.

Our dashboards with charts give you real-time visibility into work progress to make improved decisions and keep teams in the same place at every step. Without technical support, it enables you to highlight the most relevant information to their business.

As a result, you can know your business’s status at a look, gain insights, and speed up your team’s improvement all in one platform.

Top 5 Frequently Asked Questions

How do I create a stacked and unstacked column chart in Excel?

Firstly, pick out your Chart. Then pick either the Tea or Coffee series. After that, select the Design Ribbon and select the Change Chart Type button in the Type group. Now select the stacked column from the Change Chart Type dialogue box.

What is a 100% stacked column chart?

A 100% stacked column chart is an Excel chart style. It illustrates the relative percentage of multiple data series in stacked columns, where the cumulative stacked columns always equal 100%.

Can Excel tabs be stacked?

No, you cannot stack the sheet tabs. However, you will get a pop-up menu of 16 sheets to choose from when you do so. And “more sheets” if required.

Can you combine clustered column and stacked column charts?

There are several ways to combine the stacked and clustered column charts, such as adding the legend entries and organizing the target range with blank rows.

What is a disadvantage of a stacked column chart?

The Stacked Bar column chart’s main disadvantage is that they are harder to understand the more divisions each bar has. Additionally, when you need to relate each segment to another, they’re not aligned on a common baseline.

Subscribe to get the Pack of 50 Templates for FREE