For stacked bar charts, Excel 2010 allows you to add data labels only to the individual components of the stacked bar chart. The basic chart function does not allow you to add a total data label that accounts for the sum of the individual components. Fortunately, creating these labels manually is a fairly simply process.
Step 1: Create a sum of your stacked components and add it as an additional data series (this will distort your graph initially)
Step 2: Right click the new data series and select “Change series Chart Type…”
Step 3: Choose one of the simple line charts as your new Chart Type
Step 4: Right click your new line chart and select “Add Data Labels”
Step 5: Right click your new data labels and format them so that their label position is “Above”; also make the labels bold and increase the font size
Step 6: Right click the line, select “Format Data Series”; in the Line Color menu, select “No line”
Step 7: Delete the “Total” data series label within the legend
simple and neat.
Thanks Daniel. 😉
Very informative. Thanks
Elegant and well-explained. Thanks!
I like this a lot! Thanks for making it so clear. I’m also displaying a legend and I didn’t want to “Total” series showing up in the legend, so I right clicked on that legend item and chose Delete (not delete series). That removed it from the legend. 🙂
you could also just remove the name “Total” from the column
Perfect – Thank you very much.
Thank you!
Thank you.
I used this to add Error Bars for the total amount in a stacked bar chart. Thanks for the help!
Read all your articles. Thank you so much for sharing these.
Super! This was just what I was looking for! Thanks a lot!
Is it possible to add this total data labels to the Excel Stacked Bar Chart in 3-D? I tried following this process and didn’t work. I got a message that I can’t combine 2-D with 3-D charts, any suggestions how to show this totals in the Stacked Column in 3-D chart? Thanks
I’m having the same issue. Were you able to find a solution?
Hope you figured out the solution for this. You will have to change your graph to 2-D Bar Graph and then add the Line.
Very clever — thanks so much for this tip! I would have never thought of it myself.
thanks for the how-to. It’s exactly what i’m looking for.
Looked complex… but the way explained has made it very simple… kudos…. 🙂
Great stuff, cheers.
Just add another series to the stack, make it equal the the total of the stack. Format it to be no fill and align data labels to the inside base
*bump* good post. In comparison to many other explanations that seek to do the same, but use invisible columns on top of the existing data instead of lines, this workaround comes with the advantage that the scale of your axis does not have to be adjusted (good if your numbers change regularly).
Thanks . Very helpful….
Thanks. Within seconds I did it
Nice! Easy to follow and very neat. Thanks
Awesome post. Thanks a ton. However we need to get the Chart customisation options differently in Access where i was struggling a bit.
Also i had a 3-D Stacked Bar graph and when i tried to add the Line, it dinot not allow me. I had to change my Bars to 2-D to make this work!
Excel2013 does not seem to allow the GRAND TOTAL to appear in a STACKED BAR chart. Hence, the steps mentioned in this posting does not seem to work for this version of Excel2013.
If anyone have a solution to this (pls let it be simple of within Excel2013 features which I missed), appreciate the updated posting.
I was questioning the same thing; however, I found a way!
1. Add a totals at the bottom of your data chart.
and you will see your stacked bars go crazy.
2. On your graph, click the top colored bar, and select them.
and follow from the step 2
Thank you very much for sharing this information.
Technically, this is a stacked column chart, not a stacked bar chart.
Thank you so much!!! This helped me a lot! I did this using Excel 2013 and I basically followed the same steps. Thank you again.
Thanks a lot! Elegant solution.
Thanks. This helped me a lot.
Nicely explained. Thanks a lot.
Nice workaround the excel … simple and easy to understand !
Thank you 🙂
I had a chance to visit this page by searching through google. I was stuck on making the Microsoft Powerpoint presentation because I didn’t know how to add the total number on top of those the stacked bars in my graph. I just want to point out that it was very helpful, and could finish my presentation quickly.
Thank you so much MATTHEW KUO for the information! You are awesome!
Thank you..very useful information
Thanks so much! This was super helpful.
Thank you! I was looking for this
Thank You!
Superb technique make my work so easier
what a creativity
Wow, thank you so much!!!! I have been struggling with this for so long.
Thanks but your article title is drawing Google searches for a different question. Any chance you could change it? This is a stacked column chart but your title says it’s a stacked bar chart. Unfortunately this method doesn’t work for a stacked bar chart.
Hi there,
I’m experiencing a problem with this solution – if I have slicers on my graph and I select different filters that reduce the data set (specifically the fields that are contained in the Legend), the formatting of the total labels disappears. This is not usable from an end-user perspective. Any idea on how this can be solved / forced to keep the formatting?
thanks
Miguel
I still can’t believe that Microsoft hasn’t fixed Office 2013 to allow you to just add a total to a stacked column chart. This solution works, but doesn’t look nearly as nice as a 3-D stacked column chart would. Also, some of the labels for the totals fall right on top the other column labels and therefore makes both of them unreadable.
You made my day. Thank you very much for sharing useful and detailed steps.
Great tip! I was doing this by including the total as an additional stack bar but this distorted the axis range if using in a dashboard with slicers. Thanks!
Yep, that’s a great time saver and very easy to create polished stacked columns w/totals. I’m a huge Excel geek and had no idea you could delete the line series so easily. I spent a few minutes building my mobile and then saved it as a template – will be a huge time saver. Thanks!
This is very simple and instructive – but this is a Column Chart (vertical) – not a Bar Chart (horizonal). I tried the same apporach with a Bar Chart (horizontal) and it is not the same/will not work the same way.
Is there a similar approach to doing this with a horizontal Bar Chart (rather than with a vertical column chart)?
Hi, Thank you for the information. I face a small issue while following the step by step procedure. When I change the chart type as in Step 3, all the other stacks/data series turn to line as well.
Is there a fix for this?
Thank you.
Bijoy
This is GREAT! Thank you!
how to do it if you got sub-section on the horizontal axis.
Thanks a lot!
Thanks.. but it is not happening with stacked cone. can you please help.
OMG!
Thank you so so much. You are amazing.
Thank you very much for sharing with us this great solution! Very helpful.
Is there a way to add totals to the right of a true stacked bar chart (you’ve provided instructions for a column chart.) I can’t seem to change reverse the axis for the line (totals) series. My total are ending up at the top of the chart as opposed to the right. Thank you!
This was the first page I looked at when searching for help, it was well explained and I love the simplicity of the screen shots to guide me through. Worked first time and made me look very competent to my boss!!
Easy to follow and saved me from talking to the coworker that overcomplicates things to make it seem like he’s smarter than everyone.
Complete genius! Thanks you so very much!!
Very helpful–just what I needed for my Defect Trend report.
Thank you Very Much
Thank you so much!
Neat & clear. Thank you
So helpful, thank you!