Stacked Charts with Labeled Totals
Several years ago on this blog, in Label Totals on Stacked Column Charts, I answered the common question, “How can I label the totals in a stacked column chart?”
You can take your data…
…create a stacked chart…
…and add labels…
…but the labels only appear on the actual points in the chart. There’s no way to make Excel apply labels with the totals.
The Manual Procedure
In that tutorial, I showed how to compute the totals in the worksheet…
…add the Totals data to the chart…
…convert the Totals data to a line chart series…
…add labels to the Totals series…
…reposition the labels above the line…
…hide the series (format with no line or markers)…
…and hide the legend entry for the Totals series.
This protocol is pretty easy, and only takes a minute, and it works for other chart types as well, like this stacked area chart:
But I guess I understand why this might be considered tedious. Which is why I’ve developed…
Automated Labeling of Totals for Stacked Charts
I started with a simple routine which:
- Checks that it’s an appropriate chart type for labeling stacked totals
- Counts the number of series, and points per series
- Creates and populates an array for the totals (see note below)
- Adds a series of the appropriate type (e.g., line for stacked columns)
- Hides the series
- Adds labels showing the values of the added series
Note: The routine does not add a column in the worksheet for the totals. Instead, it adds the data in VBA, creating an array of totals, then uses this array rather than a worksheet range to populate series it adds to the chart. This makes it a bit cleaner, and it’s easier than figuring out where in the sheet to put the totals. However, it’s also not dynamic, so if the values change, the labeled totals will no longer be correct.
Here’s the routine:
Sub AddTotalsToStackedColumnChart() If ActiveSheet Is Nothing Then GoTo ExitProc If ActiveChart Is Nothing Then GoTo ExitProc Dim cht As Chart Set cht = ActiveChart If cht.ChartType = xlColumnStacked Then Dim SeriesCount As Long SeriesCount = cht.SeriesCollection.Count Dim PointCount As Long PointCount = cht.SeriesCollection(1).Points.Count Dim Totals() As Double ReDim Totals(1 To PointCount) As Double Dim SeriesIndex As Long For SeriesIndex = 1 To SeriesCount Dim YVals As Variant YVals = cht.SeriesCollection(SeriesIndex).Values Dim PointIndex As Long For PointIndex = 1 To PointCount If IsNumeric(YVals(PointIndex)) Then Totals(PointIndex) = Totals(PointIndex) + YVals(PointIndex) End If Next Next Dim IsAxisBetweenCategories As Boolean IsAxisBetweenCategories = cht.Axes(xlCategory).AxisBetweenCategories Dim NewSeries As Series Set NewSeries = cht.SeriesCollection.NewSeries With NewSeries .ChartType = xlLine .Values = Totals .Format.Line.Visible = False .HasDataLabels = True With .DataLabels .ShowValue = True .ShowCategoryName = False .ShowSeriesName = False .ShowBubbleSize = False .ShowPercentage = False .ShowLegendKey = False .Position = xlLabelPositionAbove End With End With cht.Axes(xlCategory).AxisBetweenCategories = IsAxisBetweenCategories cht.Legend.LegendEntries(cht.Legend.LegendEntries.Count).Delete End If ExitProc: End Sub
With minor adjustments, this code will work for stacked column, area, and line charts. With major adjustments, it will also work for stacked bar charts.
Note: you should probably avoid stacked line charts. It is visually obvious when column or area charts are stacked, but not so for line charts. Often, people used stacked line charts when they didn’t mean to, when they didn’t even realize such an option was possible.
The Stacked Chart Totaler Add-In
I’ve modified the above bit of code so it will work on any of the stacked chart types, including stacked bars. I’ve added a custom ribbon button.
I’ve added a button on the chart’s context (right-click) menu.
I’ve added the ability to select which set of stacked data to label, if the chart contains more than one set.
You can download the add-in from this link: StackedChartTotalsLabeler.zip. The add-in is packaged in a zip file. Save the file on your computer, then unzip and install the file following instructions in Install an Excel Add-In.
About the Add-In
I have left the add-in unprotected in case you want to see how it all works. There is XML code that handles the custom ribbon tab and button. There is code in a second module to handle clicks from the ribbon button. A UserForm (i.e., a dialog) has been added to get input from the user. The main procedure is more detailed than shown in this article, to accommodate this dialog, and to compile data selectively.
I enjoy doing this kind of project. Even with the ribbon components and the dialog, it only takes a few hours. If you need something like this done, send me your requirements and I’ll generate a quote.
very impressive…. is this build into your Charts for Excel Add-in? Nice thing to add if not…
Jon Peltier says
I have not incorporated this feature into Peltier Tech Charts for Excel. I may decide to do so at some point.
I’ve written a number of small add-ins like this for my blog (see also last week’s Trendline Calculator for Multiple Series), and I think I may put those together into a “greatest hits” collection.
Doug Gabbard says
Please don’t do this. Instead, use your source data to create a nicely formatted table. Then, if a visualization is still desired, keep it simple, and let the chart accompany the table.
Jon Peltier says
I hear ya, Doug. But a lot of folks have a chart, and want these totals on the chart. When it comes to grammar, and data viz, I’m a descriptivist, not a prescriptivist.
I get the message “Cannot run the macro “OnLoadPeltier30BXL’. The macro may not be available in this workbook or all macros may be disabled.” when I load the add-in. Macros are NOT disabled.
Jon Peltier says
Sorry, my bad. I left a teeny piece of XML in the ribbon file that didn’t belong.
The perils of copy-paste programming.
Download the file again, and you should have no problem.
Chip Gorman says
This is great. I’ve done this so many times (since about 1995!), I can;t believe I never created something myself to do it.