Place straight line onscatterplot

Last Edited By Krjb Donovan
Last Updated: Mar 11, 2014 03:24 PM GMT

Question

I've created a scatter plot on excell (2007) and now need to add my own line. I know i can add a linear regression line, but i need to add my own line with a different slope and y intercept. How can i do this?

Answer

It is easy to add a straight line to a scatter plot. Since the line is straight you only need to define the end points anddoes the rest.

Just as an example, lets say your x-axis is scaled to go from 0 to 50 and you want to create a line that has a y-intercept of 2.5 and a slope of 0.1. Just find a couple of free columns where you can enter the data; let's say columns D and E are empty and thus available. Simply set D1 = 0, D2 = 50, E1 = 2.5 (the y-intercept), E4 = 0.1 (the slope), and in E2 enter the formula "=E4*(D2-D1) + E1". This value in E2 is actually the line end point y value, which you are computing based on the slope and the starting y value.

Now to add this line to your chart:

  • Right-click on your chart and select Select Data Source.
  • In the Select Data Source form click on the Add button (to create a new series).
  • Enter a name for the line in the Series name RefEdit box (example "My Line").
  • Use the Series X values RefEdit box to enter the range D1:D2 (selecting this range with the mouse), and the Series Y values RefEdit box to enter E1:E2 (should look like =Sheet1!$D$1:$D$2 and =Sheet1!$E$1:$E$2).
  • Click OK and OK.

That's it. Now try changing the slope or the y intercept and see how the new line changes.

Advertisement

©2017 eLuminary LLC. All rights reserved.