Extract Labels from Category Axis in an Excel Chart (VBA)

Question: Extract a Category Label for a Point

I came across a question in the Excel Reddit, Is there a way to select a chart series point and have the label name of that point be copied into a cell?

Answer 1: Chart with Simple Axis

The short answer is the following function: you get .XValues property of the series, which is an array of category labels, and find the element of the array for the particular point:

Function GetCategoryLabel(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
  Dim srs As Series, vCats As Variant
  
  Set srs = cht.SeriesCollection(iSrsNum)
  vCats = srs.XValues
  GetCategoryLabel = vCats(iPtNum)
End Function

You call it like this, passing in the chart, series number, and point number:

Sub TEST_GetCategoryLabel()
  Dim s As String
  Dim cht As Chart, iSrs As Long, iPt As Long
  
  Set cht = ActiveSheet.ChartObjects(1).Chart
  iSrs = 1
  iPt = 3
  
  s = GetCategoryLabel(cht, iSrs, iPt)
  s = "Category Label for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
  MsgBox s
End Sub

This doesn’t stick the label into a cell, like the question asked, but displays the label with a message box. As shown below, Series 1 – Point 3 has the label “C”:

Answer 2: Chart with Multi-Tier Category Labels

It turns out that the chart was a pivot chart, based on a pivot table with several fields in the rows area. Each field contributes a tier of labels to the category axis. The screenshot below shows a Table of data, a Pivot Table based on this Table, and above both a Pivot Chart, with a two-tiered category axis.

If you use the simple approach above, the .XValues don’t distinguish among the tiers, and the labels from different tiers are put together, separated by spaces. In the chart below, Series 1 – Point 3 has the compound label “Alpha C”, according to the simple algorithm.

We need a much more detailed VBA approach. We have to parse the series formula, get the address of the range containing the category labels, then get the corresponding range, figure out how many columns of labels there are, then find the label in each column corresponding to the point. This is complicated by the empty cells in the pivot fields, so we have to keep looking upward until we find a cell with a label.

Here is the more intricate function that extracts an array of category labels:

Function GetCategoryLabels(cht As Chart, iSrsNum As Long, iPtNum As Long) As Variant
  Dim srs As Series
  Dim sFmla As String, vFmla As Variant
  Dim rCats As Range, vCats As Variant, vOutput As Variant
  Dim iRow As Long, iCol As Long, nRows As Long, nCols As Long
  
  Set srs = cht.SeriesCollection(iSrsNum)
  
  sFmla = srs.Formula
  sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
  vFmla = Split(sFmla, ",")
  
  Set rCats = Range(vFmla(LBound(vFmla) + 1))
  vCats = rCats.Value2
  
  ReDim vOutput(1 To UBound(vCats, 2))
  For iCol = 1 To UBound(vCats, 2)
    For iRow = iPtNum To 1 Step -1
      If Len(vCats(iRow, iCol)) > 0 Then
        vOutput(iCol) = vCats(iRow, iCol)
        Exit For
      End If
    Next
  Next
  
  GetCategoryLabels = vOutput
End Function

It’s called in a similar way:

Sub TEST_GetCategoryLabels()
  Dim v As Variant, i As Long, s As String
  Dim cht As Chart, iSrs As Long, iPt As Long
  
  Set cht = ActiveSheet.ChartObjects(1).Chart
  iSrs = 1
  iPt = 3
  
  v = GetCategoryLabels(cht, iSrs, iPt)
  
  s = v(LBound(v))
  For i = LBound(v) + 1 To UBound(v)
    s = s & ", " & v(i)
  Next
  
  s = "Category Labels for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
  
  MsgBox s
End Sub

When we put all the category fields into the rows area of the pivot table, we get three labels for Series 1 – Point 3, “Alpha”, “A”, and “III”:

When we put the fields “Greek” and “Latin” into the rows area of the pivot table and move “Roman” to the columns area, we get two labels for Series 1 – Point 3, “Alpha” and “C”:

When we leave the field “Greek” in the rows area of the pivot table and put “Latin” and “Roman” to the columns area, we get a single label for Series 1 – Point 3, “Gamma”: