Last week I posted a Friday Excel Challenge!. Fun times. How did you do? Spoiler alert, if you don’t know how to create this type of chart yet, DO NOT read on. Click on the logo in the top left and then find the previous post. Then after giving it a try and leaving me a comment on how you did it, then come back to this post. If you are the “I always read the last page of the book first in case I die” then read on my friend.
Here is the final chart we are trying to make:
It is a 100% Stacked Column Chart with two labels of a percentage and the value. Here is how we got here:
On Friday, I posted this question from an Excel user forum and asked my readers to give this type of chart a try.
The Challenge:
% and Number within 100% Stacked Chart?
Is it possible to list both a numeric value and its percentage within each block of a 100% stacked chart? For example let’s say I’m trying to show how many Apples, Oranges, and Bananas (Y) that Sandra, Susie, Jamie, Jorge, and Alberto each have (x). If Sandra has 3 apples 1 orange and 1 banana, can I show within each block of her stacked chart 3 (60%), 1 (20%), and 1 (20%) respectively as opposed to only a number or only a percentage? Thanks a lot for any help!
Sample data:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Apples | Orange | Banana | |
| 2 | Sandra | 3 | 1 | 1 |
| 3 | Susie | 1 | 1 | 3 |
| 4 | Jamie | 4 | 2 | 3 |
| 5 | Jorge | 1 | 3 | 1 |
| 6 | Alberto | 5 | 3 | 5 |
I had one super fan (PETE) that got this right and he taught me a thing or two by saving me a step from my original posting. Here is what Pete sent me via Email:
From Pete:
“I copied the set of provided data into my workbook, and then made an identical table next to it to show the relative percentages. Assuming my original data table started in cell A1 and my new data table starts in cell F1, my new data table values have formulas like this: =B2/SUM(B$2:B$6) and the values are formatted as a %. This gives me the percent of the total each person has of each fruit type.
Next I made a 100% stacked column chart from the new data table. Then I copied (CTRL+C) the original data table and pasted it into my chart as a new series…right click on the chart and select paste, the new values will appear in your chart.
This added them to the stacks, which is not what I wanted…so I moved the new information to a 2nd axis. This causes it to overlap the original chart exactly. I added data labels so now both % and values show. I had to move the labels around a little so they did not overlap. I also formatted my chart to get rid of the chart junk…I removed the grid lines, removed the axis lines, and changed both vertical axis labels to white text to hide them. As a final touch, I changed the series colors to match the fruit…yellow for bananas, orange for oranges, and red for apples.”
I also had another great but different entry by David.
From David:
You probably have a more eloquent technique, but I ended up with: Use text as your series labels. I have the counts in a table, I have the percents in a table, then I make up a table of text entries using an equation like this: =TEXT(LEFT(B4,2)&”, (“&LEFT(A11*100,2)&”%)”,0). Then I use those results as my labels.
That is brilliant too!! Mine is very similar if not exactly the same as Pete’s, but David’s was very ingenious use of the Text function and Excel charts.
Let me walk you through my solution below with a complete with a video tutorial.
The Breakdown
1) Setup Chart Data Range for Percentages
2) Create 100% Stacked Column Chart
3) Switch Rows/Columns
4) Add Data Labels
5) Change Percentage Data Label Position
6) Move Percentage Chart Series to Secondary Axis
7) Clean up chart junk
Step-by-Step
1) Setup Chart Data Range for Percentages
There are two tips and tricks regarding this challenge. The first is the data setup. Notice that we have 2 data labels. The first is the number value and the second is the percentages. You can only make a 100% Stacked Column chart in Excel with one or the other. So to fix this, we need to add 3 more series:
So we need to add 3 more series that display the percentages. Here is are the data points and formulas in the Chart Data Series:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Apples | Orange | Banana | Apple Precentage | Orange Percentage | Banana Percentage | |
| 2 | Sandra | 3 | 1 | 1 | 60% | 20% | 20% |
| 3 | Susie | 1 | 1 | 3 | 20% | 20% | 60% |
| 4 | Jamie | 4 | 2 | 3 | 44% | 22% | 33% |
| 5 | Jorge | 1 | 3 | 1 | 20% | 60% | 20% |
| 6 | Alberto | 5 | 3 | 5 | 38% | 23% | 38% |
Worksheet Formulas
|
These additional series will provide us with a dynamic chart series for our Excel chart data labels.
2) Create 100% Stacked Column Chart
Now that we have our chart series data range set up, we need to create our 100% Stacked Column Chart. To do this, highlight the range of data from cell A1:G6
Then go to the Insert Ribbon and choose the Column button from the Charts group. From there, choose the 100% Stacked Column Chart
Then your chart should look like this:
Not the way we wanted to show the data, but we can fix that.
3) Switch Rows/Columns
If you want to know why Excel formats your chart this way, you should check out this post:
Why Does Excel Switch Rows/Columns in My Chart?
So to make the chart the way we want, we need to select the chart and then go to the Design ribbon and from there choose the “Switch Row/Column” button from the Data group:
After you perform this step, then your chart should look like this:
This looks more like the chart we are looking to create.
4) Add Data Labels
Now is the time to create the data labels we seek. To do this, select the chart and then go to the Layout ribbon and choose the Data Labels button in the Labels group. From there, select the Center choice. Your chart will now look like this:
5) Change Percentage Data Label Position
Getting really close, but we need to move the data labels for the percentages to another area so that they don’t overlap the values data labels when we complete the final stages of the chart. To do this, select a set of data labels for any of the percentage data series (in this case, I choose the Banana Percentage data labels:
Then press your CTRL+1 keys or right click on the labels to bring up the Format Data Labels dialog box:
Then DO NOTE CLOSE this dialog box and select the other two data percentage data labels and make the same selections on those series. Your chart should now look like this:
It doesn’t look that different, but it will with the next step.
6) Move Percentage Chart Series to Secondary Axis
This step is where everything lines up for us. Select any of the NON-percentage data series and then press CTRL+1 to bring up the Format Data Series dialog box. You can also get to this dialog box by right clicking on the data block in the chart column and then select “Format Data Series…”. There is also other ways that I know of that are really fast when selecting a data series. Check out these postings and video tutorials:
The Quickest Way to Select an Data Series in an Excel Chart
How-to Select Data Series in an Excel Chart when they are Un-selectable?
When you have this dialog box open, move the data series to the secondary axis:
Repeat this step for all of the NON-percentage data series. When you complete this for the NON-percentage data series your chart should now look like this:
Awesome, we are almost there to the final chart. Now one caveat, this chart will work find for you as long as your data series are not too small. If they are too small, then the percentages will overlap the 100% Stacked Column values.
7) Clean up chart junk
Just one last step. We need to clean up the chart. Too many percentages and legend entries. So do the following:
a) Select the horizontal grid lines and press the delete key
b) Select the left vertical axis and press the delete key
c) Select the right vertical axis and press the delete key
d) Select the legend and then select any percentage legend entry and press the delete key (Repeat this step for all of the percentage legend entries)
Your final Excel chart has arrived. Here is what it should look like:
I think this is a cool tip and trick with Excel 100% Stacked Column or 100% Stacked Bar charts and graphs. Let me know what you think in the comments below and any other tips/tricks that you want to learn.
Free Excel Template File Download:
100% Stacked Column Chart with Percentage and Value Labels
Download here: Show Percent and Value 100 Percent Chart
Video Tutorial:
Also, don’t forget to subscribe to my blog and video channel so that you are sure to get the next post delivered directly to your email inbox.
Steve=True
I was looking at this post and figuring out why our percentages didn’t match…
I calculated the % of each fruit a person has, while it looks like you calculated the percentage of a persons fruit is a type…
Meaning, I show what percentage of all the apples a person has, broken down by person. While you show what percentage of all the fruit an individual has broken down by type of fruit.
This is just a different way to look at the same data. It all depends on your point of view.
Thanks for the great site and challenges.
Hi Pete,
It might look weird if your percentages don’t add up to 100% in the column.
Also, from the user “If Sandra has 3 apples 1 orange and 1 banana, can I show within each block of her stacked chart 3 (60%), 1 (20%), and 1 (20%) respectively…”
so the percentages are by person.
Thanks for your great comments.
Steve=True
Yep, you are right. I should have thought about that when I worked the file. If we really wanted to show the numnbers (%) that I have broken out in my example, the columns should be stacked the other way…people stacked by fruit. But that was not the point of this excercise.
Thanks for the feedback.
-Pete
Good catch by the way. I saw you b got the technique right and I missed that your percentages were different 🙂
Steve=True
Hi,
I followed you guide: “100% stacked Column Chart”
But when i finished my “vertical (value) axis” and “secondary vertical (value) axis) had a wrong percentage range, both were 90% to 100% –
I want it to be like yours – 0% to 100%
Where can i change that?
Hi Filip, I think you chose the wrong chart type. You need to choose the 100% Stacked chart. Or change your minimum to 0. Hope this helps. Steve=True
Hi Steve!
No i chosed the right chart type. How do i change my minimum to 0? I think that might be the problem..
BR Filip
It might be the problem. From the design ribbon, add your secondary vertical axis again so that you can see it. Then select the secondary vertical axis and press Ctrl+1 to bring up the Axis Formatting. You will see that you can change it in the Axis Options. Hope this helps. Steve=True
Hi,
Did this (horizontal) on my Excel for mac and worked great, just what I was trying to do. The only problem is that the first column (B) is not showing the data lable?!? It has all the same ticks as others and nothing. All the others are, but not this one. The same thing happened in the “trial chart”.
Can you help?
Thank you
Hi Barb, hope you got it fixed. I don’t have a Mac, but seems like you may have an incorrect range for your labels? Let me know if you need help. Maybe you can send me the file and i can check it out.
This doesn’t seem to work on my end. When I do the “Switch Row/Column” operation, only the values show in the chart. The percentages are ignored. They show up in the legend, but not it the bars. What could be causing this?
You should try and make them a large number instead of % so you can see where they are to troubleshoot if they are in the right place or order.
For excel 2016, we could do more easier by creating the chart as normal and just formarting the data labels to show both value (number) & values from cells (percentage)
Interesting approach, thanks for the feedback.