How to Add Total Values to Stacked Chart in Excel

Data Translates
219 followers
In this video I'm going to show you how you can add totals to your stacked column charts.
Here I have data from 2014 to 2017 on personnel at work that is split by region and I want to
insert a stacked column chart. So I just have to highlight it, go to insert and click on stacked
column chart. I'll just make some minor adjustment to this. So let's add the chart title.
Let's remove the grid lines and bring the legend to the top. I normally prefer it on the top.
Format legend and switch it to the top. You can even increase the plot area.
What I also want to do is bring the bars a bit closer together. So right mouse click on
any of these stacks, go to format data series and reduce the gap width. So I'll go with 90 here
and now to add the data labels I'm just going to click on the stacks. So this is the bottom stack
and add the data labels to this. The middle stack, add data labels and for the last one.
Okay so now how do I bring the data labels for the total values on top here? Well the first thing
that you need to do is to add a new series to this. So anything you want to bring into a chart
needs to be numbers right? Because on a chart you always define the position of your series.
So I need the numbers and I need to bring them in. First of all I have to add the total here.
And that's basically the sum of all the regions per year. And now I have to bring it in the chart
so I'm just going to right mouse click somewhere, go to select data, add. Series names always give
your series a name so that you can click on them, you can find them later and the values are these.
Okay and that's okay. So by default it's thinking that I'm adding another stack to it right?
And it's doing the right thing but I don't want a stack so I just have to go back and change the
series chart type of my total series of this last one from a stack column to, what do you think?
To the xy scatter. So this way I get points, I get markers on there.
The thing about this right now is that the position of the marker is actually my total
value right? So all I have to do is select the markers and you see I can't really select them,
it's difficult. Whenever it gets difficult to select something in the chart just activate the
chart series option and go here from this drop down, select the series that you want to format.
So that's the total series and to this series I want to add data labels. And now I have to click
on the data labels because I want to format them. I don't want them to this side, I want them to
actually be on top and I'm going to go and put them right above. Now while I'm there let's just
make them bold and make them bigger. I can remove this right now, all I have to do is hide these
markers. So again I can't select them properly, I'm going to go to the drop down, select the total
series, the original series now, not the data labels and I'm going to hide them. You can hide
them from here. I'm used to going here and saying that I don't want to have a shape fill and no
shape outline. Okay so they are gone. Now my legend page, click on it, click on it again
to select the total because I don't want anyone to see that. I'm going to delete it.
That was it, that's how you can add totals to your stacked columns. This is all dynamic so let's just
test by changing the America 2017, let's change it to 500. Okay the total changes, the stacks change.
So in the next lecture what I'm going to show you is how you can bring in the legend
into your chart. I guess instead of having them like this to have Europe sitting right here,
America sitting here and Asia sitting there and we're going to do this in a dynamic way
so that if America gets bigger, our label automatically moves with it because we
don't want to just bring in text boxes. Thank you for watching and hope you enjoyed this video.
