See also: Our Basic Statistics page and Our Histograms Page

1. In my example, I will use 10 ordered pairs: (1,2) (3, 4) (2, 5) (1, 4) (4, 6) (3, 3) (5, 10) (6, 9) (4, 7) (5, 12). Type the x-values in column A ,starting at A1 and pressing ENTER after each value. Now type the y-values in column B, starting at B1 and pressing ENTER after each value.
2. Select both columns of your data. For me, I select A1:B10.
3. Press the chart wizard button (the one the looks like a graph.
4. Choose XY (Scatter). It should be the fifth one in the list. Press NEXT.
   1. Step 2: Choose NEXT.
   2. Step 3: Type in titles if wanted. Click off "Show Legend".
   3. Step 4: Choose "As object in" (or don't, your call.)
5.  After the chart has been created, click on the Chart menu and select Add Trendline. (Trendline is another word for regression line.) The first option is the linear regression line, which is what we want.
   1. Click on the options tab.
   2. Click on the display equation on chart box and the display R² value on chart box.
      1. The regression equation is the line that is "closest" to your data. The R² value tells you what proportion of the differences in the y-values can be explained by differences in the x-values.
   3. If you want, you can forecast forward and backward one or two units. It makes the line look a little nicer. I forecast forward and backward one unit.
   4. Click okay. The equations can be moved to a more aesthetically pleased location.

1. Change the last ordered from 5, 12 to 5, 40. How does this change the line? How does this change the R² value? What does this say about the effect of outliers?
2. Change the fifth ordered pair from 4,6 to 4,8. How does this change the line? Why is this situation different from that in question 1?
3. Change two of the y-values. See if you can get R² over 0.80. How high can you get it?

For more information, visit the Microsoft Excel webpage.
They have a separate page on scatterplots.

Click here to go back to the home page.

Go to: