We're sorry, the browser you are using is limiting the functionality of this website. To get the best experience, we recommend that you switch to an up-to-date fully supported web browser. If you feel that you have received this message in error, please contact us.

Multipart article

How to Create a Stacked Bar Chart in Excel

Try Smartsheet for Free

In the era of big data, a chart can help readers visualize the key part of a large data set. Whether you’re looking at a pie chart showing the percentages of people who chose each of a particular set of options or a line graph that displays the number of widgets produced each hour, seeing data in a visual format is easier than picking it out of a spreadsheet.

A bar chart is a well-established format for visualizing large chunks of data; a stacked bar is a variation of the bar chart and is good for comparing sub-segments of results or for seeing which subtotal contributes the most to an overall sum. This tutorial will show you what data makes the most sense to display in a stacked bar chart and how to create one in Excel. 

We’ll also show you how to create a stacked bar chart and stacked column chart in Smartsheet, an easy-to-use work management tool that enables real-time visibility into work progress so you can make better data-driven decisions.

 

How would you like to create your stacked bar chart?
 

What Is a Stacked Bar Chart?

A stacked bar chart is a variant of the bar chart. A standard bar chart compares individual data points with each other. In a stacked bar chart, parts of the data are adjacent (in the case of horizontal bars) or stacked (in the case of vertical bars, aka columns); each bar displays a total amount, broken down into sub-amounts.

Equivalent subsections are the same color in each bar. This formatting makes it easy to compare both the whole picture and the components of each bar, as seen in the (fabricated) data below.

Stacked bar charts allow users to see changes in a series of data and where they occurred. For example, the increases or decreases of the value of investments in a stock portfolio over time is often represented as a stacked bar chart.

A variation of the stacked bar chart is the 100% stacked bar chart. In this form, each bar is the same height or length, and the sections are shown as percentages of the bar rather than as absolute values.

How to Read a Stacked Bar Chart

In a stacked bar chart, segments of the same color are comparable. With a horizontal variation of the chart, the x-axis (on the left side) shows the stacked variable, and the y-axis (on the bottom) shows the segments. In the column version of a chart, they are switched.

How Do You Make a Stacked Bar Chart in Excel?

The basic steps for stacked charts are the same as for creating other charts in Excel. Basic steps are below. For in-depth instructions on creating charts in Excel, see How to Make Bar Chart in Excel section of How to Make Bar Chart in Excel article.

How to Create a Stacked Bar Chart in Excel

  1. Highlight the data.

    Excel Bar Charts Data Select Stacked Chart
  2. Click Chart, then choose your chart type.

       

Voila, you have your chart:

You have four basic stacked options:

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

Each option has 2D and 3D variations. Try them out and choose the one that presents your data in the clearest style.

Below are examples, respectively, of a stacked bar cylinder, a 100% stacked column cone, and a 100% stacked bar 3D, all with the same data.





 

How to Create a Stacked Bar Chart in Smartsheet

Smartsheet is a real-time work execution platform that empowers teams to plan, track, capture, automate, and report on work all within a familiar, easy-to-use interface. Teams can create stacked bar and stacked column charts that automatically update as underlying data changes.

See a Smartsheet Stacked Bar Chart in Action

You no longer need to worry about version control or copying your chart into a presentation, Smartsheet charts are live and can be created quickly using Smartsheet data and then stored as widgets on Smartsheet dashboards. (To learn more about Smartsheet dashboards, watch this two-minute video or read this article.) To create a stacked bar chart in Smartsheet:

  1. Open your dashboard or create a new one by clicking the + tab and selecting Dashboard/Portal.

 

Create New Dashboard
  1. Next, add your chart widget. Click the Add Widget button at the top of the dashboard. (If you’ve created a new dashboard that contains no widgets, the Add Widget button is immediately available.)

 

 

Add Widget
  1. In the widget selection form, select Chart.

 

Select Chart Widget
  1. Before you can select your desired chart, you must first select your data in the Add Chart Data form that appears. Search for the sheet that contains your data.

 

Select Sheet or Report
  1. On the left side of the form, select the sheet that contains the table that you want to chart, then select the range of cells on the right side.

 

Select Data
  1. Once you have your data selected, click OK.

**TIP: If you need to change your data range later, edit the widget and click Edit Data to reselect the data range.

  1. After selecting your data range, you’ll choose your desired chart type and formatting. Smartsheet will recommend the best chart type for your data; however, you can select a stacked bar or stacked column chart at this time.

 

Select Type of Chart
  1. Customize your chart by adding titles, formatting text, and choosing where or if to display a legend. To change colors or add data labels, click Series, select the data, and choose the color.

 

Change Series Details
  1. Once you finish formatting your stacked bar chart, simply click the Done button and it will be added to your dashboard.

How to Create a Stacked Column Chart in Smartsheet

To create a stacked column chart, you will follow the steps listed above and simply select the stacked column chart type within the Edit Chart Widget form.

 

To learn more about how to create and format Smartsheet Charts, check out this one-minute video:

 

When Should You Use a Stacked Column Chart?

Stacked bar charts (also called stacked column charts) work for the following types of data visualization:

  • Nominal Comparisons: To display the differences in values, production, or other numerical values. Stacked bar graphs can also be used for dual-axis comparisons; more on these types of charts is below.
  • Deviations from the Norm: To show why results changed from one time period to the next.
  • Part-to-Whole Comparisons: To highlight subsections that help clarify the difference the chart is trying to point out.

 

Excel Stacked Bar Charts Part to Hole
  • Distributions: To show where different items are apportioned.
  • Comparisons over Time: To demonstrate how different contributing factors change week to week or month to month.
  • Survey Results: To break down responses by demographic groups or other factors.

  • Series-Level Changes: To view changes over time in a recurring set of figures, such as in factory production.
  • Rankings: To show how a group of results stack up over time, such as the number of deals that salespeople close each month.

When Not to Use Stacked Bar Charts

While stacked bar charts are handy in many situations, they are not perfect for every data visualization. When deployed incorrectly, stacked bar charts can be misleading. Avoid them in the following situations:

  • The bars have a lot of segments.
  • Readers need to compare each segment to the same segment on other bars.
  • The data needs to be compared at a deep level.

Hints for Creating Stacked Bar Charts

After a chart is created, you have many ways to make it easier to read, communicate the presented data, or look better. Here are a few tips to enhance your bar charts:

  • 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 most of these steps can be found here; you can also find steps for clustering data.

When to Avoid Using a Stacked Column Chart

Though the information that can be communicated is similar in the stacked column vs. bar layout, columns make more sense in certain situations:

  • If any of the bars have negative values.
  • If the number of bars doesn’t go beyond the width of the screen or page.
  • If it’s an aesthetic decision by the chart creator.

How Do You Create an Overlapping Bar Chart in Excel?

Overlapping bar charts are a variation of the clustered chart, and they’re useful for comparing two related values, such as planned expenses vs. actual expenses or any comparison with a target and a result. An overlapping bar chart works best with two variables. Here’s how to create one in Excel:

  1. Use the steps above to create a Clustered Bar Chart.
  2. Click on a bar that you want to appear in front. Right-click and select Format Data Series.

Most versions of Excel: In the Plot Series On area, click Secondary Axis.

Mac Excel 2011: Click on Axis, and click Secondary Axis in the Plot Series On area.

This action will place the bars on top of each other, creating a single overlapping bar instead of two separate stacked bars.

3. Click on the end of a bar that sticks out, and right-click and select Format Data Series.

Most versions of Excel: Change Gap Width to a smaller number (40 percent was used for this example).

Mac Excel 2011: Click on Options and change Gap Width to a smaller number (40 percent was used for this example).

This will make the bar in the back wider, so users can see both.

As you can see in the chart, those cats are blowing through their allotted budget.

How Do You Create a Clustered Stacked Bar Chart in Excel?

A clustered stacked bar chart combines the key features of the stacked bar chart and the clustered bar chart. Clustering shows users that the data is related.

A simple way to do this is to put a blank row between the sets of data. To add space in Excel, select the column of data after where the space is needed, right-click, and select Insert.

How to Make a Clustered Stacked Bar Chart in Excel

  1. Highlight the data you want to cluster.
  2. Right-click on the highlighted content and click Insert.

A blank column is inserted to the left of the selected column.

If more clustering is desired, starting with the stacked bar chart with the blank row, right-click on a bar and choose Format Data Series.

Most versions of Excel: Click on Series Options and change the Gap Width (this example uses 20 percent).

Mac Excel 2011: Click on Options and change the Gap Width (this example uses 20 percent).

 

What Is a Stacked Area Chart?

An area chart shows how values change over time and is meant for displaying trends rather than tracking values. A stacked area chart can show this information for more than one set of categories.

Stacked area charts are created like other charts in Excel. First, enter and highlight the data.

Most versions of Excel: Click on Chart. Click Insert, then click Stacked Area.

Mac Excel 2011: Click on Chart, then Stacked Area.

How to Make a Stacked Area Chart in Excel

  1. Enter the data in a worksheet and highlight the data.
  2. Click the Insert tab and click Chart. Click Area and click Stacked Area.

Google Music Timeline has an interactive example of an area chart that shows the popularity of different genres of music.

Other Kinds of Charts and When to Use Them

There are many types of charts, each with their own set of strengths and weaknesses. And new chart types are always being developed. Here are a few popular options and when you might want to use them.

Sparkline is a feature in some versions of Excel that allows a small chart to be displayed in a spreadsheet row. It’s valuable for isolating a trend from a large set of data.
 

Most versions of Excel: Select the data, click the Insert tab, then choose Line, Column, or Win/Loss, and select where you want the sparkline to appear on your spreadsheet. The sparkline will fit into the cell.

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

Line graphs are a standard option in Excel, and they’re easy to create. They’re generally used to compare two data points. Line graphs sometimes include multiple bands, which allows comparison of the same or related data (e.g., rainfall versus reservoir water level) over different time periods.

Dual-axis charts combine two related sets of data to demonstrate their connection. Dual-axis charts will often have two ways of showing data, such as a line and bars. They can also be called a secondary axis chart or a combined chart.

After creating a chart, take the following steps:

Most versions of Excel: Click in the chart to select it. Click Design and click Change Chart Type. From the All Charts tab, click Combo, and choose the option you want (e.g., Clustered Column-Line).

Mac Excel 2011: In this version of Excel, showing data in two different ways is not available, but you can add a second axis. The bars in a bar chart will end up overlapping, so use another format, such as a line graph. Select the data series to plot on the second axis, then click the Chart Layout sub-tab that appears after the chart is selected.

On the left side of the ribbon, find the Current Selection box, click Format Selection, then highlight Axis and click Secondary Axis. Click the OK button.


 

To clarify which data goes with each axis, add axis titles.

A reminder: Just because trends seem to have a relationship, they may not. The old saying “correlation does not imply causation” is an important caveat. You could plot the prevalence of ice cream consumption and sunburns; even though they both go up in hot weather, one does not cause the other.

Split-axis bar graphs have an axis in the middle rather than on the side or the bottom, and they’re handy for showing data that have negative values. Excel automatically places an axis in the middle of the chart if the data selected has a negative value.

Make Better Decisions with a Real-Time Stacked Bar Chart in Smartsheet

With the amount of data available today, making sure the right 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.

Smartsheet is an enterprise work management platform that fundamentally changes the way teams, leaders, and businesses get work done. Over 74,000 brands and millions of information workers trust Smartsheet as the best way to plan, capture, manage, automate, and report on work.

Smartsheet dashboards with charts give you real-time visibility into work progress to make better decisions and keep your teams on the same page every step of the way. The configurable, widget-driven dashboards enable users to highlight the information that’s most relevant to their business - without the need for technical support. Know the status of your business at a glance, gain insights, and accelerate your team’s innovation all in one platform.

 


Discover how charts in Smartsheet will help your team make better-informed decisions, fast.

Learn More About Smartsheet Dashboards with Charts

Discover why over 74,000 brands trust Smartsheet to get work done.

Try Smartsheet for Free