chart data

If you select a well-defined worksheet range and insert a chart, Excel parses the range and assigns values (Y values), categories (X values), and series names based on its analysis of the range.

For example, if you select the range C2:F8 shown below, Excel notices that the top left cell C2 is blank, so Row 2 and Column C will be treated differently. Excel also notices that there are more rows than columns, so the data will be plotted by column, with categories in the first column and series names in the first row. When you insert a chart, the worksheet is highlighted showing that Column C (purple highlights) is used as category labels (X values), Row 2 (red highlights) is used as series names, and the rest of the area (blue highlights) is used as Y values.

If you always arrange your data nicely like this, you will rarely have to fix up what Excel uses for X values, Y values, and series names.

Assign Names from Cells in Front of Y Values

But sometimes your chart hasn’t been no nicely assigned to the worksheet range. The chart below has series plotted by row, but the first column has not been used for series names. Instead the series have the dreaded “Series1”, “Series2”, etc., as their names.

Similarly, this chart has series plotted by column, but the first row has not been used for series names. Again, the series are labeled with the dreaded “Series1”, “Series2”, etc.

You can manually name the series, using the Select Data command from the ribbon or from the right click menu, or editing the series formula. But it’s not too much trouble to write a little code to find the appropriate cells to name the series in a chart.

I’ll start with a routing that works on one chart series. It gets the series formula, parses out the argument that specifies the series Y values, and finds the range containing those values. (If the Y values are not from a range, for example, they are hard-coded as an array right in the formula, the code skips the series.) If the Y values range is a row, the code finds the cell to the left of this range in the same row; if the Y values range is a column, the code finds the cell above this range in the same column. This cell is used to create a link for the series name.

Sub Series_AssignNameToCellBeforeYValues(srs As Series)
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sYVals As String
  Dim rYVals As Range
  Dim rName As Range
  
  sFmla = srs.Formula
    ' e.g. =SERIES("Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)
  sFmla = Replace(sFmla, "=SERIES(", "")
  sFmla = Left$(sFmla, Len(sFmla) - 1)
    ' e.g. "Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1
  vFmla = Split(sFmla, ",")
  
  If UBound(vFmla) + 1 - LBound(vFmla) = 4 Then
    sYVals = vFmla(LBound(vFmla) + 2)
      ' third element (first + 2)
      ' e.g. Sheet1!$B$2:$B$10
    On Error Resume Next
    Set rYVals = Range(sYVals)
    On Error GoTo 0
    If Not rYVals Is Nothing Then
      If rYVals.Cells.Count > 1 Then
        On Error Resume Next
        If rYVals.Columns.Count > rYVals.Rows.Count Then
          ' by row, take cell to left
          Set rName = rYVals.Resize(1, 1).Offset(, -1)
        Else
          ' by col, take cell above
          Set rName = rYVals.Resize(1, 1).Offset(-1)
        End If
        On Error GoTo 0
        If Not rName Is Nothing Then
          srs.Name = "=" & rName.Address(, , , True)
            ' e.g. "=Sheet1!$B$1"
            ' use formula notation so it links to cell
        End If
      End If
    End If
  End If
End Sub

That’s great for one series. Use the following code to call the above procedure for each series in a given chart:

Sub Chart_AssignNameToCellBeforeYValues(cht As Chart)
  Dim srs As Series
  For Each srs In ActiveChart.SeriesCollection
    Series_AssignNameToCellBeforeYValues srs
  Next
End Sub

Use this to assign names for the active chart:

Sub ActiveChart_AssignNameToCellBeforeYValues()
  If Not ActiveChart Is Nothing Then
    Chart_AssignNameToCellBeforeYValues ActiveChart
  End If
End Sub

Use this to assign names to every chart in the active sheet:

Sub AllCharts_AssignNameToCellBeforeYValues()
  Dim chtob As ChartObject
  For Each chtob In ActiveSheet.ChartObjects
    Chart_AssignNameToCellBeforeYValues chtob.Chart
  Next
End Sub

Here is our first chart, plotted by row, with its series names now defined: note the red highlighted range containing the same labels now displayed in the legend.

Here is the second chart, plotted by column, with names assigned.

In fact, the alignment of all series need not be the same, since the routine works series-by-series. The top chart below uses the first two rows of data from the first chart above and the last two columns from the second chart, with no series names assigned. The bottom chart shows how the code has correctly assigned names.

Assign Names from an Arbitrary Range

What if the series names are in a range that isn’t connected to the rest of the chart data? Here I want to use series names from the range below the chart. The highlights in the worksheet only show X and Y values for the selected series.

Here is a routine that assigns names from a given range to series in a given chart:

Sub Chart_AssignNamesFromRange(cht As Chart, rng As Range)
  Dim iSrs As Long
  For iSrs = 1 To cht.SeriesCollection.Count
    If iSrs > rng.Cells.Count Then Exit For
    cht.SeriesCollection(iSrs).Name = _
        "=" & rng.Cells(iSrs).Address(, , , True)
  Next
End Sub

This routine asks the user to select a range, then calls the routine above to assign names from this range to the active chart:

Sub ActiveChart_AssignNamesFromRange()
  Dim myRange As Range
  On Error Resume Next
  Set myRange = Application.InputBox( _
      "Select a range containing series names for the active chart.", _
      "Select Range", , , , , , 8)
    ' Type 8 to input a range
  If Not myRange Is Nothing Then
    Chart_AssignNamesFromRange ActiveChart, myRange
  End If
End Sub

Here is the dialog, which I’ve already used to select a range containing labels.

Here the chart’s series have been labeled. The highlights show X and Y values and a series name for the selected series.