Make a Clustered Stacked Chart in Excel
ฝัง
- เผยแพร่เมื่อ 24 ก.ค. 2024
- ✅ 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 TH-cam: th-cam.com/users/contextu...
#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
This has been very helpful for a complicated report I've been trying to do. Thank you so much!
You're welcome, HL, and thanks for letting me know the video helped with your complicated report!
Thankyou. I went to two very famous Excel channels. However nobody explained it as nicely and easily as you did.
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.
You're welcome, Sean, and thanks for letting me know that the video had the explanation you needed!
Very short and crisp video with utmost clarity. Thanks a ton for sharing.
Thank you, Sarvesh, and I'm glad you liked the video! --Debra
Thank you for such a clear and concise explanation - just what I was looking for!
You're welcome, and thanks for letting me know the video had what you were looking for!
Thank you, short and sweet without any gimmicks.
You're welcome, and thanks for your comment! I'm glad you liked it!
this is a very creative solution to make such a complicated chart, thanks a ton for sharing!!
You're welcome, Bo, and thanks for your comment!
This was exactly what I needed! Thank you a million!!
You're welcome, and thanks for your comment! I'm glad the video had what you needed
Thank you so much, so saved my minutes of life. I also added 3 line chart into this charts as well.
You're welcome, Fahad, and I'm glad the video helped you save time!
TY Debra, the best explanation on YT. BY FAR!
Thank you, A A, and I appreciate hearing that!
Hey thanks a lot for sharing. This is just what I was looking for
You're welcome, and thanks for your comment!
after 1 hour of trying methods to no avail- thank you so so much!!!!!!!
You're welcome, Lilies, and thanks for your comment!
You saved my presentation! Thank you very much!
You're welcome, Katherine, and thanks for letting me know that the video helped with your presentation!
This was soooo helpful! Thank you!!!
You're welcome, Olivia, and thanks for letting me know the video was helpful to you!
Hi Debra. Very nice technique! Thanks for sharing :)) Thumbs up!!
Thank you, Wayne! I appreciate your support!
Thanks so much! This was super useful!
You're welcome, Hans, and thanks for letting me know the video was useful for you!
Special thanks for your explanation
You're welcome, Ahmed, and thanks for your comment!
awesome explanation! Thanks!
You're welcome, Mahdi, and thanks for your comment!
Made my day. Thank you
You're welcome, Balchand, and thanks for your comment!
EXCELLENT . MANY THANKS.
You're welcome, Jayaraman, and thanks for your comment!
Tengkyu.
You're a life saver 🙌🙌
Thank you! I'm glad the video helped you
So helpful
Thank you, I appreciate it!
Absolute legend
Thanks!
Muito bom !!!! Me ajudou de mais!
You're welcome, Bruno, and thanks for letting me know that the video helped you!
Thanks
Thanks for watching, and leaving a comment!
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.
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
@@contextures Thank you so much!
@@maestros1327 You're welcome!
Muchas gracias
You're welcome, Odette, and thanks for your comment!
ganhou mais um inscrito !!!
Thanks, Bruno, and I appreciate it!
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
Thank you very much... I m able find out where i am making mistake... 🌹🌹🌹1000 roses for u...
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?
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.
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 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.
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
@@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.
@@BUwe You're welcome, and thanks for letting me know you found a solution!
It didn't work for me