Excel Linear Regression
Linear Regression is a statistical tool in excel that is used as a predictive analysis model to check the relationship between two sets of data of variables. Using this analysis we can estimate the relationship between two or more variables. We can see two kinds of variables i.e. “Dependent Variable & Independent Variable”.
- The dependent variable is the factor we are trying to estimate.
- The independent variable is the thing that influences the Dependent Variable.
So, using excel Linear Regression we can actually see how the dependent variable goes through changes when the independent variable changes and helps us to mathematically decide which variable has a real impact.
How to Add Linear Regression Data Analysis Tool in Excel?
Linear Regression in excel is available under analysis toolpak which is a hidden tool in excel. This can be found under the Data tab.
This tool is not visible until the user enables this. To enable this follow the below steps.
Step 1: Go to FILE >> Options.
Step 2: Click on “Add-ins” under “Excel Options”.
Step 4: Check the box “Analysis Toolpak” in the “Add-Ins”.
Now we should see the “Analysis Toolpak” option under the “Data” tab.
With this option, we can conduct many “data analysis” options. Let’s see some of the examples now.
Examples of Linear Regression in Excel
As I told Linear Regression excel is consists of two things i.e. “dependent & independent variables”. For this example, I am going to use below data of winter season jacket sold data with temperature in each month.
We have each month average temperature and jacket sold data. Here we need to know which is independent and which dependent variables are.
Here “Temperature” is the independent variable because one cannot control the temperature, so this is the independent variable.
“Jackets Sold” is the dependent variable because based on the temperature increase and decreases jacket sale varies.
Now we will do the excel linear regression analysis for this data.
Step 1: Click on the DATA tab and Data Analysis.
Step 2: Once you click on “Data Analysis” we will see the below window. Scroll down and select “Regression” in excel.
Step 3: Select the “Regression” option and click on “Ok” to open the below the window.
Step 4: “Input Y Range” is the dependent variable, so in this case, our dependent variable is “Jackets Sold” data.
Step 5: “Input X Range” is the independent variable, so in this case, our independent variable is “Temperature” data.
Step 6: Select the output range as one of the cells.
Step 7: To get the difference between the predicted values and actual values to check the box of “Residuals”.
Step 8: Click on OK, we will have the below analysis.
The first part of the analysis is “Regression Statistics”.
Multiple R: This calculation refers to the correlation coefficient which measures the strength of a linear relationship between two variables. Correlation Coefficient is the value between -1 and 1.
- 1 Indicates a strong positive relationship.
- -1 indicates a strong negative relationship.
- 0 indicates no relationship.
R Square: It is the coefficient of determination which is used to indicate the goodness of fit.
Adjusted R Square: This is the adjusted value for R Square based on the number of independent variables in the data set.
Things to Remember
- We can also use the LINEST function in excel.
- You need to have a strong knowledge of statistics to interpret the data.
- If the data analysis is not visible under the Data tab we need to enable this option under the add-ins option.
This has been a guide to Linear Regression in Excel. Here we discuss How to do Linear regression data analysis in excel along with examples and downloadable excel template. You may also look at these useful functions in excel –
- ANOVA in Excel
- Compare – Regression vs ANOVA
- Formula of Multiple Regression
- Formula of Regression
- What is Sensitivity Analysis?
- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion
- Basic Excel Training
- Advanced Excel Training
- Basic & Advanced VBA Course
- Excel Dashboard Course
- Data Analysis in Excel
- Create VBA Applications