The least squares regression line is a commonly used statistical method for analyzing the relationship between two continuous variables. It is a technique that can be applied in Excel to find the line that best fits a given set of data points, allowing for the prediction of future outcomes based on past performance. In this article, I am sharing 5 effective ways to plot least squares regression line in Excel. Stay tuned!

In the following, you will find an overview of the least squares regression line in Excel.

**Table of Contents**hide

## What Is Least Squares Regression Line?

The least squares regression line is a statistical tool used to model the relationship between two variables. It is called “**Least Squares**” because the method involves finding the line that minimizes the sum of the squared differences between the observed data points and the corresponding predicted values of the line. The line of best fit represents the average trend in the data. It can be used to make predictions about future values of the dependent variable based on the independent variable.

## How to Plot Least Squares Regression Line in Excel: 5 Easy Ways

In the following, I have shared 5 simple examples to get the least squares regression line in Excel. Among them, the first method is using **Scatter Chart** to plot the least squares regression line, the second method deals with an arithmetic formula, and the rest methods involve the utilization of Excel built-in functions to calculate the least squares regression line in Excel.

Suppose we have a month-wise dataset of **Temperature Rise (°F)** and **Air Conditioners Sold (in thousands)**.

### 1. Using Scatter Chart to Plot Least Squares Regression Line

When plotting a scatter chart to show a least squares regression line, the data points are first plotted on the chart. Then, the line of best fit is added to the chart by using the linear regression equation to calculate the y-values of the line for a range of x-values. In this method, we will plot a scatter chart to show a least squares regression line in Excel.

__Steps:__

- First, select columns from the table and choose the
**Scatter chart**from the**Insert**option.

- A scatter chart will be plotted inside the worksheet.

- Then, visit the
**Add Trendline**option from the**Advanced**options.

- From the right pane, checkmark
**Display Equation on chart**option to continue.

- Finally, we will get the least squares regression line output inside the chart.

**Read More: **How to Do Linear Regression in Excel

### 2. Using Arithmetic Formula to Calculate Least Squares Regression Line

The formula for calculating the least squares regression line is based on arithmetic calculations and is relatively simple to use. The regression line formula stands as-

**ŷ = a + bx**

Where,

**ŷ**= dependent variable

**x**= independent variable

**a**= y-intercept

**b**= slope of the line

For calculating the slope of line (b) the formula is-

**b=xy-xynx2-(x)2n**

In order to calculate y-intercept the formula is-

**a=y-(bx)n**

Thus, we will need to find the** x^2** and **xy **values and then use the values inside the formula.

__Steps:__

- First, we will calculate the
**x^2**value inside a helper column using the below formula.

`=C5*C5`

Here, **C5 **is the starting cell of *Temperature Rise* column.

- Second, in another helper column, we will compute
**xy**using the following formula.

`=C5*D5`

Here, **D5** is the starting cell of *Air Conditioner Sold in Thousands* column.

- Just below the table, we will determine the
**Total Sum Value**row-wise using**the SUM function**.

The formula to sum **C5:C13** cells is-

`=SUM(C5:C13)`

In the case of the **D** column, it will be-

`=SUM(D5:D13)`

Similarly, the formula to sum **E5:E13** cells is-

`=SUM(E5:E13)`

Lastly, the formula to sum **F5:F13** cells is-

`=SUM(F5:F13)`

- Now, it’s time to calculate the
**Slope of Line (b)**value. For this, choose a**cell**(**C18**) and write the below formula-

`=(F15-(C15*D15/9))/(E15-(C15^2/9))`

Here, **F15**, **C15**, **D15**, and **E15 **cells represent sum values of *Temperature Rise*, *Air Conditioners Sold*, *x^2*, and *xy* respectively.

- Similarly, we will calculate the
**Y-intercept (a)**value by choosing a**cell**(**C20**) and applying the below formula.

`=(D15-(C18*C15))/9`

Here, **D15**, **C15**, and **C18 **cells represent sum value of* Air Conditioner Sold in Thousands*, *Temperature Rise*, and *Slope of Line *respectively.

- Finally, we will calculate the least square regression line using the basic linear line formula (
**y=a+bx**).

`=C20+C18*20`

**Read More: **How to Do Multiple Regression Analysis in Excel

### 3. Using LINEST Function to Get Least Squares Regression Line

**The LINEST function** is a mathematical tool in Excel used to calculate the least squares regression line of a set of data points. The function returns an array of values, including the slope, y-intercept, correlation coefficient, and regression statistics for the line of best fit.

__Steps:__

- Start with, choosing a cell and applying the below formula.

`=LINEST(D5:D13,C5:C13)`

- Then, we will use the linear line formula to get the final output with least squares regression line value.

`=D15+C15*20`

### 4. Combining SLOPE and INTERCEPT Functions to Get Least Squares Regression Line

The **SLOPE** and **INTERCEPT** functions in Excel can be combined to obtain the equation for the least squares regression line for a set of data points. The **SLOPE **function calculates the slope of the line, while the **INTERCEPT **function calculates the y-intercept of the line.

__Steps:__

- First, let’s find the
**slope of line (b)**using the below formula.

`=SLOPE(D5:D13,C5:C13)`

- After that, we will find the intercept point with the following formula.

`=INTERCEPT(D5:D13,C5:C13)`

- Just like the previous methods, we will use the linear line formula to get the least squares regression line value.

`=C17+C15*20`

### 5. Utilizing Data Analysis ToolPak to Calculate Least Squares Regression Line

The **Data Analysis ToolPak** is a statistical analysis add-in that includes a variety of tools for performing advanced data analysis. One of the tools included in the **ToolPak **is the **Regression **analysis tool, which can be used to calculate the least squares regression line for a set of data points. In this part, we will utilize the **Data Analysis **feature to get the least squares regression line value.

__Steps:__

- While in the worksheet, click the
**Data Analysis**option from the**Data**tab.

- From the Data Analysis window choose
**Regression**and hit**OK**.

- Here, we will select our desired range of cells and checkmark the
**Labels**and**Residuals**option, and click**OK**.

- In conclusion, a new worksheet will be opened where we will get our regression statistics. Simple, isn’t it?

## Things to Remember

- Outliers and influential points can significantly impact the results of regression analysis and should be carefully examined and potentially removed from the data set.
- The data used to calculate the least squares regression line should be linearly related. Non-linear data should be transformed before calculating the regression line.

## Frequently Asked Questions

**1. What is the difference between simple linear regression and multiple linear regression in Excel?**

**Simple linear regression in Excel** involves the analysis of the relationship between two variables, while **multiple linear regression** involves the analysis of the relationship between three or more variables. In multiple linear regression, a regression line is a plane or hyperplane that best fits the data.

**2. How do I interpret the slope and y-intercept of the least squares regression line in Excel?**

The slope of the least squares regression line represents the change in the dependent variable for every one-unit increase in the independent variable. The y-intercept represents the value of the dependent variable when the independent variable is equal to zero.

**Download Practice Workbook**

You can download our practice workbook here for free!

## Conclusion

In conclusion, the least squares regression line in Excel is a powerful tool for analyzing the relationship between two variables in a data set. It is important to ensure that the assumptions of the regression model are met and that the data is representative before calculating the regression line. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience. Stay tuned and keep learning.