How to Insert Dynamic Labels Inside Stacked Column Charts in Excel

In the last video, I showed you how you can add total values to your stacked column chart in Excel. In case you missed that, I have added the link to it in the descriptions below. Now in this lecture, I want to take this visualization one step further, and I want to add the legend inside the chart. What I mean is that I want to add the series name of each of the stacks beside the last stack. So basically, what I want to happen is this. Let's insert a text box. This is Europe. Okay, so what I want to do is to have Europe sit right in the middle of the last stack. And then for this one, this is America, to sit right in the middle of America. But I don't want to do it the way I just did it now because it's not dynamic. If my stack all of a sudden shoots up, this is going to stay here. I want a label that's going to move with it and always be in the middle of the last stack. Because honestly, I find it much easier to read if you have the label right here. People don't need to go up and look at this color and be thinking, okay, this is this one, and then this is this one, and so on. They just see it immediately by looking at this chart. How do I do it in a dynamic way? First rule is that whatever you want to add inside your chart needs to be a position. A position means it needs to be numbers. So it doesn't matter that we want to add text to it. First, we need to bring in the position. We need to mark exactly where we want to have that text. And then we can think about how we can bring that text in there. So we know that we need a series. Okay, so that's how we can bring our positions in the chart. What type of series do we need? Well, I don't want to add a stacked column chart here. I just want markers. And markers is scatterplot, right? I could bring a scatterplot series. The good thing with the scatterplot is that it allows me to fully control the values in the x-axis as well as the y-axis. So in this case, what value would I need for the x-axis if I want a point here, I want a point here, and I want a point here? What value should my x-axis be? Well, it should be something bigger than four. So let's say maybe five. I want one here. And they all need to be five, right? Because it's going to be a vertical line. That's the x. What about the y? What should it be? Well, for the first stack, I want it to be right in the middle of the first stack. So that would be 450, right? For the second one, what should it be? Should it be 250? No, because it's a scatterplot. It's cumulative, right? So I need to add the 900 plus the 250, and that would give me a point right in the middle of the second stack. Okay, so don't forget that it's not a stack column chart. In fact, it's cumulative. So let's add the data that we need for it here. So I'm just going to add this data preparation right beside my original table. And I add a column for the x. So in this case, it's five. Okay, I can fix it. But let's say I want to make it dynamic in this case, that I might be adding years and so on to this. So I'm going to use COUNTA, COUNTTHIS, and I'm going to add a one to it. Okay, for these, I'll just reference the first cell. Okay, so I just need labels for these three. I don't need a label for my total, so I leave that out. What about y? I said I want it to be half of the first stack, and then the second one at this plus half of this. So the formula I could use is a SUM, and I'm going to sum this with itself. I'm going to fix the first cell reference. Okay, so in the first case, it's just 900. And then I'm going to deduct half of it. Okay, so that should work well. So now all I have to do is to add this inside my chart. I'm going to right mouse click, select data, add the series name with data labels. You can notice here that I already get prompted to add the x and y. So Excel knows that I want to add a scatterplot. Why? The last series that I added was a scatterplot. And because of this, it thinks that the next series I want to add is a scatterplot. So in this case, it's fine, but in case it's not prompting you for scatterplot, what you need to do is to put in your y values first, go to your chart, change the chart type of this series to a scatterplot, and then come back here and put in the x values. Here I can't, I'm just going to highlight this, and that's my y. Okay, so these are my points. They look well positioned, except that they're a bit too far. So I'm going to adjust this by, let's go with 0.3. Okay, so that's fine, it's sticking to it, but the points, remember, I'm going to hide them. What I want from these is the data labels. So I'm going to add these data labels here. Now, is this the data labels that I want to show? No, I don't want to show this. So double-click, but be careful what you select in charts. In this case, I double-clicked only on this one, but I want the entire series. So series, data labels, data labels. That's what I want, and I don't want to see this. And instead, I want to do value from cells and actually show the labels. I don't need these. Okay, so if you have Excel 2013 and above, you can do what I'm doing right now. If you have Excel 2010 and below, what you need to do is click on each of the data labels, go to your formula box, type in equals, and then select Europe for the first one. Then for the next one, click on this one, type in equals, select America, and the same thing for Asia. Okay, so it's a bit longer, but you can get there as well. Now, as the last thing, I'm going to select this whole series. Everything is selected, and I'm going to hide these markers by taking away the shape fill and the shape outline. Okay, so now they are in there. I don't need to show anyone this. Can expand my chart area. Let's test this. I'm going to increase America, say, to 1,000. You see, everything jumps up. I can also change the formatting of this, make it a bit bold so that it's easier to read. That's how you can add series labels in a dynamic way to your stacked column charts. If you take a look at this, and people are going to read this. First, they have to look at the color. They look at Europe. They look at this. They're trying to figure out the total from the y-axis here, but when they look at this, they have all the information on their hand immediately. They can immediately tell this is Europe, immediately tell this is America, this is Asia, and they can immediately compare the totals to one another. I hope you enjoyed this video, and if you like this type of videos, don't forget to subscribe to my channel to get updates when new videos like these come out.