Label Totals on Stacked Column Charts
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
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.
Possibly 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
- Clustered-Stacked Column Charts
- How to Build a 2×2 Panel Chart
- Variable Width Column Charts
Posted: Thursday, October 29th, 2009 under You Asked For It.
Comments: 6
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.
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.