Least Squares Linear Regression With Excel

Sharing is caring!

Last Updated on March 7, 2022 by Jay

This tutorial will show you how to do a least squares linear regression with Excel using the example we discussed earlier. Check here to learn what a least squares regression is.

Sample Dataset

We’ll use the following 10 data pairs. Assuming the data is in a 2D space with x and y axis. The data point pairs represent the x and y coordinates in the 2D space.

x = [12,16,71,99,45,27,80,58,4,50]
y = [56,22,37,78,83,55,70,94,12,40]

Least Sqaure Formula

As a reminder, the problem we want to solve is to find a line that best represents/fits the given data points. In order to find that best line y = b + wx, we need to find the b and w values that minimize the sum of squared errors.

The following equation will solve the best b (intercept) and w (slope) for us:

Least Squares Linear Regression Implementation In Excel

Let’s enter the following values into Excel:

• Column B – x values
• Column C – y values
• Then, column D = x^2
• Finally, column E = x * y
• N = # of data points, 10 in our example
• Don’t forget to sum up all the above values in row 12

Now we just need to plug in the formulas to solve for w:

Then we can solve for b:

This means that the line we are looking for is: y = 31.82863 + 0.495051x

The Excel LINEST() Formula

Luckily, there’s an Excel formula that can do all the heavy-lifting for us! That is the LINEST() function.

NOTE – LINEST is an array formula that returns 2 values: the intercept b and the slope w. The correct way to enter this formula is:

1. Select 2 cells, B21 and C21 in our case
2. Enter LINEST(y_range, x_range)
3. Press Ctrl + Shift + Enter together

We get both the intercept and slope terms, and we can verify that they match our previous manual calculation above.

Plot Data And Regression Line In Excel

We can plot the data points as a scatter plot and even show the fitted regression line using the following steps:

1. Select range B2:C11
2. Insert -> Charts -> Scatter
3. Then select the Chart, on the top right corner there should be a green plus + sign. Click on it and check “Trendline”.

Additional Resources

Least Squares Linear Regression with An Example