How to Create a Clustered Stacked Bar Chart in Excel

A clustered stacked bar chart is a powerful way to compare categories and their sub-components side-by-side, but Excel doesn't offer it as a default chart type. This can be frustrating when you need to show, for example, this year's sales breakdown by product versus last year's. This tutorial will walk you through a clever workaround to build exactly the clustered stacked bar chart you need, step-by-step.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Exactly is a Clustered Stacked Bar Chart?

Think of it as a hybrid chart that combines the best features of two standard charts:

• Like a clustered bar chart, it places bars for different main categories side-by-side for easy comparison. For example, you might cluster bars for "2023 Performance" right next to "2024 Performance."
• Like a stacked bar chart, each of those individual bars is segmented to show the makeup of its total value. For example, the "2024 Performance" bar could be broken down into segments representing "Online Sales" and "In-Store Sales."

The result is a chart that lets you answer complex questions at a glance. You can compare the total performance of the main clusters (Is 2024 bigger than 2023?) while also understanding what's driving that performance (Was the growth in 2024 due to online sales or in-store?).

This is extremely useful for things like:

• Comparing current vs. previous year sales, broken down by sales channel.
• Showing marketing campaign performance for Q1 vs. Q2, broken down by traffic source (e.g., Social, SEO, PPC).
• Visualizing product adoption across different customer segments for two different launch periods.

Since building this requires a specific trick, let’s get started.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Structure Your Data Correctly

The entire secret to creating this chart in Excel lies in how you arrange your source data. The chart may look complex, but the data preparation is what makes it all possible. Without this specific structure, Excel won't know how to create the visual separation between your clusters.

Imagine we want to compare laptop sales for two years, 2023 and 2024, broken down by their operating system (OS): Windows and macOS.

Your initial data might look something like this:

Standard Data Layout (The WRONG way for this chart)

To make the chart work, you need to rearrange it and, critically, add blank cells to act as spacers. You want your primary categories (Laptops, Phones, Tablets) on the vertical axis and the sub-categories (the different years and their segments) as columns.

Here's how to structure it:

The Correct Data Structure:

First, set up your primary categories in the first column. Then, create column headers for your first cluster's segments ("2023 - Windows" and "2023 - macOS"). Now, for the key step: add a completely blank column right after the data for your first cluster. This column acts as an invisible "spacer" that will create the cluster effect. Finally, add the column headers for your second cluster ("2024 - Windows" and "2024 - macOS").

Your correctly structured data should look like this:

Make sure the header for the spacer column is also empty. This blank series is what Excel will use to create the visual gap between the 2023 and 2024 bars.

Step 2: Create an Initial Stacked Bar Chart

Now that your data is properly formatted, creating the base chart is simple. Excel won't have a "Clustered Stacked" option, so we start with a standard "Stacked Bar" and modify it.

1. Click and drag to select your entire data table, including the blank spacer column and all the headers.
2. Navigate to the Insert tab in the Excel ribbon.
3. In the Charts group, click the Insert Column or Bar Chart dropdown menu.
4. From the 2-D Bar section, select the Stacked Bar chart option.

Excel will instantly generate a chart. Don't worry, it won't look right at all. You’ll see stacked bars with large white gaps in the middle of each category. This is exactly what we expect at this stage. That ugly gap is our spacer column at work, and we are about to transform it.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 3: Adjust the Series Overlap and Gap Width

This is where the magic happens. We're going to use two settings - Series Overlap and Gap Width - to transform our gappy stacked chart into a perfectly formatted clustered stacked chart.

1. Right-click on any of the colored data bars in your chart.
2. From the menu that appears, click Format Data Series...
3. A Format Data Series pane will appear on the right side of your Excel window.
4. Make sure you’re in the Series Options tab (the icon looks like three little bars).
5. Find the slider labeled Series Overlap. Drag the slider all the way to the right or type in 100%.

As soon as you set the overlap to 100%, you'll see the chart change dramatically. The colored bars will now "stack" on top of each other, forming a solid bar. The bars from your blank spacer column are still there, they are just invisible and now fully covered by the colored bars, creating the clustered look you wanted.

Next, you’ll want to adjust the spacing between the categories themselves (e.g., the space between "Laptops" and "Phones").

1. In that same Format Data Series pane, find the slider for Gap Width.
2. This setting controls the amount of white space between the full "Laptop" cluster, the "Phone" cluster, etc. The default is often too wide.
3. Adjust this value to a number that looks visually appealing. Something between 50% and 100% usually works well. A smaller number means less space and chunkier bars.

Step 4: Clean Up and Format Your Chart

You have a functional chart, but now it's time to make it professional and easy to read.

Fix the Legend

Your chart's legend probably has an entry for your blank spacer column, which is confusing. To remove it:

1. Click once on the chart's legend to select it.
2. Click a second time specifically on the legend key for the blank series (it may not have any text next to it).
3. Press the Delete key on your keyboard. The phantom entry will disappear.

Add Chart Elements and a Title

A chart without labels is just a picture. You need to add context.

• Give Your Chart a Title: Click on "Chart Title" at the top and type in something descriptive, like "Sales Performance by OS: 2023 vs. 2024."
• Add Axis Titles: Click the plus (+) icon on the top right of your chart and check the box for Axis Titles. Label the horizontal axis (e.g., "Sales in Units") and the vertical axis (if it needs clarification).

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Improve Your Color Scheme

Good coloring can make your chart much easier to interpret. A great strategy is to use related shades for related data.

• For the 2023 data, you could use a light blue for Windows and a dark blue for macOS.
• For the 2024 data, you could use a light green for Windows and a dark green for macOS.

This scheme assigns a "parent" color for each year, making the year-over-year comparison very clear. To change the color of a single series, click once on any bar to select all bars in the chart. Then, click a second time on just one bar of a specific series (e.g., the "2023 - Windows" bars). Now, only those bars are selected. Right-click, select Fill, and choose a new color.

Final Thoughts

You've now learned the clever workaround to build a clustered stacked bar chart in Excel. By strategically preparing your data with a spacer column and then manipulating the series overlap, you can create a powerful, multi-layered visualization that Excel doesn't provide out of the box. It’s a great skill to have for creating more insightful reports.

Manually structuring data and wrestling with chart settings in spreadsheets is powerful, but it's often time-consuming. We built Graphed because we believe getting insights shouldn't require so many manual steps. My process now involves connecting directly to my data sources just once. From there, I can create complex, interactive dashboards and charts just by describing what I need in plain English - no spacer columns or formatting tricks necessary. This turns hours of spreadsheet work into a 30-second task.