Regression in Excel
 Correlation

Insert your data into an Excel spreadsheet. (See Accessing Excel data from the computer lab) Insert a row at the top and add titles to the columns if necessary or desired.

Click on Tools  Data Analysis. Select Correlation. Click OK.

Position the cursor in the text box next to Input Range and use the mouse to select the columns containing your data. Check the box next to Labels if appropriate. Click the radio button next to Output Range and specify a cell in the upper right corner of an empty block of cells for the output. Click OK. Excel will output a table containing 4 cells. The value of r is the number in the first row, second column.
 Coefficients for the Least Squares Regression Line
 Insert your data into an Excel spreadsheet. (See Accessing Excel data from the computer lab) Insert a row at the top and add titles to the columns if necessary or desired.

Click on Tools  Data Analysis. Select Regression. Click OK.

Position the cursor in the text box next to Input Y Range and use the mouse to select the cells containing the values of the response variable. Follow the same procedure to select the cells for the explanatory variable. Check the box next to Labels if appropriate. Click the radio button next to Output Range and specify a cell in the upper right corner of an empty block of cells for the output. (You may want to check the boxes for Residuals and Residual plots at this time if you will need that information.) Click OK.

Excel outputs several tables. The first contains the values of r and r^{2} (among other things we don't need yet). The first column of the last two rows of the output contain the coefficients of the leastsquares reqression line. The first number is the intercept, the second is the slope.

Adding the LeastSquares Regression Line to a Scatterplot
 Insert your data into a spreadsheet and create a scatterplot (see Scatterplots).
 Click on the scatterplot to select it.
 Click on the Chart menu and choose Add Trendline.
 Choose Linear for Trend/Regression Type, then click on the Options tab at the top of the dialog box. Select Automatic under Trendline name . Check the boxes for Display equation on chart and Display RSquared value on chart if desired. Click OK.
Back to Main Excel Instructions page