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.
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.
- On the Data tab, in the Analysis group, you can now click on Data Analysis.
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.
- Go to the Data tab > Analysis group > Data analysis.
- Select Regression and click OK.
The following argument window will open.
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:
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.
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.
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.
- 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.
You will get a scatter plot in your worksheet.
- Now to add the trend line, right-click on any point and select Add Trend line.
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 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!
