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 SubThat’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 SubUse this to assign names for the active chart:
Sub ActiveChart_AssignNameToCellBeforeYValues()
If Not ActiveChart Is Nothing Then
Chart_AssignNameToCellBeforeYValues ActiveChart
End If
End SubUse 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 SubHere 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 SubThis 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 SubHere 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.
, and I must admit I know very little about it.
