Trend line between 2 points in a graph

Lasa1

New Member
Joined
Mar 31, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I would like to add a trend line between the 1st point K and last one Z.
The only option I find is to go to Chart Tools --> Add Chart Element --> Trendline-->Linear. However the linear trend does not start in K and does not end in Z.
Any idea?

Thanks!
Regards
Cecilia

1587656416976.png
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Do you want a trendline, or merely any old line connecting the points? A trendline gives a line of best fit, and in your case, the two middle points pulls this line away from the endpoints.

First of all, you probably want an XY Scatter chart, not a Line chart. The line chart plots each X axis item at an equal distance from its neighbors, so 10 is as close to zero as to 15. Here are the two different chart types:

trendline 1.png


You don't need the markers, I just like using them so I can easily see where the actual data points are in my chart.

Second, whether you want a trendline, with its attendant statistical content, or simply a connecting line, the easiest way is to add a series that uses just the values for points K and Z, without the two intervening points. Then either use a series format that connects the points with a line, or hide the points (no marker and no lines) and add a trendline.

You need a data range like this (below left), with the X and Y values for the points you want to add. Select the range and copy it, then select the chart, and go to the Home tab of the ribbon, click the little down arrow on the Paste button, and choose Paste Special. Select the options shown below right.

A new series is added, as shown at bottom left. You can use it as is, or you can use whatever line color and other format you need. Or if you do want an actual trendline, hide the added data points (format with no markers or line), and add a trendline, shown here with the default dotted line.

trendline 2.png
 
Upvote 0
Do you want a trendline, or merely any old line connecting the points? A trendline gives a line of best fit, and in your case, the two middle points pulls this line away from the endpoints.

First of all, you probably want an XY Scatter chart, not a Line chart. The line chart plots each X axis item at an equal distance from its neighbors, so 10 is as close to zero as to 15. Here are the two different chart types:

View attachment 12226

You don't need the markers, I just like using them so I can easily see where the actual data points are in my chart.

Second, whether you want a trendline, with its attendant statistical content, or simply a connecting line, the easiest way is to add a series that uses just the values for points K and Z, without the two intervening points. Then either use a series format that connects the points with a line, or hide the points (no marker and no lines) and add a trendline.

You need a data range like this (below left), with the X and Y values for the points you want to add. Select the range and copy it, then select the chart, and go to the Home tab of the ribbon, click the little down arrow on the Paste button, and choose Paste Special. Select the options shown below right.

A new series is added, as shown at bottom left. You can use it as is, or you can use whatever line color and other format you need. Or if you do want an actual trendline, hide the added data points (format with no markers or line), and add a trendline, shown here with the default dotted line.

View attachment 12227
That was exactly what I was looking for, thanks!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top