Linear Regression Analysis in Excel

Regression analysis is a statistical process for establishing connections between certain variables. For example, we have one dependent variable and we want to determine how much other independent variables affect it. That is how we get a model of interdependence, and we can use it to predict the dependent variable’s value in the future. Excel uses the linear regression model. We’ll show you how you can apply it in Excel 2013 and 2010.

After you’ve prepared data for analysis, you need to turn on the statistical analysis add-in.

You can do this by going to OptionsAdd-ins. 

excel add ins

Click the Go button.

Check the first option which is used for statistical analysis.

excel linear regression

Now click on the Data tab and then double-click the newly introduced button Data Analysis. Choose Regression.

regression analysis in microsoft excel

Now you need to select data for analysis. In the field where it says Input Y range, you should select a column or row with the history of the dependent variable values, the variable that is affected by other variables and whose future value you want to predict.

input x y range

In the field Input X range select fields with independent variables. You can select only one variable, and that is called a simple linear regression. In case you have several independent variables, you have to place them next to each other in the document and select them that way.

selection of variables in excel

 

Select Labels if your data have a mark (the first field with the variable name). Also choose where you want the results of the analysis to be shown (in a new sheet or new document). After clicking OK, the analysis results will appear. As you can see, all important parameters for the analysis are there.

regression analysis output