# 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.