How to Perform Linear Regression in an Excel Spreadsheet
Use Excel 2013 to perform a simple linear regression. Using a Trendline, Excel gives you the slope, Y-intercept and the R2 value.
Things You'll Need
- Data in Excel format
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.
Enter your data in an Excel worksheet in two columns.
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.
Right-click any data point in the chart. Select Add Trendline from the drop-down menu.
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. The formula that appears on the chart is in the regression equation, in the form Y = Bx + A , where:
- Y is the predicted score of any x value.
- B is the line's slope.
- A is the Y-intercept.
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. ” The R2 value appears on the chart. In this example, the R2 value is 0.3929.
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 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.