Make a Clustered Stacked Chart in Excel
✅ Get the sample Excel file to follow along: myctx.link/ChartCS
🔵 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 💡
Clustered Stacked Column Chart ► myctx.link/ChartCS
Clustered Stacked Pivot Chart ► myctx.link/pivotstack
🔴 Related Excel Videos 🔴
Clustered Stacked Pivot Chart ► • Create a Clustered Sta...
Add Pictures to Pie Chart Slices ► • Add Pictures to Excel ...
Compare Years in Excel Pivot Chart ► • Compare Years in Excel...
✅ Excel resources I recommend ► myctx.link/xlpick
⏰ 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.
More Excel Tips and Tutorials: www.contextures.com/tiptech.html
Subscribe to Contextures KZread: kzread.info...
#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 is Debra Dalgleish from Contextures.com.
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
Пікірлер: 71
Thankyou. I went to two very famous Excel channels. However nobody explained it as nicely and easily as you did.
@contextures
7 ай бұрын
You're welcome, and thanks for your lovely comment - I appreciate it!
Amazing! Thank you. Exactly what I needed. None of the other explanations delivered this result.
@contextures
Ай бұрын
You're welcome, Sean, and thanks for letting me know that the video had the explanation you needed!
This has been very helpful for a complicated report I've been trying to do. Thank you so much!
@contextures
5 сағат бұрын
You're welcome, HL, and thanks for letting me know the video helped with your complicated report!
TY Debra, the best explanation on YT. BY FAR!
@contextures
Ай бұрын
Thank you, A A, and I appreciate hearing that!
Very short and crisp video with utmost clarity. Thanks a ton for sharing.
@contextures
2 жыл бұрын
Thank you, Sarvesh, and I'm glad you liked the video! --Debra
after 1 hour of trying methods to no avail- thank you so so much!!!!!!!
@contextures
Жыл бұрын
You're welcome, Lilies, and thanks for your comment!
This was exactly what I needed! Thank you a million!!
@contextures
2 жыл бұрын
You're welcome, and thanks for your comment! I'm glad the video had what you needed
Thank you, short and sweet without any gimmicks.
@contextures
9 ай бұрын
You're welcome, and thanks for your comment! I'm glad you liked it!
Thank you so much, so saved my minutes of life. I also added 3 line chart into this charts as well.
@contextures
Ай бұрын
You're welcome, Fahad, and I'm glad the video helped you save time!
Thank you for such a clear and concise explanation - just what I was looking for!
@contextures
Жыл бұрын
You're welcome, and thanks for letting me know the video had what you were looking for!
This was soooo helpful! Thank you!!!
@contextures
2 жыл бұрын
You're welcome, Olivia, and thanks for letting me know the video was helpful to you!
You saved my presentation! Thank you very much!
@contextures
5 ай бұрын
You're welcome, Katherine, and thanks for letting me know that the video helped with your presentation!
awesome explanation! Thanks!
@contextures
Жыл бұрын
You're welcome, Mahdi, and thanks for your comment!
Hey thanks a lot for sharing. This is just what I was looking for
@contextures
2 жыл бұрын
You're welcome, and thanks for your comment!
Thanks so much! This was super useful!
@contextures
2 жыл бұрын
You're welcome, Hans, and thanks for letting me know the video was useful for you!
this is a very creative solution to make such a complicated chart, thanks a ton for sharing!!
@contextures
Жыл бұрын
You're welcome, Bo, and thanks for your comment!
Special thanks for your explanation
@contextures
5 ай бұрын
You're welcome, Ahmed, and thanks for your comment!
Hi Debra. Very nice technique! Thanks for sharing :)) Thumbs up!!
@contextures
2 жыл бұрын
Thank you, Wayne! I appreciate your support!
Made my day. Thank you
@contextures
2 жыл бұрын
You're welcome, Balchand, and thanks for your comment!
EXCELLENT . MANY THANKS.
@contextures
2 жыл бұрын
You're welcome, Jayaraman, and thanks for your comment!
You're a life saver 🙌🙌
@contextures
Жыл бұрын
Thank you! I'm glad the video helped you
Absolute legend
@contextures
Жыл бұрын
Thanks!
So helpful
@contextures
9 ай бұрын
Thank you, I appreciate it!
Tengkyu.
Muito bom !!!! Me ajudou de mais!
@contextures
2 жыл бұрын
You're welcome, Bruno, and thanks for letting me know that the video helped you!
Muchas gracias
@contextures
Жыл бұрын
You're welcome, Odette, and thanks for your comment!
Thanks
@contextures
2 жыл бұрын
Thanks for watching, and leaving a comment!
ganhou mais um inscrito !!!
@contextures
2 жыл бұрын
Thanks, Bruno, and I appreciate it!
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
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.
@contextures
2 жыл бұрын
Thanks, Kerem! To move the labels over a bit, you could add space characters at the start of the region names, in the chart data. OR, insert a blank row between the 2 rows of data for each region, them move the label to that blank row. Then, to add more space between the regions, add another blank row or two between them in the data
@maestros1327
2 жыл бұрын
@@contextures Thank you so much!
@contextures
2 жыл бұрын
@@maestros1327 You're welcome!
Thank you very much... I m able find out where i am making mistake... 🌹🌹🌹1000 roses for u...
@contextures
2 жыл бұрын
You're welcome, and I'm glad the video helped you find the mistake. And thanks for the roses!
is it necessary to do the arrangement like this? Won't it work if we leave one space after every row?
@contextures
Жыл бұрын
Make a copy of your data, and try that arrangement
Thank you for sharing. It's exactly what I need. However, I have a question, hope that you can reply. I want to create a clusterd stacked chart same as yours, but my horizontal axis is number (in Date format), and this method doesn't work for me. My alternative fields are as follows: + "region" => "month" (instead of "East-West-North-South", I change to Jan-2022, Feb-2022, Mar-2022, Apr-2022) + "year & season" => "Work & Plan/Actual" (instead of "2020 Win - 2020 Spr. - 2021 Win - 2021 Spr", I change to "Work A_Plan, Work B_Plan, Work A_Actual, Work B_Actual" The output chart only shows "Plan" value, no "Actual" at all. The problem can be fixed only if I change the format of Jan-2022, Feb-2022, Mar-2022, Apr-2022 to "Text". Do you know why?? Thank you.
@contextures
2 жыл бұрын
You're welcome, and thanks for your question! If you haven't tried this already, right-click on the Months axis, and click Format Axis. For the Axis Type, select "Text Axis"
I've tested that, and it's not working if you changed the Horizontal Axis into Date format. They would return into a single stacked charts
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.
@contextures
Жыл бұрын
To get columns per group per statement, enter data in this setup: B1:I1 (headings "statement-response") A-Ag, A-Ne, A-DA, A-DN, B-Ag, B-Ne, B-DA, B-DN A3:I4 (group 1 response counts) G1 20 25 25 30 30 35 30 5 A6:I7 (group 2) G2 15 40 20 25 25 45 25 5 A9:I10 (group 3) G3 15 30 40 15 15 40 40 5 Select A1:I11, insert stacked column chart
@BUwe
Жыл бұрын
@@contextures Thanks so much for taking the time to help. I actually figured out that there was no need for dividing the responses in different lines in my case. I used the 'Clustered Stacked Bar Chart in Excel' guide by @Pandi Mengri instead.
@contextures
Жыл бұрын
@@BUwe You're welcome, and thanks for letting me know you found a solution!