MS Excel allows us to run linear regressions pretty easily.Here are the steps to add the ‘Data Analysis’ toolpack for the version of excel you are using.
- First go to ‘Files’ and then select ‘Options’.This will open a window similar to the below screenshot. Select ‘Add-ins’ (highlighted in yellow).
- Select the Excel add-in as highlighted (in yellow) and press ‘Go’
- A pop-up window will open as below.Select the ‘Analysis toolpak’ and ‘Analysis VBA toolpak’ and click OK.
- Now open the ‘Data’ tab of your excel spreadsheet and go to the top right hand corner you will find a button for ‘Data Analysis’ , as highlighted below
- Now click on the ‘Data Analysis’ button and another pop up window will open.From the list of tools select ‘Regression’ as shown below and click OK.And that’s all!! You are ready to run a regression model using Excel.
- Now let’s discuss how to input the data and get the output using regression in Excel.I have downloaded a sample data from the internet as shown below.You can use any data set you want.The 30 year mortgage interest rate is my X variable (explanatory variable/predictor) and the Median home price is my Y variable (dependent variable).
- Now to run a regression on the data set, just select ‘regression’ from the drop down as already discussed.Then another pop-up window will open, similar to the one shown below.Now, we will just have to specify the input range for the Y variable which is basically the dependent variable of your model.After that, we will simply have to put the input range for the X variable which is the predictor.In case we are dealing with more than one X variable, we just need to select all the columns containing the X variables.While selecting the range if we are also including the label or header of the column then we can just tick the box ‘Label’ (as shown below).Also, if we want to see the residual plots we have options to generate them as well.Now, for the output we can select the option of ‘New worksheet’ and the output will be generated in a separate worksheet automatically.Alternatively, we can also specify a range in the same worksheet where the original data is taken from and get the output there.
- Now the output will look something like the below screenshot.It will give us the Adjusted R-square value and significance of the F-statistic to assess the overall model fit.Along with that it will give us the individual coefficient estimates along with the p values.Details of the statistical analysis will be discussed in a separate post.
- Hope you found this article helpful.Happy learning 🙂