Sometimes we try to predict the future outcome based on the past records. *Regression Analysis* is exactly the one which helps to predict the future. In this article, we are going to discuss on how to do *Linear Regression* in Excel.

For more clarification, I am going to use a *Dataset* containing *Player Name, Matches, and Goals* columns to find the *Linear Regression* between *Matches *vs* Goals*.

**Table of Contents**hide

## Basics of Linear Regression

*Regression Analysis *is a part of Statistics which helps to predict values depending on two or more variables. *Linear Regression* helps to estimate values between a single independent and dependent variable.

The equation used here is :

`Y = mX + C + E`

Where,

**Y** = Dependent Variable

**m** = Slope of the Regression Formula**X **= Independent Variable**Î•** = The **Error **which is the difference between the actual value and predicted value.

The error term, **E** is in the formula because no prediction is fully accurate. Though some **Add-ins** calculate errors off-screen, we mention it to clarify the analysis. However, the **Linear Regression** formula becomes **Y=mX+C, **if we ignore the error term.

## How to Do Linear Regression in Excel: 4 Simple Ways

### 1. Using Analysis ToolPak to Do Linear Regression

**Analysis ToolPak **is the best tool to *do Linear Regression*.

**Steps**:

- Go to
**File**.

- Then, select
**Options**.

- Click on
**Add-ins**. - Then, choose
**Excel Add-ins**and click on**Go**.

- Check one
**Add-in**at a time and press**OK**.

- Now, go to
**Data â€”> Data Analysis**.

- From
**Analysis Tools**, choose**Regression**and press**OK**.

- Now, Assign values in the
**Input Y Range**(**e. D5:D13**),**Input X Range**(**i. e. C5:C13**). - Check the boxes named
**Labels, New Worksheet Ply,**and**Residuals**. - Finally, hit
**OK**.

Then, we will have *4 major Linear Regression Analysis Outcomes *in a new window.

**4 Major** **Linear Regression Analysis Outcomes**

**Regression Statistics**:**Regression Statistics**is an array of various parameters that describe how well the measured**Linear Regression**is.

**Multiple R: Multiple R **is a Correlation Coefficient parameter that indicates the correlation between variables. Its value ranges from **-1** to **1**. The bigger positive the value, the stronger correlative the relationships are.

**R Square:** It symbolises the *Coefficient of Determination*. It indicates the scale by how well the data model fits the *Regression Analysis*.

**Adjusted R Square: **The value of **R^2** is used in multiple variables *Regression Analysis*.

**Standard Error:** Another parameter that shows a healthy fit of any *Regression Analysis*. The smaller the *Standard Error* the more accurate the *Linear Regression* equation. It shows the average distance of data points from the Linear equation.

**Observations:** The number of iterations in the data model.

**ANOVA**: It analyses the variance of the data model.

**df:** **df **expresses the **Degrees of Freedom**.

**SS: SS (Sum of Squares) **symbolizes the good to fit parameter.

**MS: **It means the **Mean Square**.

**F:** **F** refers to the Null Hypothesis. It tests the overall significance of the regression model.

**Significance F: **The **P-Value** of **F**.

**Co-efficient Outcome**: It helps to calculate the**Y**values quite easily.

**Residual Output**: It compares the estimated value with the calculated value.

**Read More:** Multiple Linear Regression on Excel Data Sets

### 2. Applying Excel Chart to Do Linear Regression

**Excel Chart **is quite **simple to** **do Linear Regression**.

**Steps**:

- Select the cells to analyze. Here, I selected cells
**C4:D13**. - Go to
**Insert**and click on**Scatter**.

Excel will show the scattered point.

- Now, Right-click on the scattered point and choose
**Add Trendline**.

- From the
**Format Trendline**box, check**Linear**and**Display Equation on chart**.

Finally, we have the **Regression Line **along with the equation.

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

### 3. Formulas to Do Linear Regression

We can also employ **Formulas **to have **Linear Regression**. We are going to apply the **INTERCEPT **and **SLOPE** functions to find out the unknown.

**Steps**:

- Input the following formula in a selected cell to have the value of
**C**which represents the intercepted value of**Y-axis**.

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

- Hit
**ENTER**to have the result.

- Again, input the following formula in a selected cell to have the value of the slope
**m**.

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

- Now, press
**ENTER**.

We are going to depict how strongly those two variables are connected with **the CORREL function**.

- Write down the following formula to have the
**CORREL**function:

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

- Now, click on
**ENTER**to finish the process.

**Read More: **How to Interpret Multiple Regression Results in Excel

### 4. Linear Regression with Solver Add-in

Using **Solver Add-in **to *do Linear Regression* is complex compared to the previous procedures. This method includes some pre-calculation approach before conducting **Solver **Add-in. The pre-calculation includes:

- Assumption of values for the slope (
**m**) and intercept (**C**) components. - Calculation of
**Y**s using those assumed values. - Error finding between the newly calculated
**Y**s and**Y** - Use the
**Solver Add-in**to minimize the error.

**Steps**:

- Input the assumed value as
**Intercept of Y**(**e. -150**) and**Slope (i.e. 1)**.

- Calculate the value of
**Y**s using the regression formula with the assumed value. The formula used here is:

`=$C$18*C5 + $C$17`

- Find the
**Error**by subtracting the calculated**Ys**from the actual**Ys**.

- Now, find the
*Sum Square*value using the**SUMSQ**function.

`=SUMSQ(F5:F13)`

Now, open the **Add-ins **box using the previously mentioned process and check the box **Solver Add-in**.

- Go to
**Data â€”> Solver**.

- From the
**Solver Parameters**box, input**Set Objective (i.e. C19)**. - Check the box
**Max/Min/Value of**depending on the dataset. - Input
**Slope**and**Intercept**cell numbers in the**By Changing Variable Cells**. - Finally, press on
**Solve**.

- Now, check the
**Keep Solver Solution**and press**OK**.

The outcome includes estimated **Y **with the *Linear Regression Analysis.*

**Read More:** How to Interpret Linear Regression Results in Excel

## Practice Section

For further expertise, you can practice here.

**Download Practice Workbook**

## Conclusion

I have tried to articulate 4 simple ways to do *Linear Regression *in Excel. I hope it will be helpful for Excel users. For any more questions, comment below.