Excel does not include a chart type that directly combines clustered and stacked bars. You can make a clear visual workaround by arranging each stack as a category, grouping those categories with multi-level axis labels, and adding blank rows between groups; then insert a standard stacked column or stacked bar chart.
What a clustered stacked chart shows
A stacked chart combines multiple values into one bar: each segment represents a series, and the full bar represents their total. A clustered chart places separate bars side by side for comparison. A clustered stacked chart aims to do both: show several stacked bars side by side within larger groups.
For example, imagine comparing sales by region and product, with each product bar divided into quarterly sales. The regions are the larger groups, the products are the neighboring bars within each region, and the quarters are the colored segments within each bar. This can be useful when readers need to compare both the composition of each product’s sales and the products within a region.
The distinction matters because Excel’s standard chart gallery has stacked charts and clustered charts, but not a native chart type that clusters stacks. The workaround below uses an ordinary stacked chart and a carefully arranged category axis. It is a practical way to make the display, but it is not the same chart structure as a native clustered-stacked chart.
Arrange the source data
Put each bar you want to draw on its own row. Use one column for the higher-level group, another for the item within that group, and one column for each segment of the stack. For example, use the following data to compare products A and B in East and West, with quarterly values making up each product’s bar:
| Region | Product | Q1 | Q2 | Q3 |
|---|---|---|---|---|
| East | Product A | 12 | 8 | 5 |
| East | Product B | 9 | 11 | 6 |
| West | Product A | 7 | 10 | 8 |
| West | Product B | 14 | 6 | 4 |
Keep the region and product names in separate columns rather than combining them into labels such as East – Product A. Excel can use the two columns to create a multi-level category axis. Repeat the region name on each product row; do not merge the region cells. The blank row is an optional visual spacer between regions. Leave its category and value cells empty.
Here, Product A in East has a total of 25 across the three quarters, while Product B has a total of 26. In West, the corresponding totals are 25 and 24. The stacked segments show the quarters, while the adjacent bars and region labels make the product and region groupings visible.
Build the chart in Excel for Microsoft 365
- Select the entire data range, including the headers, both category columns, and any blank spacer row. In the example, that means selecting the cells containing the table.
- On the ribbon, choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Excel creates a vertical stacked chart. If you want horizontal bars, choose Insert > Insert Column or Bar Chart > 2-D Stacked Bar instead.
- Check the chart’s orientation. The quarters should be the stacked series, and the products should appear as separate bars. If Excel has put the products in the legend or the quarters along the category axis, select the chart and choose Chart Design > Switch Row/Column.
- Check the horizontal axis for both region and product labels. Excel normally uses both selected text columns as multi-level category labels. If the labels appear as one level, right-click the horizontal axis, choose Format Axis, and look under Axis Options for Multi-level Category Labels.
- Add a useful title and, if needed, axis titles or data labels. Select the chart and use Chart Design > Add Chart Element to add or adjust these elements.
The ribbon labels above describe Excel for Microsoft 365 on Windows. In Excel for Mac, the chart types and most formatting options are also available from the Insert and Chart Design tabs, though their placement can differ. Excel for the web supports common chart creation and formatting, but some detailed controls and chart-editing options may be limited compared with the desktop app.
Control spacing and make the groups readable
A stacked chart displays one bar for each data row. The blank spacer row creates an empty category position between groups, helping the eye distinguish East from West. It is a visual gap, not a separate chart or a special cluster setting. If the gap is too large or too small, remove the blank row or adjust the chart’s spacing.
To change spacing, right-click one of the bars and choose Format Data Series. In Series Options, adjust Gap Width. A smaller gap width makes the bars wider and closer together; a larger width makes them narrower with more space between them. The best setting depends on the number of categories and the space available for the chart. Try a few values and check that the blank group separators remain easy to see.
Use the same color for a given quarter throughout the chart. This makes the stack segments easier to interpret, and the legend can explain the colors once rather than requiring labels on every segment. A concise chart title should identify the measure and time period, such as Sales by Product and Region, Q1–Q3. If the chart contains many regions or products, consider shortening labels or enlarging the chart instead of shrinking the text until it is difficult to read.
Choose the right chart and interpret it carefully
Choose a stacked column chart when the category names fit comfortably below upright bars and readers should compare values vertically. Choose a stacked bar chart when category labels are long or there are many groups; horizontal labels are often easier to scan. Both choices use the same grouped source-data idea, and both stack the series for each category.
Stacking is most helpful when the overall total and the makeup of that total both matter. In the example, readers can compare total sales per product and see how each quarter contributes. But only the first segment in a standard stacked chart shares a common baseline. Segments higher in the stack start where the segments below them end, so comparing those segments precisely across many bars can be difficult. If the main question is whether Q2 was higher for one product than another, a clustered column chart with separate quarter bars may be easier to read.
There is another common misconception: a chart with grouped axis labels is not necessarily a native clustered-stacked chart. This workaround shows stacks in grouped positions, but Excel still treats the categories as individual rows in a stacked chart. That usually works for a modest number of groups. If the chart must behave as a true combination of clustered stacks, or needs specialized spacing and alignment, consider building it with a more advanced charting method or another visualization tool.
Common problems and how to fix them
- The bars are stacked in the wrong direction. Use Chart Design > Switch Row/Column so each quarter is a series and each product row is a category. Confirm that the legend lists Q1, Q2, and Q3.
- The axis shows only product names. Make sure both the region and product columns were included when selecting the data. Then check the multi-level category label option in Format Axis.
- The group gap does not appear. Check that the spacer row is within the selected chart data and that its value cells are empty. If blank cells are being handled unexpectedly, review the chart’s data source and blank-cell settings. You can also use a clearly labeled grouping layout without spacer rows, accepting that groups will be closer together.
- The chart includes a zero-height bar or unwanted label. Check for zeros, formulas returning zero, or text in the spacer row. A blank separator should have no category text and no values.
- The colors are hard to follow. Keep the series order and color mapping consistent. Avoid assigning a different color to the same quarter in different groups, and use a distinct chart title or legend label if the series names are unclear.
- The total is misleading. Confirm that the segments are parts of the same meaningful whole and use the same units. Stacking unrelated measures, or values with incompatible units, can make the bar height look meaningful when it is not.
Alternatives when the workaround is not enough
If readers need to compare individual series more than totals, use a clustered column or clustered bar chart instead. Each quarter then has its own bar beside the other quarters for a product, making like-for-like comparisons easier, although the total is less immediately visible.
If the priority is showing each product’s share of its group, a 100% stacked chart can emphasize proportions rather than raw totals. This changes the message: bars all become the same full height, so readers cannot compare total sales between products or regions from bar height alone. Use it only when relative composition is the main question.
For a small number of groups, a hand-built chart using helper data or additional chart series can give more control over spacing, but it takes more setup and is easier to break when the source data changes. A PivotChart may help summarize a large dataset, but it does not add a native clustered-stacked chart type. Before choosing a complex workaround, decide what comparison the reader must make and whether a simpler chart communicates it more accurately.
Frequently Asked Questions
Can Excel make a clustered stacked bar chart directly?
No. Excel’s standard chart types do not include a chart type that natively clusters multiple stacked bars. You can approximate the layout with a stacked chart, multi-level category labels, and blank spacer rows, or use a more advanced custom approach.
Should I choose stacked bar or stacked column?
Both stack series within each category. Use a stacked column for vertical bars and a stacked bar for horizontal bars. Horizontal bars are often a better fit when category names are long or there are many categories.
How do I put spaces between the groups?
Add an empty row between groups in the chart’s source data, with the category and series cells left blank. Include that row in the chart selection. It creates an empty category position that can visually separate groups.
Why are the quarters showing as bars instead of stack segments?
The chart may have the data orientation reversed. Select the chart and choose Chart Design > Switch Row/Column. The quarters should be the legend series, while each product row should become a separate stacked bar.
Can I use this method with a PivotChart?
A PivotChart can summarize and chart data, but it does not provide a native clustered-stacked chart type. For the spacer-row workaround, a regular chart based on a prepared summary table is usually more straightforward than relying on PivotChart layout behavior.
Related
- Excel Not Responding? Recover an Unsaved File
- Excel Nested Group Rows: How to Create and Use Them
- Excel Named Ranges: How They Work and When to Use Them
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Chart Add Legend: How to Show and Format It in ExcelTo add a legend to a PivotChart in Excel for Microsoft 365 on Windows, select the chart, then…
- Power Pivot Basics: Data Models, DAX, and PivotTablesPower Pivot is an Excel feature for building data models: it can combine tables from different sources, connect…
- Excel pivot table group by month?Group Excel PivotTable dates into months, keep years distinct, fix grouping errors, and choose a helper-field alternative for…
- Excel Waterfall Chart: How to Set the Last Bar as a TotalTo make the last bar in an Excel waterfall chart show the final total, right-click that bar and…
- How to Create a Trendline in ExcelTo create a trendline in Excel, select a chart, then choose Chart Design > Add Chart Element >…
- Bell Curve Pivot Chart in Excel: Build and Explain ItA PivotChart can summarize your data into bins, but Excel does not provide a one-click bell-curve PivotChart. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.