How to Perform Linear Regression in an Excel Spreadsheet

There are three values you normally need when performing a linear regression: the slope, the Y-intercept and the R2 value. Both the slope and the Y-intercept are contained in the regression equation. Each of these, as well as the equation, are displayed when you create a Trendline in Excel 2013.

Step

Enter your data in an Excel worksheet in two columns.

Enter your data.
credit: Screenshot courtesy of Microsoft.
Create a Scatter Chart.
credit: Screenshot courtesy of Microsoft.

Step

Select the data by dragging the cursor over the cells. Click the Insert tab and then click the Scatter Chart icon in the ribbon's Charts group. Select the first Scatter Chart in the drop-down menu. A chart based on your data appears in the worksheet.

Select Add Trendline.
credit: Screenshot courtesy of Microsoft.

Step

Right-click any data point in the chart. Select Add Trendline from the drop-down menu.

A regression line is displayed in the chart.
credit: Screenshot courtesy of Microsoft.

Step

Examine the trendline that now appears on the graph. This can also be called a regression line.

Click the Display Equation on Chart check box.
credit: Screenshot courtesy of Microsoft.

Step

Click the Display Equation on Chart check box. The formula that appears on the chart is in the regression equation, in the form Y = Bx + A , where:

Step

  • Y is the predicted score of any x value.
  • B is the line's slope.
  • A is the Y-intercept.

Step

In the example shown here, the slope is 0.425. The Y-intercept is 0.785.

Click the Display R-squared Value on Chart check box.
credit: Screenshot courtesy of Microsoft.

Step

Click the Display R-squared Value on Chart check box. ” The R2 value appears on the chart. In this example, the R2 value is 0.3929.

Step

The R2 value is always a number between 0 and 1. The higher the value, the more accurate the regression equation is. If the R2 value is 0, there is no linear relationship between the coordinates. If the R2 value is 1, the regression line would be completely accurate.

Copy and paste the values into the worksheet.
credit: Screenshot courtesy of Microsoft.

Step

Copy and paste the regression formula and the R2 value into your Excel worksheet, if desired. To do this, click the Equation's Text Box to select it, then drag the cursor over the values you want to copy and paste them into the empty cells by pressing Ctrl-C and Ctrl-V. To remove the chart, click its border and press Delete.