How To Show Percentages in Stacked Excel Charts (in addition to values)

Data Translates
219 followers
In a previous video, I showed you how you can add totals
to your stack column charts,
and also how you can add the series labels
to your stacks in a dynamic way,
so it makes your chart easier to read.
There was a question on how we can add more than one value
to each of the stacks.
Basically, not just show the total value,
but also show its percentage of the total.
That's what I'm going to show you how to do in this video.
Here I have the same set of data.
I just added the calculation for the percentage of total.
Here you can see that Europe for 2014
contributed to 74% of total,
and here for Asia, we have 10% in 2017.
For each of the stacks, we're going to add two data labels.
We're going to show this, and we're going to show this.
First of all, I'm going to add the stacked column chart,
but a bit differently to what I did in the last video.
In the last video, I showed it to you in two separate steps.
I already had this data, and then I calculated this,
and I brought it in,
but once you know how the techniques work,
you can just do it all in one step.
So just highlight everything, insert a stack column.
Okay, that's our total stack,
so it's added it as a last stack on top of everything else.
All we have to do is to change the series chart type.
Of this total series, instead of a stack column,
I took, in the last video, I took a scatter plot,
but I'm going to show you that it works just as well
with a line chart.
I'll just pick this one line with markers.
Now for this series, I'm going to add the data labels,
and let's position them so they're on top.
Let's just go and make them bold and bigger,
and I'm going to hide this series here.
I can just go to format and shape outline.
I think that's already there, so no outline,
and shape fill, no shape fill.
That takes away the markers and the lines.
So let me just remove these,
and this total, I don't need it,
so you just click, click, and delete.
Now I'm going to push this to the top.
Okay, that looks better.
Let's just decrease the gap width here.
I'm just going to go a bit dramatic and go to 40%.
Okay, you can make the whole chart a bit smaller.
Okay, so now I'm going to add the data labels to these,
but I want these as well.
Now it really depends on the version of Excel that you have,
and the technique that you like.
So I'll just show you different ways of adding this.
One technique that works for Excel 2013 and above
is first of all, add the data labels.
Then when you go to format the data labels,
go to options, label options, and select value from cells.
In this case, this is Europe.
I'm going to select this row here and say okay.
Now I'm going to leave the tick mark on value
because that's the original value here.
Now I'm showing percentages and I'm showing the value.
It might be too cramped in this way.
I mean, right now with my thick bars, it looks fine,
but what you could do is define your separator
to be a new line, and that way you push it down.
Okay, so you have some separator options here.
By default, it's a comma,
but you can pick period, semicolon, or space.
I picked a new line here.
This works for Excel 2013 and above.
Now, let's say that you have an older version of Excel
or you also want to add your own type of separator
or you just want to add your own type of formatting.
So let's say you just wanted the percentage part
to be in brackets,
and you can't find that type of formatting here.
What you could do is to add your own labels.
I'm just going to copy these, paste them here,
and say for data labels.
I'm going to add my own labels here.
What I want is to have this number
and have a space first,
and this number, and I want to put this number in brackets.
Basically, I'm going to start and say
equals bracket open quotation mark,
and then my number,
and there's a lot of ands there,
but that's how you combine text with cells.
You always need these quotation marks.
So and bracket closed quotation marks,
and that one, that should be fine.
Okay, so it's putting my brackets,
but it's showing the full number here
because the formatting of the cell
when you're combining different numbers,
it reverts to general.
And I have different numbers
and I want to format them differently.
So the function that works great here is the text function.
What I can do is to wrap this one in a text function
that says format this one cell as a percentage.
That means I need to define zero.
Zero is just a placeholder for the number and percentage.
I don't want any decimal places.
Okay, so that looks okay.
Let's just check.
Okay, that works.
Now for the last one, I'm going to do the same thing,
but I don't want to show it as a percentage.
I want to show it with a thousand separators.
So I'm going to use the hashtag sign
for a placeholder as my number
and the zero for the last one.
And that should work.
I don't need to fix any cells.
I can just drag this along and drag it down.
Okay, let's just double check that the references are okay.
And that's the one right behind my chart.
Okay, let's just make sure it's 10%.
That looks good.
I have my own type of formatting.
Let's bring it in.
I'm going to right mouse click, add data labels.
So you always need that before.
And then you can format the data labels
to show what you want.
This technique that I'm going to show you right now
is again for Excel 2013 and above,
but I'm going to show it to you for this last stack.
I'm going to show you how it works with Excel 2010
or Excel 2007.
Here, we're going to say value from cells
and we're going to select,
now I'm in the second one, America.
We're going to select this one and okay.
Okay, but this time I don't need the value.
So I can uncheck this.
Leader lines, I don't need either.
Can't see them anyhow on this.
But how do I get a line break in here now?
I can try with the separator and say show new line,
but it's not showing more than one thing.
That separator works if you're showing
more than one category, one name here.
And I'm not, I'm showing everything in one.
I actually want to generate a line break in here.
And I can do that using the Alt and Enter key.
So right here in this text where I have my space,
I'm going to click and I'm going to press Alt Enter
and then press Enter.
Okay, and that generates that line break
and I can copy it across.
Okay, so that still works in this stack.
Now in the last stack,
I'm going to show you the method for Excel 2007 and 10.
Again, add the data labels to it.
Then click on the data labels,
but now what you need to do
because you don't have this option available,
what you have to do is this.
You have to click again on the first label,
go to the formula tab, press equal and select your number.
So that's my 2014.
And then do the same for the next ones.
In this case, because this is a smaller stack anyhow,
I don't need to generate a line break,
but the line break technique works also
when you use this technique.
If I wanted to generate a line break here,
I just go to this data label, press Alt and Enter
and Enter, and then it reflects it here.
Okay, but in this case, I don't want to do that.
Now that I'm showing both,
I can actually remove the Y axis.
These are different ways that you can show
more than one data label in each of your stacks.
The version that you choose really depends
on what you prefer
and also on the version of Excel that you have.
For me, this is a little bit too crowded.
It's just too much data in the chart.
I prefer to restrict it to one data label
and then show the other part,
the other values that I want to show in a different way.
Here, let's say the important thing to show
is the total values here
and the percentage contribution
of each of the stacks to the whole.
The way I would prefer to show this is like this.
I'm just going to copy this chart and paste it down here.
Let me bring it up so that we can easily see both.
What I would do is to activate the axis here,
the primary vertical axis,
and leave that here so that people can easily deduce
the amount of each of the stacks.
On the stacks themselves,
I'm just going to show the percentages.
Okay, so I'm just going to update these.
What I'm going to do is,
let's just reactivate the data labels here.
I'm going to show value from cells,
and I'm just going to show these percentages.
I'm going to take away the value from this.
Now for the second one,
I'm going to change that cell reference to show these ones.
And for this, I'm just going to delete and reactivate
because I had direct cell references there.
And I'm going to do the same thing and show these values.
This way, I can even make it much smaller.
I'm showing the exact information here.
It's just with less data.
I'm able to compare the totals to one another,
and I can see the percentage contribution
of each of the stacks to the total.
I also have an idea about the size of each of the stacks,
and it does take a bit less space on my report.
So these are different ways of showing percentages
and totals in the same stacked column chart.
If you enjoyed this video,
don't forget to give it a thumbs up.
And if you like this type of videos,
don't forget to subscribe to this channel
to get notifications when new videos like this one come out.
