Make a Clustered Stacked Chart in Excel

🔵 Excel doesn't have a Clustered Stacked chart type, but you can build one yourself, with a cluster for each region, and a stack for each year. This short video shows how to set up your Excel data, and build the chart. Then, make a couple of quick formatting changes, to get a clustered stacked chart.

💡 Related Links 💡

🔴 Related Excel Videos 🔴

⏰ Video Timeline ⏰
00:00 Introduction
00:19 Cluster Stack Chart
00:40 Data Layout
01:52 Make the Chart
02:32 Format Chart
02:58 Change Colours

Instructor: Debra Dalgleish, Contextures Inc.
#ContexturesExcelTips

VIDEO TRANSCRIPT
In this workbook, I have: sales data for two years, for four different regions, broken down by season.
I'd like to create a chart like this one, that shows each of the regions, with a stack for each year and the seasons broken down within each stack.
This chart that I want to create is like a combination of a cluster column chart, and a stack column chart.
So we've got clusters for the regions and stacks for the years.
There's nothing built into Excel that will do that so I'm going to copy my data to a blank sheet, then change the way it's arranged.
To start I'll copy the columns with data (I want to leave the original data unchanged)
Copy that, go to a blank sheet and paste it.
To get the data ready for the chart, I'm going to add some blank rows. I want a blank row before the first region and then a blank row after each region.
A quick way to rearrange my data is to put some numbers down column A.
I've got four regions and I need three rows for each region, so I'll select and copy this, then paste it twice.
I want a blank row at the very top, so I'll type a zero here.
I'm going to select those numbers and all the data that I have, and then sort those A to Z, so data A to Z.
Now there's my blank at the top, and each region has its data in one row and then two blank rows after that.
All I have to do now is select the second year of data and drag it down one row.
So, we've got blanks, two rows of data, another blank, and this is how we need it to create our cluster stack column chart.
I'm going to select starting in cell B2 above the region headings here;
select all the headings and down to the last row that I've got numbered there, so I want to include that blank after South,
and then I'm going to insert my chart.
Go to the Insert tab, and I want a column chart, a stacked one.
Click that, and there's the chart.
Because we've got these blank rows, we've got East has its first year of data and then its second year, and then there's a blank where the third row is empty, and the same for each of the other regions.
Now to make these look more clustered, I'll do a little formatting.
Click one of the segments, and on the Format tab, Format Selection, and I want a gap of some little number, so I'll put 20 here and now it's looking more clustered.
There's a bigger space between the regions than there is between the stacks for each region.
The final thing you could do to make this look nicer, is to match up the colours.
Right now, winter for both years is blue, but you could make this a different shade of orange.
So if I go to Format and choose a lighter orange here and do the same for the grey and for the yellow, and now you have a cluster stack chart and you can compare year to year totals for each region

[DWAGON]

Thankyou. I went to two very famous Excel channels. However nobody explained it as nicely and easily as you did.

[mimimilan]

This is the best way to plot this type of chart. So quick and easy, thank you!

[sarveshsharma]

Very short and crisp video with utmost clarity. Thanks a ton for sharing.

[HL-tyje]

This has been very helpful for a complicated report I've been trying to do. Thank you so much!

[AA-dmti]

TY Debra, the best explanation on YT. BY FAR!

[burntsolis]

Thank you, short and sweet without any gimmicks.

[callytmk]

Thank you for such a clear and concise explanation - just what I was looking for!

[SeanLenehan-oiim]

Amazing! Thank you. Exactly what I needed. None of the other explanations delivered this result.

[liliesratshidi]

after 1 hour of trying methods to no avail- thank you so so

[kkcheng]

You saved my presentation! Thank you very much!

[ForgetSometimes]

This was exactly what I needed! Thank you a million!!

[jism]

Thank you so much, so saved my minutes of life. I also added 3 line chart into this charts as well.

[boknows]

this is a very creative solution to make such a complicated chart, thanks a ton for sharing!!

[happykusha]

Hey thanks a lot for sharing. This is just what I was looking for

[hansriensche]

Thanks so much! This was super useful!

[wayneedmondson]

Hi Debra. Very nice technique! Thanks for sharing :)) Thumbs up!!

[CatherineVilson]

Thank you so much but do you know at all how I can center the horizontal labels?

[maestros]

That was really helpful! I wonder how can we center the titles east, west north and south at the end of our chart? I mean, it seems each of them only represent the first column at their point, respectively.

[zsdmrtzmrde]

Hi. Can I make it work with a none four-by-four set of variables? I'm trying to compare two years, month by month, of about twenty inputs. Can't get it to stack side by side

[BUwe]

I have reactions to two statements in four categories (agree, neutreal, disagree, don't know) which add up to 100%, by three groups. When I organize the data as shown here, it stacks by column instead of by row. I reorganized the data to accomodate for this... it suddenly stacks by row leading to the exact same, wrong figure. I guess the auto interpretation by Excel just wins this battle.