How can I create a linear regression in Excel?

A linear regression is a data plot that graphs the linear relationship between an independent and a dependent variable. It is typically used to visually show the strength of the relationship, and the dispersion of results – all for the purpose of explaining the behavior of the dependent variable.

Say, as a simple example, we wanted to see test the strength of the relationship between amount of ice cream eaten and obesity. We would take the independent variable, the amount of ice cream, and relate it to the dependent variable, obesity, to see if there was a relationship. Given a regression is a graphical display of this relationship, the lower the variability in the data, the stronger the relationship, and the tighter the fit to the regression line.   

There are a few critical assumptions about your data set that must be true in order to proceed with a regression analysis:

1. The variables must be truly independent (using a Chi-square test).
2. The data must not have different error variances (this is called heteroskedasticity).
3. The error terms of each variable must be uncorrelated. If not, it means the variables are serially correlated.

If those three things sound complicated, well – they are. But the effect of one of all of those considerations not being true is a biased estimate. Essentially, you'd be misstating the relationship you are trying to measure.

The first step in running regression analysis in Excel is to double-check that the free Excel plugin Data Analysis ToolPak is installed. This plugin makes calculating a range of statistics very easy. It is not required to chart a linear regression line, but it makes creating statistics tables simpler.   

Using the Data Analysis ToolPak, creating a regression output is just a few clicks. Remember that the independent variable goes in the X range.

Say we want to know, given the S&P 500 returns, if we can estimate the strength and relationship of Visa (V) stock returns.

Go to the Data Tab —> Data Analysis, and run the result:

[Note: If the table seems small, right-click image and open in new tab for higher resolution.]

Using that data (the same from our R-squared article), we get the following table:

We can chart a regression in Excel by highlighting the data and charting it as a scatter plot. Apply some formatting, and the visual result sums up the strength of the relationship, albeit at the expense of not providing as much detail as the table above.