Add Totals to Stacked Column Chart

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.