NEWExcel AI Agents: use Copilot and Claude in Excel, and prove every number is right. Excel AI Agents course. See the course →

How to Create a Clustered Stacked Column Chart in Excel

Excel gives you clustered columns. Excel gives you stacked columns. What it won’t give you is both at once — which is exactly the chart you need when you’re comparing categories that each break down into parts.

There’s a workaround, and it takes about two minutes. It isn’t a hidden chart type; it’s how you lay out your data before you insert the chart.

Watch: a clustered stacked column chart in 2 minutes

Prefer reading? The same steps are written out below the video.

Step-by-step

1. Lay out your data with two label columns

This is the step that makes the chart work. The example has two courses. Each has an Income row and an Expenses row, broken down into four categories: E-learning, YouTube, Webinars and Podcast.

  • Column A holds the course name (Course 1, Course 2).
  • Column B holds Income and Expenses. Together, columns A and B become a two-level axis.
  • Columns C to F hold the four categories. They become the chart legend.
E-learningYouTubeWebinarsPodcast
Course 1Income…………
Expenses…………
Course 2Income…………
Expenses…………

2. Insert a stacked column chart

Select the whole range (A2:F6 in the example), go to Insert, click the Column Chart button and choose Stacked Column. It won’t look right yet. That’s expected.

3. Switch rows and columns

Click the chart, open the Chart Design tab (called Design in older versions of Excel) and click Switch Row/Column. Excel now groups the columns by course: an Income column and an Expenses column for each course, each stacked by category.

To check the result, select C3:F3 (the income of Course 1) and look at the Sum in the status bar. It matches the height of Course 1’s Income column.

4. Close the gaps

Right-click any of the series, choose Format Data Series and set Gap Width to 100%.

5. Finish the chart

Give the chart a title and move the legend to the right.

Tip: it works for bar charts too

For a horizontal version, go to Chart Design > Change Chart Type and pick Stacked Bar.

Build more charts like this

My course Excel Charts, Infographics & Data Visualisation walks through 50+ advanced, dynamic charts in 90 lessons.

WATCHING ON YOUTUBE?

The full courses go further

11 step-by-step courses on Excel, Power BI, Python and AI, all on Udemy with lifetime access.