How to Add Total Data Labels to the Excel Stacked Bar Chart

by Matthew Kuo on April 3, 2013

in Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

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.

Stacked Total 0

Step 1: Create a sum of your stacked components and add it as an additional data series (this will distort your graph initially)

Stacked Total 1

Step 2: Right click the new data series and select “Change series Chart Type…”

Stacked Total 2

Step 3: Choose one of the simple line charts as your new Chart Type

Stacked Total 3

Stacked Total 4

Step 4: Right click your new line chart and select “Add Data Labels”

Stacked Total 5

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

Stacked Total 6

Step 6: Right click the line, select “Format Data Series”; in the Line Color menu, select “No line”

Stacked Total 7

Step 7: Delete the “Total” data series label within the legend

Stacked Total 8

{ 56 comments… read them below or add one }

Dan April 3, 2013 at 10:44 pm

simple and neat.

Reply

Matthew April 4, 2013 at 2:56 am

Thanks Daniel. 😉

Reply

Vicky December 2, 2014 at 6:41 am

Very informative. Thanks

Reply

Mark July 26, 2013 at 6:56 am

Elegant and well-explained. Thanks!

Reply

Curtis August 15, 2013 at 11:48 am

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. 🙂

Reply

Chris September 20, 2013 at 11:54 am

Perfect – Thank you very much.

Reply

David October 9, 2013 at 3:08 pm

Thank you!

Reply

October 18, 2013 at 8:50 am

Thank you.

Reply

Leah October 26, 2013 at 7:21 pm

I used this to add Error Bars for the total amount in a stacked bar chart. Thanks for the help!

Reply

Helen November 4, 2013 at 2:32 pm

Read all your articles. Thank you so much for sharing these.

Reply

Emma November 22, 2013 at 4:44 am

Super! This was just what I was looking for! Thanks a lot!

Reply

Oscar December 4, 2013 at 6:52 am

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

Reply

Brock March 13, 2014 at 8:02 am

I’m having the same issue. Were you able to find a solution?

Reply

Farida February 2, 2015 at 12:36 am

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.

Reply

January 19, 2014 at 1:31 pm

Very clever — thanks so much for this tip! I would have never thought of it myself.

Reply

KC January 24, 2014 at 4:30 am

thanks for the how-to. It’s exactly what i’m looking for.

Reply

Kishore April 25, 2014 at 1:18 am

Looked complex… but the way explained has made it very simple… kudos…. 🙂

Reply

EE May 6, 2014 at 11:53 pm

Great stuff, cheers.

Reply

May 25, 2014 at 10:52 pm

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

Reply

DK May 28, 2014 at 1:53 am

*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).

Reply

MN May 28, 2014 at 3:08 am

Thanks . Very helpful….

Reply

zahid_SWCC_Khobar June 7, 2014 at 2:48 am

Thanks. Within seconds I did it

Reply

Rich June 11, 2014 at 8:21 pm

Nice! Easy to follow and very neat. Thanks

Reply

Farida February 2, 2015 at 12:34 am

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!

Reply

Luke February 13, 2015 at 1:15 am

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.

Reply

May 27, 2015 at 2:11 am

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

Reply

Blourf February 18, 2015 at 10:00 pm

Thank you very much for sharing this information.

Reply

Avid Africa Watcher March 24, 2015 at 10:34 am

Technically, this is a stacked column chart, not a stacked bar chart.

Reply

Susana April 29, 2015 at 2:41 pm

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.

Reply

Daniel May 14, 2015 at 10:28 pm

Thanks a lot! Elegant solution.

Reply

Pedro May 20, 2015 at 2:09 am

Thanks. This helped me a lot.

Reply

Chandana May 20, 2015 at 3:33 am

Nicely explained. Thanks a lot.

Reply

Shashikant May 25, 2015 at 5:44 am

Nice workaround the excel … simple and easy to understand !
Thank you 🙂

Reply

May 27, 2015 at 1:54 am

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!

Reply

Anil July 1, 2015 at 10:14 am

Thank you..very useful information

Reply

Lora July 9, 2015 at 12:41 pm

Thanks so much! This was super helpful.

Reply

Maggie July 17, 2015 at 11:05 am

Thank you! I was looking for this

Reply

Bobalz July 21, 2015 at 7:07 am

Thank You!

Reply

Noman Ali August 1, 2015 at 2:19 am

Superb technique make my work so easier
what a creativity

Reply

Rochelle August 13, 2015 at 7:58 am

Wow, thank you so much!!!! I have been struggling with this for so long.

Reply

Darren Reynolds September 3, 2015 at 9:59 am

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.

Reply

Miguel September 7, 2015 at 2:14 am

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

Reply

Jon September 15, 2015 at 12:13 pm

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.

Reply

rajanikanth October 7, 2015 at 7:59 pm

You made my day. Thank you very much for sharing useful and detailed steps.

Reply

Mary Beth December 18, 2015 at 11:56 am

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!

Reply

dan April 1, 2016 at 1:27 pm

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!

Reply

Steve April 6, 2016 at 12:40 pm

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)?

Reply

Bijoy M May 31, 2016 at 12:34 am

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

Reply

July 7, 2016 at 11:59 am

This is GREAT! Thank you!

Reply

Guillaume July 19, 2016 at 6:34 am

how to do it if you got sub-section on the horizontal axis.

Reply

Rogerio September 12, 2016 at 5:38 am

Thanks a lot!

Reply

Mam September 29, 2016 at 12:22 am

Thanks.. but it is not happening with stacked cone. can you please help.

Reply

Taha Hafiz October 31, 2016 at 6:43 am

OMG!
Thank you so so much. You are amazing.

Reply

Ayrton January 26, 2017 at 4:51 am

Thank you very much for sharing with us this great solution! Very helpful.

Reply

Susan February 3, 2017 at 9:26 am

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!

Reply

Caroline Shaw February 7, 2017 at 12:34 am

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!!

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post:


Related pages


example of vlookup functionvlookup for multiple criteriahistogram template exceldesign tab excellogical statement in excelwhat are vlookupsbell curve graph makerhlookup function in excel with exampleexcel if statement equalstick symbol for exceladvanced excel formulas tutorialmicrosoft excel demoexcel color schemeshow to remove duplicates from two columns in excelpre mba internship consultingfc seoul jerseyexcel dynamic formularandom usernames generatormba grading systemwhat does vlookup mean in excellookup vertical and horizontal excelusing countif function in excelvlookup hlookup combinationexcel formula example sheetucla mbaexcel to pdf file convertersumproduct and vlookupexcel free trial 2007quotes in excel formulah look up in excelcompare two pivot tablestop 20 most visited websitesnested if and functionsopen excel without macrosexcel 2007 formulas listduplicate excel formulaexcel macro matchdeloitte mba salarycreate a lookup table in excelexcel look up functionshow to make stacked bar chart in excelexcel format millionsfind duplicate rows in excel 2007excel v look upsfind duplicates in two columnsvlookup with multiple resultshow to use the vlookup function in excel 2010excel stacked bar chart exampledelete duplicate excelfind duplicate records in exceltranspose function excel 2010how to delete numbers in excelexcel vba multiple ifmicrosoft excel offsetlearn excel formulasms excel offsetexcel 2007 formulas tutorialexcel converter to pdfduplicate numbers in excelhow to convert excel files to pdfusing transpose in excelif isna vlookup functionexcel macro to remove duplicatesmaking a histogram in excel 2010harvard business review case studies solutionsduplicate function in excelexcel countif uniquevlookup in excelhow to find double entry in excel 2007excel chart data labelsshortcut key to delete a row in excelexcel task tracking templateconsulting names generatornested if function excel 2010 exampledelete shortcut in excel
\n