A member of the online forum Quora asked, How can I have multiple scatter plots and one trendline for all of them combined in Excel? I interpreted this to mean, “I have multiple scatter series in my chart, how do I get a trendline for the combined data in the chart?”

I made up some dummy data, and generated this XY chart.

You can download a workbook with my dummy data and charts here: MultiScatterTrendlineData.xlsx.

Of course you can add a trendline to each of the series, and you get this cluttered mess. It has multiple trendlines, not a multiple-trendline.

Note: In a recent version of Excel (I don’t recall if it was 2013 or 2007), trendlines changed from black to the color of the points, which was good for visibility, and they also became a dotted line, which was bad for visibility. I always change to a solid line.

Well, this is not what the person wanted to know. But it’s no big deal to get what they wanted.

First, let’s just check out the chart. The three series 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)

I’ve color coded the formula arguments to show series names in red, series X values in purple, and series Y values in blue (the colors of the series highlights in the worksheet). The numbers at the end are the plot order of each series.

What we need to do is add a series to the chart that uses all of these X values and all of these Y values. There are at least two ways to get this series.

## Select Data

Right click on the chart and click on Select Data from the pop up menu.

The Select Data Source dialog appears.

Click the Add button, and the Edit Series dialog appears.

Click in the Series Name box, and add a descriptive label. “Combined” works.

Click in the Series X Values box, then with the mouse select the first range of X values. Type a comma, and select the second range of X values. Type another comma, and select the third range. Note: instead of typing a comma, you could hold the Ctrl key while selecting the second and third ranges, but I find that I make fewer errors if I type the comma and then don’t have to worry about how many buttons I’m holding with how many fingers.

Click in the Series Y Values box, delete the “={1}” that is there, then with the mouse select the first range of Y values. Type a comma, and select the second range of Y values. Type another comma, and select the third range.

The populated Edit Series dialog looks like this:

The Series X Values and Series Y Values boxes contain

`=Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11`

and

`=Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11`

Excel will put parentheses around these comma-separated addresses, and double quotes around the Series Name.

Click OK twice, and a new series will appear, whose markers obscure those of the existing three series.

The formula for the added series is:

`=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)

We can see that it includes the X values and the Y values for the three original series.

## Add Series Formula Directly

You don’t need to use the Select Data Source dialog to add data to a chart. If you select the chart area (just the outermost rectangle containing the chart), you can click in the Formula bar, and enter your formula. Note that you have to enter double quotes around the series name and parentheses around the multiple areas of the X and Y values. You can type the addresses of the individual names, which is inconvenient since you have to remember to include the sheet name and exclamation point; it’s easier to select the ranges with the mouse. You can hold Ctrl while selecting multiple areas, but I find it easier to type a comma between range addresses. Don’t forget to end the formula with a comma, the Plot Order 4, and the closing parenthesis.

When you press Enter, the chart has a new series that hides the old series, just like above.

## Add Trendline

Select this new series, click on the plus “skittle” next to the chart, and check Trendline.

Now format the “Combined” series to hide it (no lines or markers), and format the trendline to enhance visibility and to display the trendline formula and R² value.

## Update: Trendline Calculator for Multiple Series

For anyone who wants to apply this technique but finds it tedious to add a series with all the X and Y ranges, I’ve built a small utility that does the heavy lifting for you.

It’s a simple Excel add-in that lets you select a chart, choose which series in the chart to include (and exclude), and it builds a hidden series with a visible trendline combining all series that you’ve selected.

In the article I show some simple code that compiles the data into a new series. The add-in is free and unlocked, so you can look into its inner workings: the code, the dialog (UserForm), and the custom ribbon elements.

See Trendline Calculator for Multiple Series for information.

## Trendline Articles on this Blog

- Trendline Calculator 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