Change Series Formula – Improved Routines

Ever make a copy of a chart, and all you want is do is change the worksheet containing the source data? Or change the X values of all series from column A to column B? It sure is a pain to edit each series’ source data range of series formula one by one.

The series formula is a simple text string, but there’s no Search and Replace feature in Excel that can access these formulas. But you can use some very simple VBA code to make wholesale changes to chart series formulas. Or you can skip all of the noise, scroll to the end of this article, and download the new Change Series Formula Utility.

About the Chart Series Formula

A more detailed description of a chart and its series formula is presented in The Chart Series Formula and How to Edit Series Formulas elsewhere on this site, but a brief description here is in order.

Every chart series has a formula which describes the data in the series. The simple chart in this example is shown below its data range; the series is selected to highlight the data used to plot the series and to show the SERIES formula in the formula bar.

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

This formula can be broken up into four elements as follows:

=SERIES([Series Name],[X Values],[Y Values],[Plot Order])

In our example:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

Sheet1!$B$1 contains the Series Name

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

Sheet1!$A$2:$A$11 contains the X Values or Category Labels

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

Sheet1!$B$2:$B$11 contains the Y Values

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

and the series is plotted first (1) among the chart’s series collection.

The Series Name can be blank, a text string in double quotation marks, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The X Values can be blank, a literal array of numeric values or text labels enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Y Values can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Plot Order can only be a whole number between 1 and the number of series in the chart.

Editing the Series Formula

The series formula is a text-based Excel formula like any other. You can click in the formula bar, and edit the formula manually, to change aspects of the charted series. Select part or all of the formula, type in the text you want there instead, and press Enter to apply the changes (or sometimes to cause an error!). Alternatively you can select one range address in the formula, then click and drag with the mouse to insert the address of another range in the worksheet.

But if you have to do this for every series in your chart, and every chart on the worksheet, you will be editing in the formula bar all day. So let’s make VBA do some of the tedious work for us.

Editing the Series Formula with VBA

The meat of this technique involves this very simple VBA command:

Series.Formula = WorksheetFunction.Substitute(Series.Formula, OldString, NewString)

Basically, Substitute works by replacing every instance of OldString in the input text string (Series.Formula) by NewString. You could use Replace instead of WorksheetFunction.Substitute, but this would not be compatible with Excel 97.

There are a few tricks I’ve learned to improve the reliability of this technique. First, OldString should be entered using the same case as is found in the formula. For example, “b” will not find any references to column B in the formula. Second, to make sure you don’t trash any worksheet name in the formula, use “$C$” rather than “C” if you are replacing references to column C.

In the simplest version of this utility, you can use a couple input boxes to ask the user for OldString and NewString. A little error trapping makes sure that there is an active chart and that OldString is valid.

Program to Modify the Active Chart

Sub ChangeSeriesFormula()
    ''' Just do active chart
    If ActiveChart Is Nothing Then
        '' There is no active chart
        MsgBox "Please select a chart and try again.", vbExclamation, _
            "No Chart Selected"
        Exit Sub
    End If

    Dim OldString As String, NewString As String, strTemp As String
    Dim mySrs As Series

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString= InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        '' Loop through all series
        For Each mySrs In ActiveChart.SeriesCollection
            strTemp = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
            mySrs.Formula = strTemp
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

Program to Modify All Charts on the Active Sheet

Adding another loop to iterate through a sheet’s chart objects will change every chart on the sheet.

Sub ChangeSeriesFormulaAllCharts()
    ''' Do all charts in sheet
    Dim oChart As ChartObject
    Dim OldString As String, NewString As String
    Dim mySrs As Series

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString = InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        For Each oChart In ActiveSheet.ChartObjects
            For Each mySrs In oChart.Chart.SeriesCollection
                mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
            Next
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

How the program works

Here is our chart again, bet we’ve got an extra column of data. The chart shows column B plotted, but we want to plot column C instead.

Using the second version of the program above, all we need to do is run the code. I usually like to select one chart’s series, to remind me what string I want to change.

The program’s first input box asks for the string we want to replace ($B$ for column B):

The second input box asks for the string we want to replace that string with (“c”):

We see that the program has changed the series formula, while the chart now highlights the new range and shows the new data.

Warning: Blank Line or XY Series

When a line or XY series has no plottable points (that is, the data range has no numerical values to plot), VBA can’t read the formula. This example shows the problem.

We have no trouble changing the chart’s Y values from column B

to column C

even though column C has no data to plot. The chart merely looks blank, and we cannot select the series.

When we try to change from column C

to column D

Excel chokes, because it can’t read the series formula we want it to change.

Excel can’t read the formula for a line or XY series with no data, but fortunately it can read other types, so we can do just fine if we change the series temporarily to a column chart, edit the series, and change the series back. Here’s the code:

Sub ChangeSeriesFormulaAllCharts()
    ''' Do all charts in sheet
    Dim oChart As ChartObject
    Dim OldString As String, NewString As String
    Dim mySrs As Series
    Dim iChartType As XlChartType
    Dim sFormula As String

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString = InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        For Each oChart In ActiveSheet.ChartObjects
            For Each mySrs In oChart.Chart.SeriesCollection
                sFormula = ""
                On Error Resume Next
                sFormula = mySrs.Formula
                On Error GoTo 0
                ' change to column chart if series is inaccessible
                If Len(sFormula) = 0 Then
                    iChartType = mySrs.ChartType
                    mySrs.ChartType = xlColumnClustered
                End If
                mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
                If Len(sFormula) = 0 Then mySrs.ChartType = iChartType
            Next
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

The intermediate chart still looks blank, but if we are attentive we might notice the horizontal axis has changed from the typical XY value-style axis to a typical column category-style axis.

After the series is changed, the columns reflect the new values.

Finally, the chart is reverted to its original XY style.

Warning: VBA Error

If the X value argument of a series formula consists of a Name instead of an array or a range address, VBA incorrectly reads the series formula, by enclosing the Name in single quotes. When assigning a formula to a series with a Name as its X value argument, VBA fails whether or not the erroneous single quotes are included. This is apparently a bug in Excel VBA, and it has been reported to Microsoft. The utility below has been upgraded with a workaround to avoid this error.

Essentially the workaround involves checking for a Name in the X values argument of the formula, temporarily removing the argument altogether from the new series formula, then reassigning the Name using the Series.XValues property.

The Change Series Formula Utility

I’ve updated the utility to take into account the funny VBA behavior when Names are used in the formula, and the problem with XY and Line charts that have no data to plot. The updated utility is located in PTS_ChangeSeriesFormula.zip. You can install it following the protocol in Installing an Excel Add-In.

Upon installation, the utility creates a toolbar (In Excel 2007, the toolbar buttons are buried on the Add-Ins tab of the ribbon).

This utility incorporates all of the editing code. In addition, it replaces the separate old and new text input boxes with a single dialog with two text boxes. It also has two buttons, which based on what is selected and active, allows you to change the active chart, change any charts which you’ve selected using Ctrl+Click, or change all charts on the active sheet. It also remembers the inputs in the two text boxes, so you can reuse them for charts on another worksheet. Here is the Change Series Formula dialog:

Try this new utility, and please let me know if it doesn’t work in the comments below.

Related Posts:

• How to Edit Series Formulas
• Make Your Recorded Macro Independent of Which Sheet is Active
• UDF to Calculate an Arbitrary Formula
• Dynamic Chart Source Data
• Make a Copied Chart Link to New Data
• Chart Series Data Highlighting