Peltier Tech Excel Charts and Programming Blog

A couple months back I wrote Add One Trendline for Multiple Series which shows how to add a trendline to a chart, and have the trendline calculated for multiple series in the chart. In fact, that tutorial was based on my answer to a question on Quora, How can I have multiple scatter plots and one trendline for all of them combined in Excel? Some Quora questions can be kind of lame, but this was a good one, especially if I’m getting a second blog post out of it.

Feedback on that tutorial was positive, but it seems that people would like the process to be faster and simpler. Fair enough.

So I decided to write a small add-in that automates the process.

The Manual Process

If you recall, the original problem was that we had three series of data in the chart, and we can easily get a trendline for any or all individual series, but we want a trendline that covers all points in all three series. You can download a workbook with my dummy data and charts here: MultiScatterTrendlineData.xlsx.

Note: this approach will not work in Line charts; in general you should not use Line charts if you need trendlines for your data.

Here is the original chart from the earlier tutorial:

And here is the chart with a trendline for each individual series:

We created a new series in the chart that included all points from the first three series (the yellow markers cover the blue, orange, and green ones):

This was the tedious step, adding all the data to a new series, and this is the part that my add-in will speed through.

Then we hid the new series by formatting it without markers, and added a trendline:

The VBA Code

The three original series in the chart have these formulas:

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11,1)
=SERIES(Sheet1!$E$2,Sheet1!$D$3:$D$11,Sheet1!$E$3:$E$11,2)
=SERIES(Sheet1!$G$2,Sheet1!$F$3:$F$11,Sheet1!$G$3:$G$11,3)

Remember, a series formula has four arguments (a bubble chart series has a fifth argument, but we’ll ignore bubble charts here):

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

We’ll give our added series a new name, “Combined”, and it will automatically be 4th in the plot order. In between we will combine the X values and Y values of the original three series. Our constructed series formula looks like:

=SERIES("Combined",
    (Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11),
    (Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11),
    4)

The multiple X value ranges are enclosed in parentheses, as are the multiple Y value ranges.

What our code will do is count the series in the chart, read each series formula in turn, split out its arguments, and concatenate the separate X and Y values into combined X and Y values. The code will then add the new series, apply the arguments of the series formula, hide the markers, and add a trendline.

Here is the simple procedure:

Sub ComputeMultipleTrendline()
  If Not ActiveChart Is Nothing Then
    With ActiveChart
      Dim ixSeries As Long
      For ixSeries = 1 To .SeriesCollection.Count
        Dim SeriesFormula As String
        SeriesFormula = ActiveChart.SeriesCollection(ixSeries).Formula
        SeriesFormula = Mid$(SeriesFormula, InStr(SeriesFormula, "(") + 1)
        SeriesFormula = Left$(SeriesFormula, Len(SeriesFormula) - 1)

        Dim SeriesArgs As Variant
        SeriesArgs = Split(SeriesFormula, ",")

        Dim XAddress As String, YAddress As String
        XAddress = XAddress & SeriesArgs(LBound(SeriesArgs) + 1) & ","
        YAddress = YAddress & SeriesArgs(LBound(SeriesArgs) + 2) & ","
      Next

      XAddress = "=(" & Left$(XAddress, Len(XAddress) - 1) & ")"
      YAddress = "=(" & Left$(YAddress, Len(YAddress) - 1) & ")"

      With ActiveChart.SeriesCollection.NewSeries
        .Name = "Combined"
        .XValues = XAddress
        .Values = YAddress
        .Format.Line.Visible = False
        .MarkerStyle = xlMarkerStyleNone
        With .Trendlines.Add.Format.Line
          .DashStyle = msoLineSolid
          .ForeColor.ObjectThemeColor = msoThemeColorText1
          .ForeColor.Brightness = 0
        End With
      End With
    End With
  End If
End Sub

If you want to run this code, open the VB Editor (easiest way: use the Alt+F11 shortcut), find your workbook in the Project Explorer, and insert a fresh module (Insert menu > Module, or simply Alt+N+M).

If the new module doesn’t say Option Explicit at the top, type it yourself, then go to the Tools menu > Options, and on the Editor tab of the dialog, check the box labeled Require Variable Declaration, and you may as well uncheck the box for Auto Syntax Check. I discuss why in a decade-old tutorial, VB Editor Settings.

Skip a line after Option Explicit in your brand new code module, then copy the code from above, and paste it into the module.

Before you run the code, select a chart. Then press Alt+F8 to open the Macros dialog. Select ComputeMultipleTrendline and click Run. In the blink of an eye, the new series is added, though it’s not visible, and the trendline appears. I used a solid black line, rather than the default dotted line Excel uses, because I think a solid line makes it easier to see.

It doesn’t matter if all series use the same or different X values; the code doesn’t even compare the X values of the different series, it just puts them all into the series formula.

The Multi Scatter Trendline Calculator

I used the code above as the basis for my add-in. I added a custom ribbon tab named Multi Trendline with a custom button labeled Multi Scatter Trendline to invoke the code. I also designed a dialog so that you can select which series in the chart to include in your analysis (and which to exclude).

Preparing to Install the Add-In

You can download the add-in from this link: MultiScatterTrendlineCalculator.xlam. The add-in is packaged in a zip file. Unzip the file, and store the add-in in the User Add-in Library, which is

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns\

where USERNAME is your Windows login. You can get there quickly by pressing Win+R (Win = Windows key), typing %appdata% in the Run box, and clicking OK, which opens the Roaming directory, and drilling down to Microsoft and then AddIns.

You can actually store the add-in in almost any convenient folder, but when you use the Add-In Library, it’s easy to find the add-in from within Excel when you install it.

Windows protects your computer from malicious software that came from a different computer than yours, but it also protects your computer from useful software that came from my computer, so you need to unblock the add-in. Right click on the add-in file in Windows Explorer, and choose Properties. At the bottom of the General tab of the Properties dialog, there may be a notice that the file may be blocked, and there is a checkbox to unblock the file.

Check the box, and click OK.

Installing the Add-In

If you have the Developer tab showing on Excel’s ribbon, go there and click on Excel Add-Ins (or if it’s an older version of Excel that has no Excel Add-Ins button, click on Add-Ins) to open the Add-Ins dialog.

Otherwise, click on the File tab > Options > Add-Ins. Click the Go button near the bottom of the list to open the Add-Ins dialog.

Or you can use the old Excel 2003 shortcut, Alt+T+I to open the Add-Ins dialog.

If you stored the add-in in the User Library, it will appear in the Add-Ins dialog as MultiScatter Trendline Calculator. Otherwise you will have to click Browse, then navigate to find the add-in.

Check the box in front of this entry, then click OK, and the add-in is installed, available whenever you run Excel.

If you don’t want the add-in installed all the time, you can simply start it when you need it, using File > Open in Excel, double clicking in Windows Explorer, or dragging it from Windows Explorer and dropping it on Excel.

Using the Add-In

Select a chart, then click the button on the custom ribbon tab.

The dialog pops up. Select which series you want to include, and click OK.

The series is added invisibly, and a trendline is added using Excel’s default settings. You can format this just like any other trendline, to change the fitting model used, to show the trendline formula on the chart, or to change the trendline’s formatting.

About the Add-In

I have left the add-in unprotected in case you want to see how it all works. There is XML code that handles the custom ribbon tab and button. There is code in a second module to handle clicks from the ribbon button. A UserForm (i.e., a dialog) has been added to get input from the user. The main procedure is more detailed than shown in this article, to accommodate this dialog, and to compile data selectively.

I enjoy doing this kind of project. Even with the ribbon components and the dialog, it only takes a few hours. If you need something like this done, send me your requirements and I’ll generate a quote.

I liked this little utility so much that I’ve added it to Jon’s Toolbox, which is a nifty set of tools for quickly manipulating charts and data for such purposes as teaching and preparation for publishing.

More Trendline and Regression Articles

• Trendlines and Chart Types in Excel
• Add One Trendline for Multiple Series
• Trendline Fitting Errors
• Choosing a Trendline Type
• Plot Two Time Series and Trendlines with Different Dates
• Polynomial Fit vs. Statistical Process Control
• Moving Averages
• Stacked Column Chart with Stacked Trendlines
• Deming Regression
• Deming Regression Utility
• LOESS Smoothing in Excel
• LOESS Utility for Excel