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:

- Select 2 cells, B21 and C21 in our case
- Enter LINEST(y_range, x_range)
- 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:

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