The method of least squares is a method we can use to find the regression line that best fits a given dataset.

The following video provides a brief explanation of this method:

To use the method of least squares to fit a regression line in Excel, we can use the =LINEST() function.

The following step-by-step example shows how to use this function in practice.

Step 1: Create the Dataset

First, let’s create the following dataset in Excel:

Step 2: Use Method of Least Squares to Fit Regression Line

We can use the =LINEST(known_ys, known_xs) function to use the method of least squares to fit a regression line to this dataset:

Once we press ENTER, the coefficients of the regression model will appear:

Step 3: Interpret the Results

Using the coefficients from the =LINEST() function, we can write the following fitted regression line:

y = 11.55211 + 1.07949(x)

We can use this equation to estimate the value of y based on the value of x.

y = 11.55211 + 1.07949(10) = 22.347

Step 4: Plot the Results

Lastly, we can use the following steps to plot the dataset along with the fitted regression line:

• Highlight cells A2:B16.
• Click the Insert tab along the top ribbon. Then click the first chart option titled Insert Scatter (X, Y) or Bubble Chart in the Charts group.
• Once the chart appears, click the plus “+” sign in the top right corner. In the dropdown menu, click the checkbox next to Trendline to add the fitted regression line to the chart.