Change Series Formulas.

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.

About the Chart Series Formula

A more detailed description of a chart and its series formula is presented in the The Chart Series Formula page 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. For example, a simple series formula looks like this:

=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 test-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.

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

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

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

The ChgSrsFmla.ZIP zip file contains an Excel add-in with a more advanced form-based version of this technique. he user has a choice of working on the active chart only, every chart on the active sheet, or on the subset of these charts which have been selected when the code is run.

The utility was upgraded on 1 August 2007 to avoid the VBA error described above.

To install this addin:

• Download the zip file by clicking on the link above
   
• Extract the file ChgSrsFmlaForm.xla to Excel's add-in library folder, usually found at
      C:\Program Files\Microsoft Office\OFFICE{xx}\Library
  where {xx} is 11 for Office 2003, 10 for Office 2002, and nothing for Office 2000
   
• Restart Excel, and open a new workbook if none is open
   
• Go to Excel's Tools menu, and select Add-Ins
   
• Check the box in front of Change Series Formula Add-In

A floating toolbar appears. If you hide the toolbar, you can go to the View menu > Toolbars, and retrieve it from the list.

Click the Change Series Formula button to use the add-in. Enjoy the utility, I use it every day.