Excel does not offer a single chart type that directly combines clustered and stacked bars. In Microsoft 365 for Windows, the practical solution is to arrange the source data so each cluster becomes a set of adjacent category rows, then insert a 2-D Stacked Bar or Stacked Column chart; blank spacer rows can separate the main categories.
What a stacked-and-clustered chart shows
A stacked bar displays the parts of one total in a single bar: each series contributes a segment, and the segments share an axis. A clustered bar instead places separate bars side by side so you can compare series independently. A combined stacked-and-clustered chart aims to show multiple stacks side by side for each main category. For example, for each quarter you might want one stack for Actual and another for Plan, with each stack divided into product types.
This chart is useful when the audience needs to compare both the total size of each group and the composition of that total. It can also get crowded quickly: every additional stack and segment adds visual work. Keep the number of main categories and component series modest, and make sure the component colors mean the same thing in every stack.
Why Excel does not make this a one-click chart type
Excel’s standard chart types include stacked and clustered bars, but a regular chart cannot simply apply both arrangements at once to the same series. The Combo chart command is not a direct answer: it assigns different chart types to different series, commonly with one series on a secondary axis. That can overlay or separate series, but it does not automatically make several complete stacks sit side by side for each category.
Instead, the workaround makes each desired stack a separate category position. The data still uses the component values as chart series, so Excel stacks those components. A second category-label column identifies which main category and group each stack belongs to. This creates the clustered appearance through the order and labels of the categories rather than through a special chart type.
Arrange the source data
Put the main category and the group name in the first two columns. Put each component that should form a segment in its own numeric column. Each data row represents one stack. For example, a small report might be arranged like this:
| Quarter | Plan type | Product A | Product B | Product C |
|---|---|---|---|---|
| Q1 | Actual | 40 | 25 | 15 |
| Q1 | Plan | 35 | 30 | 20 |
| Q2 | Actual | 45 | 20 | 18 |
| Q2 | Plan | 42 | 24 | 22 |
The first two rows create the two Q1 stacks; the next two create the Q2 stacks. The empty row between quarters is optional and serves as a visual spacer. Leave the numeric cells in that row genuinely empty. A blank row between every pair of stacks would separate the members of a cluster, so place spacers only where you want a larger break.
Create the chart in Excel for Windows
- Select the complete range, including both label columns, the component headings, all data rows, and any spacer rows.
- Choose Insert > Insert Column or Bar Chart, then select 2-D Stacked Column for vertical bars or 2-D Stacked Bar for horizontal bars.
- Check that Excel treats Product A, Product B, and Product C as the data series and treats the first two columns as category labels. If it has arranged the data the other way around, select the chart and choose Chart Design > Switch Row/Column.
- If the chart does not show both label levels, right-click the category axis, choose Format Axis, and check the axis label options for multi-level category labels. The main category and the group label should identify each stack.
- Use Chart Design > Add Chart Element to add a chart title, axis titles, data labels, or a legend as needed.
The chart type remains stacked: the component series form the segments within each row’s bar. The adjacent rows create the cluster. With horizontal bars, categories run down the vertical axis; with columns, categories run along the horizontal axis. Choose the orientation that makes the labels and comparisons easiest to read.
Control spacing and make the groups legible
To change the distance between bars, select a bar and open Format Data Series. Under Series Options, adjust Gap Width. A smaller gap makes the stacks appear more substantial; a larger gap gives the category groups more breathing room. Because gap width affects the category positions broadly, it will not independently set one spacing for stacks within a quarter and another spacing between quarters. Empty spacer rows provide the extra separation between main categories.
Use consistent colors for component series. For instance, Product A should have the same color in the Actual and Plan stacks, while Plan and Actual can be distinguished by their category labels. Do not rely on color alone to communicate the group: labels, a subtitle, or annotations can make the meaning clear for readers with color-vision differences or when the chart is printed in grayscale.
For a meaningful stacked comparison, all component values should normally use the same unit and represent parts that can reasonably be added. If the components have different units, such as dollars and percentages, stacking them produces a misleading total. If the main goal is to compare component values rather than totals, a clustered chart or separate small charts may be clearer.
Common problems and fixes
- Excel stacks the categories in the wrong direction: use Chart Design > Switch Row/Column, then confirm the component headings appear in the legend and the category labels identify each bar.
- The group labels do not appear: make sure both label columns were included in the selected range. Right-click the chart and choose Select Data to inspect the chart data range and horizontal category axis labels.
- A spacer row appears as a zero bar: verify that its component cells are empty, not filled with zeros or text such as a dash. If Excel still displays an unwanted mark, remove the spacer row and rely on a wider Gap Width, or use a deliberate category label and check the chart’s handling of empty cells through Select Data > Hidden and Empty Cells.
- The chart looks too busy: reduce the number of component series, show fewer category labels, or split the chart into separate panels. Too many thin segments make it difficult to compare either totals or composition.
- The total is difficult to compare: stacked segments other than the bottom segment do not share a common baseline, so their lengths are harder to compare precisely. Add total labels if totals matter, or choose a clustered chart when comparing individual components is more important.
When to use a different approach
The category-row method is usually the clearest way to create adjacent stacks without distorting the values. A secondary-axis combination chart is sometimes used as a more involved workaround, but the series and axes must be arranged carefully. A secondary axis can have a different scale, making visual comparisons misleading, and the chart may be harder to maintain when the data changes. Use it only when the separate axis is justified and clearly labeled, not simply to force two chart types together.
For a dashboard, another option is to create one stacked chart for each group, align the charts, and give them matching scales and colors. This takes more formatting and does not behave as one chart, but it can improve readability when there are many groups. If readers mainly need exact comparisons between Actual and Plan for each component, a clustered bar chart may communicate that more directly than stacked bars.
Excel for Mac and Excel for the web
Excel for Mac also supports stacked bar and column charts and can use the same source-data layout. Ribbon wording and the location of chart-formatting controls can differ slightly from Windows, so look for the Insert chart controls and the Format Data Series pane if a named command is not in the same place. On Mac, many shortcuts differ; for example, copying is Command+C rather than Ctrl+C.
Excel for the web can create and edit common charts, but some detailed formatting controls may be more limited or arranged differently than in the desktop app. If you need to control empty-cell behavior, axis labels, or precise spacing and cannot find the relevant setting in the web interface, open the workbook in desktop Excel. The underlying category-and-component layout remains the same.
Frequently Asked Questions
Can Excel make a clustered stacked bar chart automatically?
No. Excel does not provide a standard chart type that automatically clusters multiple stacks within each category. Use one row per stack, component columns for the stacked segments, and category labels that identify the main category and group.
Should I choose Stacked Bar or Stacked Column?
Choose Stacked Bar for horizontal bars, especially when category names are long. Choose Stacked Column for vertical bars when time periods or other short labels fit well along the horizontal axis. Both use the same underlying arrangement.
Do I need to use a secondary axis?
Not for the category-row workaround. A single stacked chart is sufficient because each row represents one stack. Secondary axes are an optional, more complex charting technique and can make comparisons confusing if the scales differ.
How do I add more than two stacks to each category?
Add another data row for each additional group under each main category, using the same component columns. Keep the group name in the second label column and insert a blank spacer row only between main categories if you want a visible break.
Can the chart show totals as well as the segments?
Yes. You can add data labels or place total values in a separate label series, but a total series should not be stacked as another component because that would inflate the bar. For a straightforward chart, add total labels manually or use a separate, clearly scaled chart for totals.
Related
- Excel Conditional Formatting Number Formats Explained
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Gantt Chart Scrolling Increment and Setup GuideIn Excel, a Gantt chart does not have a built-in scrolling-increment setting. To move a timeline by a…
- Conditional Progress Circle Chart in Excel: Build OneYes. In Excel for Microsoft 365 on Windows, you can make a conditional progress circle with a doughnut…
- Create a Line Chart in Excel with Two Data SetsTo create a line chart from two data sets in Excel for Microsoft 365 on Windows, arrange the…
- Excel Pivot Table Percentage of Total: How to Show ItTo show Excel PivotTable values as a percentage, right-click a value in the PivotTable and choose Show Values…
- Excel Sparklines Line Chart: How to Add and Format OneIn Excel for Microsoft 365 on Windows, select the data, choose Insert > Sparklines > Line, and specify…
- Excel Pie Chart From a Single Column: How It WorksIn Excel for Microsoft 365 on Windows, select a column of numeric values, then choose Insert > Insert…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.