This article illustrates how to perform multiple linear regression on data sets in excel. You will learn 2 easy ways to do that here. Linear Regression is used to predict the value of a response variable dependent on another explanatory variable. We need to use **Multiple Linear Regression** instead when there are more than one explanatory or independent variables on which the value of the target variable is dependent. You can easily perform a Multiple Linear Regression analysis or data sets in excel. Have a quick look through the article to learn how to do that.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from the download button below.

## What Is Multiple Linear Regression?

**Multiple Linear Regression** or **Multiple Regression** is a statistical method to forecast the outcome of a particular variable dependent on several other independent variables. Its purpose is to establish a linear relationship between a dependent variable and several other independent variables. It is the extended version of the **Simple Linear Regression** which uses only one independent variable.

Assume that there are two independent variables **X _{1}** and

**X**affecting the value of a dependent variable

_{2}**Y**. Then, the equation for Multiple Linear Regression will be:

**Y = α**

_{0}+ α_{1}X_{1}+ α_{2}X_{2}+ ϵHere,**α** stands for the Intercept (constant). **α _{1}**and

**α**represent the change in

_{2}**Y**due to the changes in

**X**and

_{1}**X**respectively. On the other hand,

_{2}**ϵ**signifies the residual or error.

Multiple Linear Regression assumes a linear relationship between the dependent and the independent variable. But, the relationship between the independent variables is statistically insignificant.

This technique is mainly and extensively used in financial inference and econometrics.

## 2 Ways to Perform Multiple Linear Regression on Data Sets in Excel

Consider the following dataset. Here, the dataset contains a sample of 10 houses numbered 1 to 10. **X _{1}** indicates the age of the houses and

**X**denotes the number of grocery stores near each of them. Then prices per unit area of the houses are expressed by

_{2}**Y**. Therefore,

**Y**is the dependent variable here. On the other hand,

**X**and

_{1}**X**are the independent variables.

_{2}You can perform a Multiple Regression on the above dataset in two ways in excel. Follow the methods below to do that.

### 1. Multiple Linear Regression on Data Sets with Data Analysis

Performing a Multiple Linear Regression in excel involves 3 easy steps as highlighted below.

#### ⏩ Enable the Analysis ToolPak

- First, press
**ALT+F+T**to open**Excel Options**. Next, go to the**Add-ins**tab. Then, select**Excel Add-ins**. Click on**Go**after that.

- Then, check the
**Analysis ToolPak**checkbox. Next, select OK. After that, you can access the**Data Analysis**feature from the**Data**tab.

#### ⏩ Perform the Regression Analysis

- Now, select
**Data >> Data Analysis**as shown in the picture below.

- Next, scroll through the
**Analysis Tools**in the**Data Analysis**dialog box. Then, select**Regression**and then click OK.

- Now, enter the entire range of
**Y**(**$E$4:$E$14**) variables including the header cells for**Input Y Range**using the upward arrow. Next, do the same for**Input X Range**(**$C$4:$D$14**). Then, check the**Labels**checkbox. You can choose the**Output Options**as required. Finally, select the OK button.

- After that, you will see the analysis result in detail as shown in the following picture.

#### ⏩ Interpret the Results

Here, I will explain the three components of the regression analysis: The **Regression Statistics** table, the **ANOVA** table, and the **Regression Coefficients** table.

**Interpret the Regression Statistics Table:**

- Let’s discuss the summary output or the
**Regression Statistics**table first. Here, the R Square is of the greatest importance.

**R Square = 0.6998**means**69.98%**of the variables can be explained by the regressors or the independent variables.- The Standard Error signifies the estimated standard deviation for the residual or error.

**Interpret the ANOVA Table:**

- Now, the
**Analysis of Variance (ANOVA)**table is given below.

- Here,
**df**stands for the degree of freedom and**SS**signifies the sum of squares of variances. - The
**Significance F**column has a**P-Value**of**0148**which is less than**5%**. So, we can reject**the Null Hypothesis**. It concludes that the impact of the independent variables on the dependent variable is statistically significant.

**Interpret the Coefficients Table:**

- The table below containing the coefficients and other outputs is of the most importance.

- We get the following coefficients from the table above.
**α**,_{0}= 43.11**α**,_{1}= – 0.82**α**, and_{2}= 2.42**ϵ = 6.58**. Therefore, the equation becomes:

**Y = 43.11 – 0.82X**

_{1}+ 2.42X_{2}+ 6.58**Read More:**** How to Interpret Multiple Regression Results in Excel**

### 2. Multiple Linear Regression on Data Sets with LINEST Function

Alternatively, you can use the **LINEST function** in excel to get those results. Enter the following formula in cell **H5** to get the desired results.

`=LINEST(E5:E14,C5:D14,TRUE,TRUE)`

Then, excel may show errors in some cells. You can nest **the LINST function** inside the **IFERROR function** to avoid that.

`=IFERROR(LINEST(E5:E14,C5:D14,TRUE,TRUE),"")`

**🔎 How Does the Formula Work?**

**Objectives:**

The **LINEST** Function returns the statistics that describe a linear trend matching known data points, by fitting a straight line using the least-squares method.

**Syntax:**

**LINEST(known_ys, [known_xs], [const], [stats])**

**Arguments:**

**known_ys :**Required. The dependent variable i.e. the**Y**range.**[known_xs] :**Optional. The independent variables i.e. the**X**and_{1}**X**ranges._{2}**[const] :**Optional.**True**– constants are calculated normally.**False**– constants are set equal to zero.**[stats] :**Optional.**True**– returns the additional regression statistics.**False**– do not return additional regression statistics.

**Read More:**** How to Do Linear Regression in Excel (4 Simple Ways)**

## Things to Remember

- Excel is limited to a particular number of regressors, possibly 16. Therefore, you cannot use more independent variables than that.
- You must keep the regressors or the independent variables in adjoining columns.
- Excel assumes that the errors are independent with constant variance (homoskedastic). It does not provide any alternatives.

## Conclusion

Now you know how to perform multiple linear regression on data sets in excel. Please let us know if the methods were useful to you. You can also use the comment section below for further queries or suggestions. Do visit our **ExcelDemy** blog to explore more about excel. Stay with us and keep learning.