How to do regression in excel? (Simple Linear Regression)

Performing regression analysis in excel is a very easy task. Before going towards practical, we recall the concepts of regression. Let’s describe it briefly.

Regression is used for predictive analysis. It is used for finding the strength of predictors and forecasting an effect.

Its most common type is linear regression. The linear regression can be further divided into simple linear regression, multiple linear regression, and logistic linear regression.

You may have performed simple linear and multiple linear regression in programming languages like Python and R. The regression examples in python can be found in these links.

Today we are going to perform the regression analysis in excel.

Let’s start!

If you do not find “Data Analysis tool” in “Data” tab, then go to “File” menu and click on options. Go to “Add-Ins” and select “Analysis ToolPak”

After pressing OK, you will see this box. Select “Analysis ToolPak” add-in and press OK.

Now you will see “Data Analysis” option in your Data tab has appeared as shown in the below figure.

Click on “Data Analysis” tool. A box showing the list of available tools will be opened. Choose “Regression” from the list of analysis tools and press “OK. See this figure.

Now, starting from figure-1 again. On selecting “Regression” option from figure-1, you will see this.

Set the input value of X and Y by selecting relevant columns of values in the worksheet. Here we have selected A1: A21 and B1: B21.
“Set constant to zero”. This value is optional. The selection of this option will cause the regression line to start from zero.
Check “Labels”, if headings are available in your table.
You can select the output range to put the result on specific cells or select “New Worksheet Ply” to place the result in a new worksheet.
“Residuals” selection of this checkbox will tell you the difference between actual and predicted values. This checkbox is optional.
Press “OK” and see the regression analysis on a new worksheet.

After performing the regression, you will see this output.

See the summary output, we have got some results but what they mean. Let’s briefly explain them.

Multiple R: These are the Correlation Coefficient used to measure the relationship strength of variables. Larger value shows a strong relationship while ‘0’ means no relationship.
R Square: It shows the points falling on the regression line.
Adjusted R Square: It is adjusted for the independent variables.
Standard Error: It shows the precision of the regression analysis performed by us.
Observation: Shows the number of observations.

Now, we take a look at the second part of the output “ANOVA”. In this part, “Significance F” value shows the accuracy of the model. If the value is less than 0.05, the model is OK. If it is greater than 0.05, you will have to replace the value in order to get the accuracy.

Simple Linear Regression in Excel

In simple linear regression, there is one dependent variable i.e. interval or ratio, and one independent variable i.e. interval or ratio or dichotomous.

We can perform simple linear regression in excel. To do this, select the columns and go to Insert -> Graph -> Scatter