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 SubIf 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
Bank says
Thank you for your useful Add-In.
I want to use this in my thesis, However, I don’t know which math theory or math model that you use to line new multiple trendlines. Is it root mean square error or another technique please give me a name.
Hope for your reply and sorry for my English grammatically.
Jon Peltier says
Bank –
Excel uses conventional least squares to calculate its trendlines, the same as it uses in its LINEST worksheet function (you can read about that here: https://support.office.com/en-ie/article/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d).
Jannik says
Hi Jon,
thanks for the supplied Add-In!
However, when run it, throws me a “Run-time error ‘1004’ – Application-defined or object-defined error”. The debugger points to this line “.XValues = XAddress”.
Can you help me out? I’m using the most recent Excel for Office 365.
Thank you very much,
with kind regards,
Jannik
Jon Peltier says
Jannik –
I can’t tell what’s wrong without looking at your chart and data. Could you upload your file to a file sharing site, or email me the file?
Jon Peltier says
Jannik sent me his data, and I found the issue. All of the X values in a series and all of the Y values in a series have to be on the same worksheet, though the X and Y values can be on different worksheets. Jannik’s data had data for each series on a separate worksheet, so it could not be combined so easily.
Patrick says
Jon, I have downloaded this add-on but it didn’t create the Custom ribbon so I don’t know how to launch it when I select the chart. I am using excel version 2007. Yea, I know I should upgrade.
Jon Peltier says
Patrick –
Follow installation instructions here:
Install an Excel Add-In
Make sure you unblock the add-in file in Windows Explorer. (If you’ve installed the add-in without unblocking, you can unblock it without uninstalling, then restart Excel.)
Jon Peltier says
Patrick told me that he uses Excel 2007. Microsoft ended support for Excel 2007 in October of 2017, and I stopped supporting Excel 2007 in my add-ins soon thereafter.
What Patrick could do (aside from updating Excel) is to customize the Quick Access Toolbar, adding a button for the macro called ComputeMultipleTrendline.
Mary says
Is there a way of removing the combined data label from the legend?
Jon Peltier says
Mary –
Click once to select the legend, then click again to select the legend entry you don’t want, and press the delete key.
Red1 Kessa says
thanks bro for this great work
awesome
Emily Tauber says
this is amazing and so helpful! You have saved me a lot of time and headaches. Very grateful for you taking the time to create this and share it
Keaton says
Hey Jon,
I have 5 scatter plots that I’m trying to get a trendline between. Each trendline contains XY data on a separate sheet in the same workbook, with the chart being on it’s own sheet. I am having trouble getting the tool to work to combine this data, also struggling to manually select a new group of all the data, its simply only grabbing my last selected data column even with the CTRL and comma trick. I can manually copy/paste each dataset into one sheet to get started, however, I have approx. 10 of these workbooks to do, this tool would really help! Thanks.
Jon Peltier says
Keaton –
The trendline calculator builds one large large series in the chart, combining the ranges for all of the X values into one set of X values, and all of the Y values into one set of Y values. But if the X or Y values are not contained on a single worksheet, these ranges cannot be combined into a single series in a chart. You will have to get all of the data onto a single worksheet for the trendline calculator to work.
Jon Peltier says
It would be possible to modify my routine. Instead of joining the separate X and Y ranges into two large ranges, you could extract the corresponding arrays of X and Y values, combine them into two large arrays, and use these arrays to plot the master series. This would take me a couple hours to do, and unfortunately I can’t spare the time right now.
Teresa says
THANK YOU SO MUCH!!! Directions were SO easy to follow and this saved me so much time on my BiologyInternal Assessmemt for IB Biology.
Alex says
Just discovered it. Legendary.
thanks mate !
The last fine tuning would be to remove the combined Serie from the legend.
Jon Peltier says
Easy enough to do: single click to select the legend, then single click to select the single legend entry, then click Delete.
Izaak says
Thank you for this! I have successfully used the addin. However, when I use it in a chart, the master trendline goes to infinity, making it impossible to use the chart. How do I make sure that the trendline is to scale within the boundaries of the chart?
Jon Peltier says
Glad it helps.
Is it because the Y-axis is scaling automatically? You can double-click on the Y-axis, and in the task pane that appears, you can enter preferred limits on the scale.
If that’s not it, give me another clue and I’ll keep trying to solve it.
Dave says
Hi Jon,
Thanks for all your help and support with this tool. It seems fantastic, but I keep getting the same error that Jannik reported. However, all my data is pulling from the same work sheet, so I’m not sure exactly what is causing the error. When I debug, VBA highlights the “With ActiveChart.SeriesCollection.NewSeries” line near the very bottom. I dig a bit of digging around on various help forums, and nothing that others have suggested seem to help me (there’s no empty cells, no empty series, I’ve selected only the cells that contain data rather than referring to entire columns when building the pivot table/chart). I’m not sure what else to do. If it helps, I’m using Office Pro Plus 2019 on Windows 10 version 1909.
Colin says
Hi, Jon–
Thanks for this excellent tool. I cannot get it to work correctly with a moving average trendline. When I combine two series (using either the add-on or the manual process you describe), I get three separate moving averages. When I switch to any other trendline option, I get the one trendline as expected. No matter what I try, I cannot get a combined moving average. Any idea why, or how to fix?
Cheers,
Colin
Jon Peltier says
Hi Colin –
Most trendline options compute a trendline using algorithms that do not care what order the points are in. The approach I used to combine multiple series into one series for the global trendline does not sort the data, it merely appends the data together: series 1 values, series 2 values, etc.
A moving average is calculated according to the order of the points. When my algorithm combines points, the moving average looks like the series 1 moving average, then it averages the last points of series 1 with the first points of series 2, then it does the same with series 2 and 3, etc.
To make a moving average work, you would have to decide how to order your points (probably by sorting the X values, or more likely dates, since I don’t recall using a moving average like this for XY data). You’d also have to decide how many points to average: suppose you had a three-point moving average for each of three series, would you still want a three-point combined moving average, or would you want a nine-point moving average, or something else?
Chase says
Jon,
This tool really is a lifesaver. However, there is one graph that it for some reason does not work on, despite, to the best of my knowledge, the graph being formatted identically to the other ones. Somehow it is calling all the right cells that have the values in them, but they don’t get plotted. There are about 14 different data sets on the graph and it is only using 4 of them to get the trendline. If you try combining anything other than the 4 random ones it likes, it will just not plot anything.
Again, this is one of 7 graphs in the workbook generated in the exact same way just with 14 different data sets of identical length. It is the only one that doesn’t work to combine the 14 different sets in however way I choose.
Best,
Chase
Jon Peltier says
Chase –
I just tried this; my chart has 14 series, and I chose four of them to include in the regression. The SERIES formula of the combined series correctly includes the four chosen series, and the regression looks good.
Is it possible to share the workbook with the various charts?
Jon Peltier says
Chase sent me his workbook, and I discovered an issue with one of the series in the chart. The X values only had one cell:
=SERIES(“.01-.02”,Gauges!$DJ$2,Gauges!$DK$2:$DK$121,2)
When I changed the formula:
=SERIES(“.01-.02”,Gauges!$DJ$2:$DJ$121,Gauges!$DK$2:$DK$121,2)
the trendline calculator worked fine.
Stanislav says
Hi Jon, thanks for a cool tool. Unfortunately, it doesn’t work as supposed to in my case. I double and tripple checked the data, but it always comes down to this – even though the “Combined” series is comprised of all the series’ values, only values from one of the series are plotted and therefore the trendline is plotted for just those one series’s values and not al of them combined. Would you look at the sample data and tell me what is wrong? https://www.icloud.com/iclouddrive/0d0iKI1mbh4fRa7ZFGs6An8Kg#sample_data
Thank you!
Jon Peltier says
Stanislav –
This technique works great with XY Scatter charts but has problems with Line charts. Tha major cause of the problem is that all series in a line chart use the same X values; the combined trendline uses the X values of the first series (just one set of the repeated dates), and since it runs out of X values, it only uses the first set of Y values.
In general, you should not be using Line charts for data you want trendlines for, even if the series have the same number of points and the same X values.
Stanislav says
Jon –
I found a neat workaround – I created a Scatter chart with smooth lines and it works exactly as I need with the combined trendline. Thank you for your answer.
Jon Peltier says
Good plan. My only suggestion is not to use smooth lines, which obscure the location of actual data points and which may even distort data.