Label Totals on Stacked Column Charts
by Jon Peltier
Thursday, October 29th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
A common question is “How can I label the total stack values in a stacked column chart?” When you add data labels to the chart, you can’t get any labels showing the totals.
Let’s use some simple data to illustrate.
Here is a standard stacked column chart.
When we apply data labels to the chart, we get a label for each column segment in the stack, but no labels for the total of each stack.
We only need to add a series to the chart that encodes the totals. Column E has the totals for each stack.
Construct the chart as a stacked column chart, with the Totals column stacked on top.
Change the Totals column series to a line chart type series.
Add data labels to the Totals series.
Move the labels to the Above position (right click on the labels and choose “Format” to open the format dialog).
Hide the Totals series by formatting it to show no lines and no markers.
Nicely done, without being too sneaky: just one hidden series.
Related Posts:
- Hide Series Data Label if Value is Zero
- Stacked vs. Clustered
- Individually Formatted Category Axis Labels
- Individually Formatted Dual Category Labels
- Callout Labels with XY Line Segments
- Candlestick Alternative: Individually Colored Up-Down Bars
- Link Chart Text to a Cell
- Stacked Bar Chart Alternatives
- How to Build a 2×2 Panel Chart
- Simple Waterfall Chart with Up-Down Bars
Posted: Thursday, October 29th, 2009 under You Asked For It.
Comments: 13
Comments
Comment from Robert
Time: Thursday, October 29, 2009, 7:32 am
Helpful post thanks. Is it possible to achieve this on a horizontal stacked bar chart too?
Comment from Jon Peltier
Time: Thursday, October 29, 2009, 7:36 am
Robert -
I’ts possible to get this effect in a stacked bar chart, but it’s not as easy. You can’t use a line chart series for the labels. You could use an XY series, and you’d have to mess around with the primary and secondary axis scales (see Bar-Line Combination Chart). Or you could add another bar series onto the stack, with values of zero and custom labels. Good topic for a follow up.
Comment from Tony
Time: Thursday, October 29, 2009, 12:13 pm
I like that you have the x-axis as being a category versus time because we all know that a stacked column chart is bad for showing data series’ over time, right? Good tip!
Comment from chip
Time: Thursday, October 29, 2009, 12:35 pm
Jon, I do these slightly differently and it works for bar charts (per Robert’s request) the same as for column charts and has I think fewer steps.
Same methodology up through where the totals are added. Instead of converting to a line chart:
1. Open the formatting for that series, change it to Secondary Axis. Now it’s sitting in front of the stacked bars I really want to see.
2. Add data labels to that series. (They go in the right spot at the top of the bar).
3. Format the area pattern as None and the border as None.
The bar disappears, leaving the data labels and the underlying stacked bars or columns.
This is another one of the things that Harvard Graphics for DOS used to do automatically! Silly that you have to trick the program to add these.
Comment from Jon Peltier
Time: Thursday, October 29, 2009, 12:49 pm
Thanks, Chip. I was going to write this up as a follow-up, but you’ve done it for me. I suppose I could make a illustrated version….
Comment from Robert F
Time: Friday, October 30, 2009, 2:48 pm
You can also use the right menu to change the total part of the stack to area and border= none. Then select total data labels adn change the alignment label position = inside base.
Comment from Anna
Time: Tuesday, February 23, 2010, 12:56 am
This information was most helpful. Thanks.
Comment from Doug Keith
Time: Monday, April 26, 2010, 3:03 pm
Thanks for the great tips! Worked like a charm.
Comment from Kirvin K
Time: Monday, July 26, 2010, 11:33 am
Thanks Jon, very useful tips. Can you think of way to show Pivot Chart Totals or should I just give up and create a regular chart? I would really like to have the pivot filter capability while displaying the totals over my stacked bars.
Comment from Jon Peltier
Time: Monday, July 26, 2010, 3:12 pm
A regular chart will do this easily. You probably can’t do it with a pivot chart unless you have created a custom field to capture the totals.
Comment from Neil Jimack
Time: Thursday, August 5, 2010, 6:30 am
I’ve been asked to produce something very similar, but I also need to apply labels to each series showing their percentage of the total.
I tried creating several dummy series’ which calculate the percentage, using a secondary axis, but can’t get the labels to line up with the values.
Comment from Jon Peltier
Time: Thursday, August 5, 2010, 8:18 am
Neil -
Calculate the percentages in the worksheet, and use Rob Bovey’s Chart Labeler to apply these custom labels to the stacked column series.
Comment from Neil Jimack
Time: Thursday, August 5, 2010, 9:13 am
Thanks Jon. Unfortunately I can’t download that onto my work computer, so I’ve had to resort to manually linking each label to a cell.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.