Excel Column Chart - Stacked and Clustered combination graph
ฝัง
- เผยแพร่เมื่อ 9 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Easy steps to create a clustered stacked column chart in Excel - this chart is both stacked and clustered. This type of chart is not available in the standard Excel chart selection. With a few steps, you'll learn how to create this from scratch.
⬇️ Download the workbook here: pages.xelplus.com/column-char...
One way of creating a clustered and stacked column chart combination is to use both the primary and secondary Y-axis. One problem you'll run into is that they will likely have a different minimum and maximum which will show the wrong visualization. You need to ensure that both the primary and secondary y-axis have the same minimum and maximum in a dynamic way (without manually fixing these values). I show you the trick on how you can set this up.
👉 What You'll Discover:
- Step-by-step guide to creating a clustered stacked column chart.
- How to manipulate data for clear visualization.
- Tips for using primary and secondary axes effectively.
- Dynamic adjustments for changing data sets.
- Cosmetic enhancements for clearer, more professional charts.
📈 Perfect for:
- Excel users of all levels, especially those in data analysis and reporting.
- Anyone looking to enhance their Excel charting skills.
- Those who need to present data in an engaging, easily understandable format.
🔑 Key Takeaways:
- Simplify complex Excel charting tasks.
- Ensure your data stands out with advanced formatting techniques.
- Learn to automate and dynamically update your charts.
Links to related videos:
Charts Basics: • Excel Charts & Graphs:...
Dynamic Charts based on drop-down selection: • Excel Dynamic Chart wi...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
![[4K] BABYMONSTER(베이비몬스터) “FOREVER” Band LIVE Concert “영원히” 베몬만을 사랑하게 되…💗 [it’s KPOP LIVE 잇츠라이브]](http://i.ytimg.com/vi/TQfC0xdpiw4/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/column-chart-file
This video helped me two times in less than four months. Microsoft office developers should really add a stacked bar chart.
@monink980 - my secondary bar chart is higher than primary bar chart. I added Inv value 2 times and didn’t fix the problem. Do you know how to show the 2nd bar chart not overlapping the primary bar chart in stacking?
I was so glad when I saw your face when I did a TH-cam search on this very topic! I was struggling to get cluster stacked charts to work and I was going about it the wrong way! You've been a constant reliable source for my Excel development and I think you for all that you do.
Very cool Secondary axis and line too : ) Great Tricks!
Thanks Mike! Just finished watching your comprehensive Excel for Math intro video. Love the color convention. Learning Excel couldn't be made simpler :)
Thank you for your kind words!
@@LeilaGharani thats the true spirit
Love this video 💕
Thanks LG
This one is very unique topic
Well done. Easy to understand both visually and audially. Thank you
Fascinating. Very complex--and useful.
Thank you Oz :)
This is such a useful video, thank you for helping me find a solution.
One of the best TH-cam channel to learn excel . The way you explain it is simply awesome .Could you please upload some videos for array function ?
Wow! That's a very nice comment! Thank you! Anything special you're looking for regarding arrays? I have some videos in this playlist here: th-cam.com/play/PLmHVyfmcRKyxsnv_kvZ7X_h7y7dTEzyGs.html
Interesting challenge and awesome solution. Always thinking out of the box. Thank you, Leila!
You're very welcome Celia! Thank you for your comment :)
Thanks Leila, it's an amazing tutorial. i am very happy to learn from your tutorial. The way you explain is very simple and awesome. please keep uploading more excel stuff.
You're very welcome Gulshan. Glad you like it.
Leila good morning > I find this video very hope full. The way you explained I can follow you very well. Every day i become more interested in your video.
Useful and helpful tutorial. Big thanks!
This is way too awesome. Thanks Leila. Appreciate it.
Thank you.. i was looking for this solution so much !!
Thanks Leila for this video & information
Is there a way to also have 3 number sets of data stacking along with clustered visibility ?
This was so helpful!! I have used this multiple times
I love it your video. Simple and clear to follow it.
I think I am in love - brilliant video.
thanks so much for this - exactly what I needed and explained so clearly!
Glad it was helpful!
So you have an explanation of "activating" charts? I've never heard of this before. Excellent video as we've come to expect from you.
Once again, a Big salute to my beloved Excel Teacher, no words to praise your teaching method.I Pray the almighty to give good health, to my beloved/beautiful Teacher to do more Excel Videos in future for the benefit of all the Excel learners/users/addicts.--Big Royal Salute.
Thank you very much Gopala. Glad you like the videos. Wish you also all the best and will do my best to keep this up in the future :)
Thanks a lot Excel Guru/Teacher. I am very much benefited by your videos in learning skills/tricks.I pray the almighty to bestow good health & prosperity in every walks of life.
Thank you so much, Leila. I'm happy to learn with your lessons and I will practice tomorrow morning.
You'd BETTER practice tomorrow or you'll be in trouble. 😊
You're very welcome. Yes - practice is the only way. I'll have workbook uploaded tomorrow. Will let you know once it's there in the comment.
I've added the link to the workbook now in the descriptions - Happy practicing :)
BIG THANKS for all of your great videos!! I have learned so much!! Really very useful ideas and tools, thanks!!
Glad you like them!
Thank you!!
Hi Leila, your videos and tips n trics are very informative and useful. I have a question - how to create two 100% stacked bar charts in single graph? Is there any work around? It would be very helpful to know.
Thanks Leila jan
What if we have already used the secondary axis for another chart i.e. we have a line chart showing cumulative sales across quarters?
Leila Gharani , thank you so much for this helpful video. Is it possible to create one stacked bar on the primary axis (with two values), and another stacked bar on the secondary axis (with two values), but with space between them? I have two data sets with very different value ranges, but I would like to display them in the same chart.
I have learned from you Leila, thanks to you and Happy Holidays!...
You're very welcome and thank you for your comment! Happy holidays to you too :)
Thank you so much. The VDO is very useful I actually utilize it for presentation.
That’s great. I’m happy to hear that.
Great Video. Easy instructions to follow. thsnks
Thanks so much, I almost got it working but might be doing something wrong. When I tried this, the columns stack behind each other (from the bottom axis), instead of stacking on top of each other. Is there a way to fix that?
Hello. How can I add a percentage line? I am finding it hard to do since it has already a secondary axis. Thank you
YOU'RE MY ANGEL.. I JUST SPENT 1 HR TO DO WHAT YOU JUST SHOWED ME.. THANK YOU
I'm very happy to hear that! Thank you for your comment. Hopefully next time it will just take a few minutes :)
I love you!
thanks for sharing!
You are a saviour!!
Thank you so much!!
Hi Leila, could you show us how to overlay 2 radial bar charts- it would be very helpful for me at work🙏
Thank you for the video!!
i need this, thank you so much
Really excellent! Saved my bacon on a deadline! You are very smart with Excel, Leila!
Glad it was helpful!
This is one's good. Thanks Leila
Great demonstration .. Really helped me a lot ...
Amazing Leila. Very Helpful and I will use it at my work for Quarterly Summery.
Great! You're very welcome John.
Thank you so much for your tutorial.
highly useful thank you to share your knowledge with others
Outstanding!!
what an awesome video as always thank you so much and i'm sorry i watched it late because in so busy in work.
Thank you Ismail for your comment. Glad you like it! Of course work is priority :)
You genius 🤓 I needed it badly today
Hi Leila, I'm a PICU doctor with zero computer skills! I've been trying to make the routine data we collect "sing"! Your videos have been invaluable - Thank You...
It's great you are developing new skills! I'm really glad I can contribute a little to that.
Ah. MAY. Zing. Fantastic solution to this problem which has been plaguing me all morning!!
Happy to help :)
Thank you for this video, its very helpful and the way you explain it in simple is great to understand. I have one pivot table with data how do I convert this data into a readable chart which works in presentation as well, I have to report this data in the presentation.
Leila, You are Awesome at excel, love the tutorial. The best part about your videos, is your explanations are clear and easy to understand. Do you know in this example if excel allows you to put the total portion on the bottom and the apps portion on top? I tried to inverse the series that you would select to the secondary access, but no go. :(. Again Thank you you're awesome.
Great chart and as always great explanation
Thank you Vida.
OMG thank you! This saved me such a headache.
U are so helpful I can't thank you enough. 👍
You're very welcome Hernan!
Thank you so much, very helpful
You're very welcome Stuart.
Thank you so much.
Thank you Leila, the video is really helpful, I have a similar problem but i have 2 sub categories in 1 category eg -P-newapp1 & P- newapp2. can this be solved using this technique?
Thank you so muchhhhhh!!!!! You are GREAT!!!!!
You're very welcome :)
thanks a lot, it helps me
Thanks for making this kind of videos, I watch many videos, it really helped me a lot.. thank u, love your channel and subscribed
Thanks for subbing!
how do invert the total for a bar. e.g. 500 on top 2000 bottom
Thanks for sharing such a great knowledge, I hope you can help me. I want to send multiple excel charts through outlook which I have created after watching your excel videos but it doesn't fit properly. I mean I want to send it like it is in excel dashboard but its very difficult. Hope you will sort this out for me.
Amazing! Thank you!
Glad you liked it!
thanks for this.
Great solution. Thanks Leila :-)
Glad you like it Alex :)
Thank you so much for the video @Lelia Gharanti. Can you show us how to fix the secondary bar chart (5) overlapping the primary bar chart (4) in stacking? I did two of inv values. Still didn’t work
in this example if i need to get p -new apps bar above the productivity - how do i do ? example 2000 dark blue is below and 500 (light blue) is on top of the dark blue ?
Do you have any video on how to bring the series names inside the bars in a clustered bar graph?
This was exactly what I needed 🤩
Happy to hear that :)
Hi , on top of these, i need to add a line chart for another set of data for the same period. Is there a workaround since the secondary axis is already utilized
Thank you so much!!! I wrecked my brain to figure out this graph for my boss.
Glad it helped!
Hi awesome explanation but if I want to show data values with percentages as well how do i do that in same chart pls help
could you explain how to create a stacked and non-stacked clustered chart. Thanks!
like this .. I've learned many things from this video. Thanks v much
Glad to hear that!
Thank You Leila
Is it possible to add labels to both stacks and the total? I haven't been able to figure this out and have been manually adding a label to the second stack.
very nice information ... but how to put standard error or deviation in this type graph
How do you show cumulative/additive stacking rather than one in front of the other/overlapping?
can you have a combination of stacked and cluster column on the same axis? because i have another series that i am plotting on the primary axis.
Leila, what happens when the values from one axis are close to the values from the other I find that as they vary from greater than or less than in each column, I lose visibility of the one of the axis. Is there a resolution for this? Is the only solution to vary which column is on which axis, manually? Also how do you create a gap between each column?
If I have high and low numbers in different periods you said add the Min value as you did when adding the invisible value, yet I get an error when doing so saying "error in formula" Make sure you've included all the required parenthesis, verify any reference to another sheet, avoid an equal or minus sign. I did the same formula and tried adding the same. specifically how do I add that parameter to the primary axis.
Question - Is it possible to do three stacks? It seems like since there is only a primary and secondary axis, it doesn't seem possible to add a third stack since there is not a 3rd horizontal axis?
Your are a genius!
😘
Hi, I have a question, is it possible to add lines to the chart? But not one for the entire graph but four? One line chart for each quarter? Thank you
HI. Thanks for the video. It was very useful...! I have a question with regards of the Stacked Column chart. I already set the colors for my different data. The issue is when I delete one of the group values because I don't need it anymore, the chart automatically change all the colors and does not keep the original ones as I structured them at the beginning. The problem with this is that I need to dispose that old information and add new data to show on the chart. Is there a possibility to block the colors on the pivot chart so the ones that remains, keep their original colors?
Thanks for your response!
Have a nice day.
Are you using you colors based on a theme? For the colors to remain the same, if another series is removed, it's best if you fix the color directly on the chart and not rely on the theme....
@Leila Gharani Thank you so much for the very informative tutorial! I have just one question. around 6:27 you mention the possibility of the primary series to be smaller than the other. If that is the case, then the bigger column will cover the smaller one. How can one solve this issue? Again thank you so much!
Hi Pietro - You're very welcome. You can get around this by adding an invisible point, like I did here - but you can add that point to both the primary and the secondary axis...this will make sure that they are always in-sync.
Thanks for the reply! I also found another way (not sure how intuitive), by colouring the bar with a gradient. Example: tot n projects 10, of which 3 have been given a score of 1, 3 of 0.5 and 4 a score of 0. So now we have 3=30%; 3=30% and we leave the 0s out. Since the total would be 60% I created a bar chart where for that specific bar I put 60%. Then I take the proportion of 30% and divide it by 60% (=50%). At that point if you move on format data point on PPT you can just put both gradients at 50% under 'position'. It's a bit tedious - especially if you have to do it with a lot of bars, but I found that it also works. I guess you're method is probably quicker than this though. In any case thank you for taking the time to answer my question!
Awesome Leila! This is the solution I am looking for. 5:57 "Why did it do that?", so cute
How to update chart by adding more entries? I mean I am using your template and want to add more entries.
Another beauty created by you. I am a data visualisation guy and I love to learn new techniques in data visualisation. I use Tableau very often but I think you create very useful charts fitting the use cases of many problems. I also refer to some other you tube channels for Sumit Bansal (Trump Excel) & Chandeep (Goodly). I also have my own You Tube channel and am currently creating videos for basic Excel as of now but will soon move into the advance Excel concepts and features and go upto Business Intelligence tools as I am a full time faculty for a B-School in Mumbai and teach subjects related to Analytics. Your channel is a great inspiration to me. Thanks once again.
Thank you! You have a great TH-cam channel! Keep it up. Looking forward to the BI tutorials :)
Leila Gharani thank you for your kind words . Look forward to associate with you on this platform. It has been a great learning so far and hope it goes on the same way.
Hi Leila I Purchased your Excel charts course from Udemy. I just wanted know about overlap series.. So my question is if we have 4 series which gets overlap first.. wo we have our control on series overlapping.. kindly suggest.
Hi Leila, Thanks is helpful. Also i am looing for stacked waterfall chart. Can you please share the same. Thanks once again
I need to increase the x axis of the excel column chart . I can do the vertical grid line distances not the horizontal between columns
Thanks mam for sharing such nice and helpful videos....
You're very welcome Ubaid! Thank you for your support and comment :)
You are very welcome mam...but tbh thank you for your support and help...
Despite my last comment though, this can be a great chart combination to show how much of the current budget on each category is being used up. 😉
At this point, I'm just watching these because they're so satisfying.
Have created a similar stacked graph and eliminated several of the items from legend as shown. Have a more complex graph and copied the first one as a template, however, now wish to add items to the legend. Deleting is obvious, how do you add items to the legend?
Nice Trick ...
Glad you like it Sukhomoy.
Thanks Leila, but I'm struggling to force my secondary Y -axis ( in %) to always start from 0, on a Power Pivot combo chart. My primary y-axis always may have negative values when the pivot changes, can you do a video explaining this?. can write DAX measures to determining max etc. but need to align primary and secondary axis to always start from 0 dynamically.
I am hoping to create a stacked/clustered bar graph that shows actual daily progress but also compares it to the weekly plan for that activity. Say I have four items. I would have a bar representing weekly planned amount and a stacked bar below it showing actual daily progress. I need this for each item. Would you happen to know how to do that? Any help would be greatly appreciated! I have learned a lot from your videos!