The Best Guide to Understand Regression Analysis in Excel

Regression analysis is a set of statistical methods used for the estimation of relationships between a dependent variable and independent variables. We can use it to assess the strength of the relationship between variables and for modeling the future relationship between them.

In this article, we will look into the following topics.

  • Data Analysis Toolpak
  • Run Regression Analysis
  • Interpret Regression Analysis Output
  • Regression Graph in Excel

Data Analysis Toolpak

The Data Analysis ToolPak is an Excel add-in that provides data analysis tools for financial, statistical, and engineering data analysis

  • Click the File tab, click Options, and then click the Add-Ins category.
  • Select Analysis ToolPak and click on the Go button.
  • Check Analysis ToolPak and click on OK.

dat 1

  • On the Data tab, in the Analysis group, you can now click on Data Analysis.

dat2

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Run Regression Analysis

In Excel, we use regression analysis to estimate the relationships between two or more variables. There are two basic terms that you need to be familiar with:

The Dependent Variable is the factor you are trying to predict.

The Independent Variable is the factor that might influence the dependent variable.

Consider the following data where we have a number of COVID cases and masks sold in a particular month.

regression

  • Go to the Data tab > Analysis group > Data analysis.
  • Select Regression and click OK. 

The following argument window will open.

regression 2

Select the Input Y Range as the number of masks sold and Input X Range as COVID cases. Check the residuals and click OK.

You will get the following output: 

output regression

Interpret Regression Analysis Output

Let us now understand the meaning of each of the terms in the output. We will divide the output into four major parts for our understanding.

Summary Output

The summary output tells you how well the calculated linear regression equation fits your data source.

summary output

The Multiple R is the Correlation Coefficient that measures the strength of a linear relationship between two variables. The larger the absolute value, the stronger is the relationship. 

  • 1 means a strong positive relationship
  • -1 means a strong negative relationship
  • 0 means no relationship at all

R Square signifies the Coefficient of Determination, which shows the goodness of fit. It shows how many points fall on the regression line. In our example, the value of R square is 0.96, which is an excellent fit. In other words, 96% of the dependent variables (y-values) are explained by the independent variables (x-values).

Adjusted R Square is the modified version of R square that adjusts for predictors that are not significant to the regression model.

Introduction To Data Analytics (FREE)

Mastery to Data Analytics Basics is a Click Away!Start Learning
Introduction To Data Analytics (FREE)

Standard Error is another goodness-of-fit measure that shows the precision of your regression analysis.

ANOVA

ANOVA stands for Analysis of Variance. It gives information about the levels of variability within your regression model.

annova

  • Df is the number of degrees of freedom associated with the sources of variance.
  • SS is the sum of squares. The smaller the Residual SS viz a viz the Total SS, the better the fitment of your model with the data.
  • MS is the mean square.
  • F is the F statistic or F-test for the null hypothesis. It is very effectively used to test the overall model significance.
  • Significance F is the P-value of F.

Regression Graph In Excel

You can quickly visualize the relationship between the two variables by creating a graph. To create a linear regression graph, follow these steps:

  • Select the two variable columns of your data, including the headers.
  • Go to Insert tab > Charts group > Scatter Plot.

graphgraph1graph1

You will get a scatter plot in your worksheet.

graph2

  • Now to add the trend line, right-click on any point and select Add Trend line.

graph3

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

That’s how you do Regression analysis in Excel. You should also know the fact that Microsoft Excel is not a statistical program.

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training.

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you! 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.