Excel Column Chart - Stacked and Clustered combination graph

in this video I'm gonna show you how you can create a clustered stacked column chart in Excel it's basically going to look like this if you ever tried to put this together from scratch you're gonna see it's not that easy why because Excel allows you to insert a clustered column chart you can insert a stacked column chart but you don't have an option to insert a clustered column that's stacked in order to set this up yourself you just have to take a few steps which I'm going to show you in this video I got this question some time ago from an excel user I just changed the data set for the purpose of this video but the idea is this we have quarterly revenue by division that we want to show in this way each column here belongs to one division but what we want to do is to split each column into two one part of this shows the revenue of the new apps within that division and the other column shows basically the rest and on top here we have the total value how can we set this up lest it is from scratch right here the data set that we have is given to us in this form that we have for q1 this productivity value this is the value for the entire division and this 500 is the value of the new abs so this is a part of this 2000 okay so given that I'm going to highlight this I'm going to go to insert and insert a clustered column chart first thing I noticed is that the queues are here and not here I want to bring them on the axis what I have to do is right mouse click select data and switch the row and the column now I get the quarters right here now let's take a closer look at this data set this first one is the whole productivity this is the new apps in productivity so I don't want them right beside each other but I actually want these ones so the new ones here to be on top of the totals one way that occurs to me is to put these on the secondary axes and the way I can do it fast is to right mouse click go to change series chart type and wherever I see this new app to check mark this and say push it on the secondary axis that looks better but what's the problem here look at the axis zero zero one thousand two hundred two thousand five hundred I have different maximums for the y-axis I can't have that they have to be identical so my primary axis and my secondary axis have to be identical how can I get them to be identical well I could manually fix them I could double click on this and fix it to two thousand five hundred and fix this one as well so two thousand five hundred but I don't want to do that because next month maybe my maximum is gonna be three thousand I want to make this dynamic the way I can make it dynamic is to introduce one single data point in this chart that's going to be invisible no one is gonna see it the purpose of this one single point is to make sure that the maximum of this is going to be identical to this what number should this data point have it should have the same maximum number as the primary series so what's the maximum number here for the primary series it's 2,100 an Excel by default just as a bit to it it came to two thousand five hundred in order to make my secondary axis match my primary I need to make sure that I have one single data point that has this number right now I don't all my numbers are far smaller that's why Excel is giving me a much smaller maximum for the secondary than my primary if I just add one series I can call it invisible value that's going to be the max value in this data table and I add this on the secondary axis when I should have the same maximum for the y-axis to add the same all I'm gonna do is right mouse click select data add series name that's this one series value is this one it's okay okay looks like it's messed up everything because it's added a new column here but I don't need to add this as a column what I'm gonna do is to right mouse click change series chart type and change the chart type of this new one that I added from a clustered column to something else that's not a column so let me go with a line the line is just one point no one is gonna see it on there you see it automatically made the secondary axis identical to the primary why did it do that well did you notice that if I just go back did you notice that the tick mark was there it added it to the secondary axis by default so if it didn't do that for you and it added it to the primary you need to push it to the secondary axis and now they are the same if your example was different and your primary values here could be smaller than your secondary values what you need to do is to add this series again to your chart and have it attached to the primary axis you would add it in two times once on the secondary ones under primary and this way it doesn't matter which one has the bigger value it's gonna force to make them match okay so that's trick on getting the same maximums on your primary and your secondary y-axis all we need to do now is to make some minor cosmetic adjustments today's my legend for example I'm gonna push it to the top and I'm gonna delete the ones that I don't need so no one needs to see that I have an invisible value there I'm just gonna click click and press Delete and these new ones you can choose to keep them if you want but I'm just gonna remove them from here I'd rather not have so many different legend texts in there and in fact in this case this belongs to one division the second one is game and so on so I'm gonna change the color of these to something that's gonna be obvious that they belong to the same thing but if you have a different setup you probably need to keep the additional names for the legend as well what I'm going to do now is to add the data labels to these right mouse click add data labels the same for the other series I'm also going to add the data labels to the bottoms back to improve the readability of this what I'm gonna do is to reduce the gap width between these stacks so that my data label is going to sit inside the columns let's go to the chart options and reduce the gap way to like 70% I have to do it on both sides of the primary and for the secondary the same number okay the other thing I'm going to do is to bring these a bit together they're a bit too far apart I'm just going to click on one of these snacks and instead of - 27% for the overlap I'll do minus 10% again I have to do it on both sides let's delete the axes I don't need that I'm gonna remove the grid lines as well as the last step I'm gonna bring these data labels inside the bottom stack so there is a setting but you can say label options inside base I'm going to repeat that for the other ones now the totals I could also change them to balls to make them really stand out and the same for the axis that's pretty much it you can add a chart title equals this now just to make it obvious that this bottom part is the new apps what I can do is to bring this in a little bit click on my charts so that it's activated go to insert and insert a text box come here click there and type in new apps now we can format this the way we want and push it right here I fixed it in this case I don't need it to be dynamic because the new apps part is always going to be on the bottom of this chart but it just might make it more obvious to the people that this bottom part is the contribution of the new apps to the entire revenue and because I activated the chart before I went on an insert shape it's already a part of my chart I don't need to group anything together so the moment I move this this text is going to come with it just like the chart title comes with it this was one way on how you can create a clustered stacked column chart in Excel I hope you like this video if you did don't forget to give it a thumbs up and for more videos like this one don't forget to subscribe to this channel so that you can get updates when new videos like this one and come out

Channel: Leila Gharani

Views: 307,238

Rating: 4.8667111 out of 5

Keywords: excel column chart stacked and clustered, excel clustered stacked column chart, excel stacked chart side by side, excel data visualization, stacked and clustered combination graph, make excel bar charts thicker, primary and secondary axis same scale, how to create excel stacked chart, excel charts switch axis, XelplusVis, Advanced Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts

Id: IwWh3UOTymE

Length: 11min 4sec (664 seconds)

Published: Wed Dec 20 2017