Linear Regression in Excel

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.

Linear Regression Data Analysis Tool in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Linear Regression in Excel (wallstreetmojo.com)

How to Add Linear Regression Data Analysis Tool in Excel?

Linear Regression in excel is available under analysis toolpakAnalysis ToolpakExcel's data analysis toolpak can be used by users to perform data analysis and other important calculations. It can be manually enabled from the addins section of the files tab by clicking on manage addins, and then checking analysis toolpak.read more, which is a hidden tool in excel. This can be found under the Data tab.

You can download this Linear Regression Excel Template here – Linear Regression Excel Template

This tool is not visible until the user enables this. To enable this, follow the below steps.

  1. Go to FILE >> Options.

    Linear Regression Excel Example 1

  2. Click on “Add-ins” under “Excel Options.”


    Linear Regression Excel Example 1-1

  3. Select “Excel Add-insExcel Add-insAdd-ins are different Excel extensions that can be found in the options section of the file tab. The first box displays the system's enabled add-ins, and if the user wishes to enable more, they must click on manage add-ins.read more” under Manage Drop Down List in excel and click on “Go.”

    Linear Regression Excel Example 1-2

  4. Check the box “Analysis Toolpak” in the “Add-Ins.”

    Linear Regression Excel Example 1-3

  5. Now we should see the “Analysis Toolpak” option under the “Data” tab.

    Linear Regression Excel Example 1-4

With this option, we can conduct many “data analysis” options. Let’s see some of the examples now.

Examples

As I told, Linear Regression excel is consists of two things, i.e., “dependent & independent variables.” For this example, I am going to use the below data of winter season jacket sold data with temperature in each month.

Linear Regression Excel Example 2

We have each month’s 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 Soldis 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.
Linear Regression Excel Example 2-1
  • Step 2: Once you click on “Data Analysis,” we will see the below window. Scroll down and select “Regression” in excel.
Linear Regression Excel Example 2-2
  • Step 3: Select the “Regression” option and click on “Ok” to open the below the window.
Linear Regression Excel Example 2-3
  • Step 4: “Input Y Range” is the dependent variable, so in this case, our dependent variable is “Jackets Sold” data.
Linear Regression Excel Example 2-4
  • Step 5: “Input X Range” is the independent variable, so in this case, our independent variable is “Temperature” data.
Linear Regression Excel Example 2-5
  • Step 6: Select the output range as one of the cells.
Linear Regression Excel Example 2-6
  • Step 7: To get the difference between the predicted values and actual values to check the box of “Residuals.”
Linear Regression Excel Example 2-7
  • Step 8: Click on the OK; we will have the below analysis.
Linear Regression Excel Example 2-8

The first part of the analysis is “Regression Statistics.”

Linear Regression Excel Example 2-9

Multiple R: This calculation refers to the correlation coefficient, which measures the strength of a linear relationship between two variables. The 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 determinationCoefficient Of DeterminationCoefficient of determination, also known as R Squared determines the extent of the variance of the dependent variable which can be explained by the independent variable. Therefore, the higher the coefficient, the better the regression equation is, as it implies that the independent variable is chosen wisely.read more which is used to indicate the goodness of fit.

Adjusted R Square: This is the adjusted value for R SquareAdjusted Value For R SquareAdjusted R Squared refers to the statistical tool which helps the investors in measuring the extent of the variance of the variable which is dependent that can be explained with the independent variable and it considers the impact of only those independent variables which have an impact on the variation of the dependent variable.read more based on the number of independent variables in the data set.

Things to Remember

Recommended Articles

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 a downloadable excel template. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>