Transcript:
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, Its 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-axi’s 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-axi’s. 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.