Add Total Values for Stacked Charts in Excel: Column & Bar Charts

All right, thanks for tuning in, everyone. This is Anthony Smoke. Go ahead and check me out on anthonysmoke.com. Definitely hit subscribe here on YouTube. As always, if you learned something, go ahead and hit that like, leave a comment, and make sure you hit the bell so you get that notification when I drop a new video. Today we're in Excel, right? So what data professional doesn't love Excel for reporting, right? You don't have to answer that. But there comes a time when we don't have the most optimal tools for our data visualization and reporting needs, and we need to MacGyver our way to a solution. And I'm talking to my hardworking, database-deprived analysts toiling away in finance or external reporting. You're probably cutting and pasting from Excel at this very minute. But that's okay, because this video is gonna save you some time because it's your one-stop shop for all things stacked bar totals in Excel. So here is a manual task in Excel that drove me up the wall until I got a decent handle on it. So let's take a look at some data here. I've just got some products, and then I've got a ship load here. And if you look at my stacked column chart here, you'll see that I have the totals here at the top. So this is not something that's intuitive and Excel makes easy to do. You're gonna have to hack your way through it. And then I'm also gonna show you how to do it this way, your stacked bar, right? Excel calls them stacked column and stacked bar. So on the stacked bar, we also have the totals here. So let me show you how you would end up doing something like this, adding those totals to the stacked bar and the stacked column. So let's start with stacked column first. So my original data, if you take a look, my original data looks a little bit more database-friendly here. You got your subcategory, your ship mode, your sales per customer. If we look at what I've got here, all I did was pivot that data to turn it into this. This isn't a pivot chart. I removed kind of the underpinnings there for the pivot chart, and I just cut and pasted into this format. Not gonna show you how to make a pivot chart. That's not what this video is about. We're gonna get right into creating the chart here in Excel. So let's highlight all of these the products, and let's highlight the ship mode as well. And I'm not including the grand total here. And then once we have those highlighted, we can come up here and we can insert a 2D column, stacked column right here. All right, so we have something like this. And then I'm just gonna go ahead and make this a little bigger so we can see that. And we can do a little bit of formatting here. I can say equal to this right there. And if you'll notice, I have, see how we have the first class, same day, second class, standard class. Those are the columns up top here. What I want, I want my first column here to be on the axis. So what I like to do, I mean, you could do it either way, but what I like to do is I like to switch the row and column. So you'll notice my chairs, tables, machines, and copiers are here on the axis. And then on these different cuts, right, I should have, right, copiers. So this is all copiers, sorry. First class, same day, basically my ship mode. So once I'm in this format right here, what we can do here, we'll just do a little bit of formatting. I'm gonna delete this right here. And then you can go to add labels and bring in all of your labels. Do that fairly quickly, right? And so all of those will correspond to the different values here in the subsequent columns, right? So what I wanna do is get all the grand total stacked on top here. It's a little hack. So in order to do that, let's select, right, we select the chart, and then we can expand the chart here to include the grand total. And you'll notice we have this big chunk here that corresponds to our grand total. So if I select copiers here, you'll see the value of 8534, which corresponds to copiers. And we can actually turn, we can add data labels there. So you can see that that is the grand total. And so what we wanna do now, let's go in here. Let's change the series chart type. And this will pop up, right? We're in a combo chart. And right, all of these ship modes, we wanna keep a stacked column. But on the grand total, I wanna change this over to a line, right? Say okay. And we end up with something like this. And then I'm gonna go in here and make sure that you select the whole line. I'm gonna right click here. And let's go ahead and format the data labels. And those should show up at some point. And I wanna make sure that those are above, and you'll see that they move there. And I can leave that alone for now. And then we can get out of here. I'm gonna come back and make sure I'm selecting the line. You'll see all the points here. And then I'm going to format the data series. And if I come over here, I wanna say no line, right? And that gets rid of our line. And so now all we have to do, I can select these and just format this as normal. I can make that bold. I can make this bigger if I want to make that stand out. And you'll notice we have the grand total down here. We don't want that grand total to show up. So I'm gonna select it and hit delete. And there you go. We have our stacked column with the totals on top. So that's part one. Now stick around. I'm gonna show you the stacked bar totals. Let me show you this guy right here, how we add those totals to the stacked bar total. So stick around. Okay, so the stacked bar chart in Excel, adding totals, this one, this is the one you wanna stick around for. This one's a little bit trickier, a little bit more hackier. You don't see too many solutions for this on the internet. So this is the one you wanna watch. So let's go ahead and highlight. And well, first thing you'll notice, I have this new column called spacing, right? So you'll wanna put this column in here. I call it spacing. And you wanna have a, yeah, we'll start with a value of zero. I'm gonna change this so you can see, but we'll start with this. So let's highlight all of this, not including the grand total. And then we're gonna go up here to insert, and we go back over here, and we want our 2D bar. And we end up with something like this. Let's go ahead and expand this out so it's easier to see, right? And we're gonna do our little formatting, get rid of that. Chart title. There we go. All right, and you'll notice, right, so spacing is here. Basically, again, I want my products to be on the axis so I can switch the row and column. And you'll notice that spacing disappears because it has a value of zero, right? And we wanna make sure that our grand total does not include the spacing value because we're gonna change it. It's only summing up these values. So, all right, so what do we do with that spacing value? So right now, just temporarily, I'm gonna put a value of 1,000 in here just to get something to show up, right? And so you'll see, right, I could copy this down, but I don't wanna screw up my formatting. So you'll see I've added this spacing value here. Let's go ahead and turn all these on just so you can get a feel for what these correspond to. Gonna go add all those data labels, and I'm gonna add the data labels for the spacing. It has a value of 1,000 right here. And so what I'm gonna do, I'm gonna go in here, I'm gonna right-click, and then I want to format the data labels. And you'll see it has a value here. And what I want, I want the value from cells, not the value, the value from cells. So I'm gonna select value from cells. It's gonna ask me for a data label range. I'm gonna select the grand total here without the headers, say okay. So what that does, you'll see that it's trying to put in two values here. It's putting in the value and the value from cells. If I uncheck value, you'll see that my 1,000 now is replaced by the grand total. So that's what that value from cells does. And let's go ahead and format the spacing data labels. Let's move this position here to inside base. So we're gonna move that to inside base. And then I'm gonna select, I'm just gonna go back in here and select the series here. And we want to change the fill to no fill, right? So now we just have our value just kind of floating out here, right? Changing it to inside base keeps it right there. And so what I can also do, I can go back and change these to zero just in case, right? So if I change those to zero, you'll see that, oops, get rid of that. You'll see that my values are still here, right? So a little hacky way of doing it. Basically, there's a zero value here, but we're showing the grand total instead of the zero. I mean, that's the trick right there. And of course, obviously we can do some minor formatting just like we did before. Make that stand out, make that pop. I want to get rid of my spacing value. We don't need to see that. And then, you know, you can do something like this. You can format the legend. If you want to bring it to the top or move it around, you can do that. Just showing you a little extra tip, but here you go. This, this is how you add totals to your stacked bar chart in Excel and your stacked column chart in Excel. So hopefully this has been helpful. As always, this is Anthony Smoke. Get out there and do some great things with your data. Thanks for watching.