Excel Column Chart - Stacked and Clustered combination graph

Рет қаралды 526,216

Күн бұрын

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

Пікірлер: 267

@LeilaGharani 6 ай бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/column-chart-file

@mobink980 11 ай бұрын

This video helped me two times in less than four months. Microsoft office developers should really add a stacked bar chart.

@sallyho146 Ай бұрын

@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?

@Scott93312 2 жыл бұрын

I was so glad when I saw your face when I did a KZfaq 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.

@tednewman4768 5 жыл бұрын

Well done. Easy to understand both visually and audially. Thank you

@onlinebusiness5305 6 жыл бұрын

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.

@cathyb2878 5 жыл бұрын

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.

@Dev_Bartwal 6 жыл бұрын

Love this video 💕 Thanks LG This one is very unique topic

@ExcelFormulasHacks 6 жыл бұрын

One of the best KZfaq channel to learn excel . The way you explain it is simply awesome .Could you please upload some videos for array function ?

@LeilaGharani 6 жыл бұрын

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: kzfaq.info/sun/PLmHVyfmcRKyxsnv_kvZ7X_h7y7dTEzyGs

@gulshanmehta6997 6 жыл бұрын

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.

@LeilaGharani 6 жыл бұрын

You're very welcome Gulshan. Glad you like it.

@robh9207 3 жыл бұрын

This is such a useful video, thank you for helping me find a solution.

@1gopalakrishnarao 6 жыл бұрын

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.

@LeilaGharani 6 жыл бұрын

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 :)

@1gopalakrishnarao 6 жыл бұрын

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.

@zenyhuelgas8128 3 жыл бұрын

Useful and helpful tutorial. Big thanks!

@bricc322 6 жыл бұрын

Interesting challenge and awesome solution. Always thinking out of the box. Thank you, Leila!

@LeilaGharani 6 жыл бұрын

You're very welcome Celia! Thank you for your comment :)

@luqmananuar6705 Жыл бұрын

This is way too awesome. Thanks Leila. Appreciate it.

@rchltrrs 2 жыл бұрын

This was so helpful!! I have used this multiple times

@mikepike7063 5 ай бұрын

I think I am in love - brilliant video.

@irviantantohaditomo8917 5 жыл бұрын

I love it your video. Simple and clear to follow it.

@EZern1 2 жыл бұрын

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.

@nightangel8087 4 жыл бұрын

Thank you.. i was looking for this solution so much !!

@OzduSoleilDATA 6 жыл бұрын

Fascinating. Very complex--and useful.

@LeilaGharani 6 жыл бұрын

Thank you Oz :)

@excelisfun 6 жыл бұрын

Very cool Secondary axis and line too : ) Great Tricks!

@LeilaGharani 6 жыл бұрын

Thanks Mike! Just finished watching your comprehensive Excel for Math intro video. Love the color convention. Learning Excel couldn't be made simpler :)

@excelisfun 6 жыл бұрын

Thank you for your kind words!

@kinshukpatil7490 3 жыл бұрын

@@LeilaGharani thats the true spirit

@gurucool80 2 жыл бұрын

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.

@soumiamoujane3695 9 ай бұрын

Great Video. Easy instructions to follow. thsnks

@theturnip69 4 жыл бұрын

thanks so much for this - exactly what I needed and explained so clearly!

@LeilaGharani 4 жыл бұрын

Glad it was helpful!

@rienthongw 6 жыл бұрын

Thank you so much, Leila. I'm happy to learn with your lessons and I will practice tomorrow morning.

@OzduSoleilDATA 6 жыл бұрын

You'd BETTER practice tomorrow or you'll be in trouble. 😊

@LeilaGharani 6 жыл бұрын

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.

@LeilaGharani 6 жыл бұрын

I've added the link to the workbook now in the descriptions - Happy practicing :)

@theviewerisgone 3 жыл бұрын

BIG THANKS for all of your great videos!! I have learned so much!! Really very useful ideas and tools, thanks!!

@LeilaGharani 3 жыл бұрын

Glad you like them!

@valerianverdy4546 6 жыл бұрын

YOU'RE MY ANGEL.. I JUST SPENT 1 HR TO DO WHAT YOU JUST SHOWED ME.. THANK YOU

@LeilaGharani 6 жыл бұрын

I'm very happy to hear that! Thank you for your comment. Hopefully next time it will just take a few minutes :)

@roshp3577 5 жыл бұрын

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...

@LeilaGharani 5 жыл бұрын

It's great you are developing new skills! I'm really glad I can contribute a little to that.

@TheRon3141 3 жыл бұрын

Really excellent! Saved my bacon on a deadline! You are very smart with Excel, Leila!

@LeilaGharani 3 жыл бұрын

Glad it was helpful!

@tanasiripa8413 6 жыл бұрын

Thank you so much. The VDO is very useful I actually utilize it for presentation.

@LeilaGharani 6 жыл бұрын

That’s great. I’m happy to hear that.

@johnborg5419 6 жыл бұрын

Amazing Leila. Very Helpful and I will use it at my work for Quarterly Summery.

@LeilaGharani 6 жыл бұрын

Great! You're very welcome John.

@yoursolemate 2 ай бұрын

Thank you!!

@tinovakimzamora9799 6 жыл бұрын

I have learned from you Leila, thanks to you and Happy Holidays!...

@LeilaGharani 6 жыл бұрын

You're very welcome and thank you for your comment! Happy holidays to you too :)

@bushrasait7724 Жыл бұрын

You are a saviour!! Thank you so much!!

@RenzMartyGamaro 4 жыл бұрын

This is one's good. Thanks Leila

@sanjaydatta3741 2 жыл бұрын

Great demonstration .. Really helped me a lot ...

@mirahmadhaidary5491 4 жыл бұрын

highly useful thank you to share your knowledge with others

@manoellopes8071 2 жыл бұрын

Thank you so much for your tutorial.

@joesatriani2189 4 жыл бұрын

i need this, thank you so much

@waltermorales9849 2 жыл бұрын

I love you! thanks for sharing!

@milenamegre8927 2 жыл бұрын

Thank you for the video!!

@lakshmikanthraju105 Жыл бұрын

You genius 🤓 I needed it badly today

@ThePtaher 4 жыл бұрын

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?

@wameedabbas4952 3 жыл бұрын

Outstanding!!

@ismailismaili0071 6 жыл бұрын

what an awesome video as always thank you so much and i'm sorry i watched it late because in so busy in work.

@LeilaGharani 6 жыл бұрын

Thank you Ismail for your comment. Glad you like it! Of course work is priority :)

@MixBernstein 2 жыл бұрын

Ah. MAY. Zing. Fantastic solution to this problem which has been plaguing me all morning!!

@LeilaGharani 2 жыл бұрын

Happy to help :)

@KedarBhuptani 2 жыл бұрын

Thanks Leila for this video & information Is there a way to also have 3 number sets of data stacking along with clustered visibility ?

@johnryan7616 2 жыл бұрын

OMG thank you! This saved me such a headache.

@anukarthika4ever 3 жыл бұрын

Hi Leila, could you show us how to overlay 2 radial bar charts- it would be very helpful for me at work🙏

@kbalaji9516 4 жыл бұрын

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.

@jannoduque4040 4 жыл бұрын

Hello. How can I add a percentage line? I am finding it hard to do since it has already a secondary axis. Thank you

@AweshBhornya-ExcelforNewbies 6 жыл бұрын

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.

@LeilaGharani 6 жыл бұрын

Thank you! You have a great KZfaq channel! Keep it up. Looking forward to the BI tutorials :)

@AweshBhornya-ExcelforNewbies 6 жыл бұрын

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.

@SurendraTarai 3 жыл бұрын

Thanks for making this kind of videos, I watch many videos, it really helped me a lot.. thank u, love your channel and subscribed

@LeilaGharani 3 жыл бұрын

Thanks for subbing!

@lizcannon2251 3 жыл бұрын

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.

@ershadismite 6 жыл бұрын

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 ?

@ArunSen 3 жыл бұрын

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?

@vida1719 6 жыл бұрын

Great chart and as always great explanation

@LeilaGharani 6 жыл бұрын

Thank you Vida.

@VijayKumar-mh8go 5 жыл бұрын

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.

@hernanperez912 4 жыл бұрын

U are so helpful I can't thank you enough. 👍

@LeilaGharani 4 жыл бұрын

You're very welcome Hernan!

@igmrakaalpinaditya2449 Жыл бұрын

thanks a lot, it helps me

@kaaa3485 6 жыл бұрын

Thank you so muchhhhhh!!!!! You are GREAT!!!!!

@LeilaGharani 6 жыл бұрын

You're very welcome :)

@stuartwickson8535 5 жыл бұрын

Thank you so much, very helpful

@LeilaGharani 5 жыл бұрын

You're very welcome Stuart.

@neebomar Жыл бұрын

Thank you so much!!! I wrecked my brain to figure out this graph for my boss.

@LeilaGharani Жыл бұрын

Glad it helped!

@alexrosen8762 6 жыл бұрын

Great solution. Thanks Leila :-)

@LeilaGharani 6 жыл бұрын

Glad you like it Alex :)

@kaaa3485 6 жыл бұрын

Thank you so much.

@marcogutierrez209 2 жыл бұрын

Amazing! Thank you!

@LeilaGharani 2 жыл бұрын

Glad you liked it!

@priscilacunha2819 7 ай бұрын

This was exactly what I needed 🤩

@LeilaGharani 7 ай бұрын

Happy to hear that :)

@SleepyBoBos 4 жыл бұрын

Awesome Leila! This is the solution I am looking for. 5:57 "Why did it do that?", so cute

@yasirhusni3449 4 жыл бұрын

like this .. I've learned many things from this video. Thanks v much

@LeilaGharani 4 жыл бұрын

Glad to hear that!

@nageshaadi 3 жыл бұрын

Hi Leila First of all, the way you present is truly simple and easy to understand. I was trying to create a chart, which is a combination of Clustered plus Stacked Column Do you already have a video for that? If not Please need your help. Not Sure how to insert files into this comment. Else, I wish to give an example.

@baphnie 5 жыл бұрын

At this point, I'm just watching these because they're so satisfying.

@patrickkinbonso1809 Жыл бұрын

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. 😉

@isalvare75 5 жыл бұрын

how do invert the total for a bar. e.g. 500 on top 2000 bottom

@FaysalEasyExcel 4 жыл бұрын

thanks for this.

@akky158 4 жыл бұрын

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?

@tameemalbatta7582 4 жыл бұрын

Thank You Leila

@debrahetmer9151 5 жыл бұрын

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.

@oobercheerful 3 жыл бұрын

How do you show cumulative/additive stacking rather than one in front of the other/overlapping?

@sallyho146 Ай бұрын

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

@user-xc8vp9nr1m Жыл бұрын

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

@spencerstreeter6535 5 жыл бұрын

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?

@deda118 2 жыл бұрын

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.

@swapnildewalwar1948 27 күн бұрын

For those who tried and the bars are not overlapping perfectly - right click on the bars, click on format data series, and make the series overlap to 100%

@LeilaGharani 27 күн бұрын

👍

@eyuelgorfu9404 4 жыл бұрын

Your are a genius!

@LeilaGharani 4 жыл бұрын

😘

@kaaa3485 6 жыл бұрын

Thank you so much for your tutorial it was very helpful. I took excel expert but I was not able to create the dual axis chart. the chart has the primary axis as column and the secondary as line. the chart has to show only where the two chart has value but if the secondary has 0 as value don't show the column data of the primary. Thank you so much.

@LeilaGharani 6 жыл бұрын

You should be able to set different min and max values for primary and secondary axis. Make sure your line chart is really based on secondary and adjust the secondary axis values. I can't see why it wouldn't work....

@kaaa3485 6 жыл бұрын

Thank you so much for your quick response. I have a question Create a combo chart only for a specific name: Horizontal axis label range: Date First data series range: Duration with a value of Carvallo in the name column Second data series range: Charge with a value of Carvallo in the name column Type: Combo chart Duration series: Clustered column (Primary axis) Charge series: line (Secondary axis) Name Date Time Duration Charge Carvallo james carvallo sarah

@LeilaGharani 6 жыл бұрын

Hi - I'm not sure about the question here...maybe you can post it either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...

@genellegaspiabordo8457 4 жыл бұрын

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?

@tejnabhandari8844 4 жыл бұрын

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.

@KshitijSharmaMe 6 жыл бұрын

How to update chart by adding more entries? I mean I am using your template and want to add more entries.

@SeeJunzhe 6 жыл бұрын

could you explain how to create a stacked and non-stacked clustered chart. Thanks!

@leandrogonzalez3295 6 ай бұрын

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

@MJs-85 8 ай бұрын

Do you have any video on how to bring the series names inside the bars in a clustered bar graph?

@user-cr9cm4wc1n 2 ай бұрын

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.

@rickrathier6064 4 жыл бұрын

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?

@naziarais1045 5 жыл бұрын

very nice information ... but how to put standard error or deviation in this type graph

@MoonShine-bs6cl 4 ай бұрын

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

@Teymourtash 3 жыл бұрын

Leila Khanoom, I have 2 products (X and Y) whose sale that I would like to trend for each month of 2018 and 2019. For example total sale in Jan 2018 next to total sale in Jan 2019, but show it in stacked bar, so I can not only show total sale comparison of Jan'18 and Jan'19, but inside the bar I can show product mix as well (X and Y). I have 2 years, 12 months per year and 2 products. I also need to show TOTAL sales (X + Y) on top of each bar for each month (Jan - Dec). How do you suggest I format my excel table? Merci, Mehran

@UbaidUllah-nj5et 6 жыл бұрын

Thanks mam for sharing such nice and helpful videos....

@LeilaGharani 6 жыл бұрын

You're very welcome Ubaid! Thank you for your support and comment :)

@UbaidUllah-nj5et 6 жыл бұрын

You are very welcome mam...but tbh thank you for your support and help...

@aremes007 3 жыл бұрын

How can i make a 3 level stacked - 2 column chart. I have data for number of manpower for electrician, mechanic and plumber distributed by months having the baseline and actual values. I want to see the difference between the baseline and actual per month in a chart. Thank you.

@juancarlosdiaz6158 6 жыл бұрын

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.

@LeilaGharani 6 жыл бұрын

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....