#Excel trendline for multiple series plus
#Excel trendline for multiple series series
#Excel trendline for multiple series download

'.XValues = '=Info!L26:е…ѓ1 Info!M26:M31 Info!N26:N31' ''Application-defined or object-defined error'.

'.XValues = '=testytest' ''Application-defined or object-defined error'.

#Excel trendline for multiple series series

Update: Trendline Calculator for Multiple SeriesДЇor 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. 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.

#Excel trendline for multiple series plus

Select this new series, click on the plus вЂњskittleвЂќ next to the chart, and check Trendline. When you press Enter, the chart has a new series that hides the old series, just like above. DonвЂ™t forget to end the formula with a comma, the Plot Order 4, and the closing parenthesis. You canВ hold Ctrl while selecting multiple areas, but I find it easier to type a comma between range addresses.

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. Note that you have to enter double quotes around the series name and parentheses around the multiple areas of the X and Y values.

If you select the chart area (just the outermost rectangle containing the chart), you can click in the Formula bar, and enter your formula. You donвЂ™t need to use the Select Data Source dialog to add data to a chart. We can see that it includes the X values and the Y values for the three original series. =Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11Д®xcel will put parentheses around these comma-separated addresses, and double quotes around the Series Name.Д¬lick OK twice, and a new series will appear, whose markers obscure those of the existing three series. The Series X Values and Series Y Values boxes contain The populated Edit Series dialog looks like this: Type another comma, and select the third range. Type a comma, and select the second range of Y values. 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.Д¬lick in the Series Y Values box, delete the вЂњ=вЂќ that is there, then with the mouse select the first range of Y values. Type a comma, and select the second range of X values. вЂњCombinedвЂќ works.Д¬lick in the Series X Values box, then with the mouse select the first range of X values. Right click on the chart and click on Select Data from the pop up menu.Д¬lick the Add button, and the Edit Series dialog appears.Д¬lick in the Series Name box, and add a descriptive label. There are at least two ways to get this 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. The numbers at the end are the plot order of each series. 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). But itвЂ™s no big deal to get what they wanted.ДЇirst, letвЂ™s just check out the chart. Well, this is not what the person wanted to know. 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. It has multiple trendlines, not a multiple-trendline.

Of course you can add a trendline to each of the series, and you get this cluttered mess.

#Excel trendline for multiple series download

You can download a workbook with my dummy data and charts here: MultiScatterTrendlineData.xlsx. I made up some dummy data, and generated this XY chart.

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?вЂќ