How to Perform Regression in Excel and Interpretation of ANOVA

This article illustrates how to perform Regression Analysis in Excel using the Data Analysis tool and interpret the Anova Table obtained from the analysis. It is widely used in statistical modeling to estimate the impact of variables on a particular topic of interest. Follow the article to learn how to use the regression analysis tool in excel to perform this task.


Download Practice Workbook

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


What Is Regression Analysis?

Regression Analysis is a set of methods used to create a relationship between a dependent variable and a single or multiple independent variables. The two most common forms of regression are-

Simple Linear Regression: You can perform this analysis when there is only one independent variable affecting the outcome of the dependent variable. The equation for simple linear regression may be as follows.

Y = α0 + α1X1 + ϵ

Multiple Linear Regression: You can do this analysis when there are multiple independent variables affecting the outcome of the dependent variable. The equation for multiple linear regression may be as follows.

Y = α0 + α1X1 + α2X2 +….+ αnXn + ϵ


How to Perform Regression Analysis in Excel and Interpretation of ANOVA

⦿ Part-1: How to Perform Regression Analysis in Excel

You can perform Regression Analysis using the Data Analysis tool in Excel. But you may need to activate the tool to be able to access it. The following steps will be sufficient for that.

  • Go to File>>Options or press ALT+F+T.
  • Select the Add-ins tab>>Manage Excel Add-ins>>Go.
  • Check the Analysis ToolPak checkbox>>Click OK.

enable Analysis ToolPak

After that, you can access the Data Analysis tool from the Data tab as shown below.

select Data>Data Analysis


i. Simple Linear Regression

Assume you have the following dataset. Here, X is the independent variable that indicates the rates of interest and Y is the dependent variable that indicates the price of homes. You can perform a regression analysis to see how these variables are related to each other.

dataset for simple linear regression

Follow the steps below to perform a Simple Linear Regression analysis on the dataset in Excel for that.

📌 Steps:

  • First, select Data>>Data Analysis. Then choose Regression from the analysis toolbox and click OK.

select Regression & click OK

  • Next, you will see the Regression dialog box. Now select the Y values including labels for Input Y Range and the X values for Input X Range. Then check the Labels checkbox. Next, mark the radio button for Output Range and enter the cell reference where you want to get the analysis results. Click OK after that.

input data for regression analysis

  • Finally, you will see the following results in the specified location.

ANOVA table from simple linear regression analysis


ii. Multiple Linear Regression

Now assume you have the following dataset instead. Here the dependent Y variable represents the number of weekly riders in different cities. On the other hand, the independent X variables represent the price per week, the population of cities, monthly income of riders, and average parking rates per month respectively. You can verify how the independent variables affect the number of weekly riders by performing a regression analysis on the dataset.

dataset for multiple linear regression analysis

Follow the steps below to perform a Multiple Linear Regression analysis on the dataset in Excel for that.

📌 Steps:

  • First, select Data>>Data Analysis. Then choose Regression from the analysis toolbox as earlier and click OK.
  • Next, you will see the Regression dialog box as earlier. Now select the Y values including labels for Input Y Range and all of the X values for Input X Range. Then check the Labels checkbox. Next, mark the radio button for Output Range. Then, enter the cell reference where you want to get the analysis results. Click OK after that.

input data for multiple linear regression analysis

  • Finally, you will see the following result in the specified location.

ANOVA table from multiple linear regression analysis


Similar Readings


⦿ Part-2: How to Interpret ANOVA and Other Regression Analysis Results in Excel

The regression analysis output is divided into three different parts as follows.

  • Regression Statistics
  • ANOVA Table
  • Coefficients Table

We will briefly explain a few components from each part as the rest of them do not have much importance.

Regression Statistics: The two important values from this table are-

  • Multiple R: It is called the correlation coefficient. It tells you how strong the linear relationship is between the independent and dependent variables. 1, -1, and 0 indicate a strong positive, a strong negative, and no relationship respectively.
  • R Square: This is called the coefficient of determination. It tells you the percentages of the dependent variable that can be explained by the independent variable(s). A value closer to 1 indicates that a difference in the dependent variable can be explained by the difference in the independent variable(s) for most of the values.

ANOVA Table: The Significance F is of the most importance here.

  • Significance F: A value below 0.05 indicates the linear relationship is statistically significant.

Coefficients Table: The coefficients from this table are used to form the linear equation to represent the relationship between the variables.


i. Simple Linear Regression

First, observe the Regression Statistics table below.

regression statistics for simple linear regression

  • Multiple R = 0.62 indicates that the relationship between the variables is not so strong but not so weak either.
  • R Square = 0.38 indicates that 38% of Y values can be explained by X values.

Then, observe the ANOVA Table below.

Anova Table for simple linear regression Analysis

  • Significance F = 0.01 < 0.05 indicates that the linear relationship between the variables is statistically significant.

Finally, observe the Coefficient table below.

coefficients table for simple linear regression

The regression equation can be- Y = 393348.62 – 23409.45X + 41456.52.

Read More: How to Interpret ANOVA Single Factor Results in Excel


ii. Multiple Linear Regression

First, observe the Regression Statistics table.

Regression Statistics table for multiple linear regression

  • Multiple R = 0.97 indicates that the relationship is strong.
  • R Square = 0.94 indicates that 94% of Y values can be explained by X values.

Then, observe the ANOVA Table below.

Anova Table for Multiple Linear Regression Analysis

  • Significance F << 0.05 indicates that the linear relationship between the variables is highly statistically significant.

Finally, observe the Coefficient table below.

Coefficients table for Multiple Linear Regression

The regression equation can be- Y = 100222.56 – 689.52X1 + 0.055X2 – 1.3X3 + 152.45X4 + 5406.37.

Read More: How to Interpret Two-Way ANOVA Results in Excel


Things to Remember

  • You must enable the Analysis ToolPak add-in to access the Data Analysis tool.
  • For the Significance F, a much smaller value than the assumed confidence level means a stronger relationship.

Conclusion

Now you know how to perform a regression analysis in excel and interpret the Anova table obtained from the analysis. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo