Regression Analysis In Excel

Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Regression Analysis In Excel?

Regression Analysis in Excel defines relationships between two or more variables in a data set. In statistics, regression is done by some complex formulas. However, we have Excel tools to perform Regression Analysis.

For Example, to calculate the Regression Analysis in Excel for the input X and Y range, select the “Data” tab – go to the “Analysis” group – click the “Data Analysis” option, and when the “Data Analysis” window pops-up, select the “Regression” option, and click “OK“, as shown in the image below.

Then, we will get the calculated Excel Regression analysis.

Regression Analysis 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: Regression Analysis In Excel (wallstreetmojo.com)

Key Takeaways

  • Regression Analysis in Excel is used to see if there is a statistically significant relationship between two sets of variables.
  • We can calculate the value of the dependent variable based on the values of one or more independent variables. If the independent variable is modified, then we can see its impact on the dependent variables.
  • We will get incorrect results if we choose to predict any value outside the given range.
  • To run the Regression Analysis,we must first enable the “Analysis ToolPak”. Then click the Regression option from the Excel Add-ins from the Data Analysis option in the Data tab.

How To Run Regression Analysis Tool In Excel?

  1. We must enable the Analysis ToolPak Add-in.

  2. In Excel, click the “File” tab on the ribbon, and click the “Options”, once the file tab opens the below list.


    Regression 1

  3. On clicking “Options,” select “Add-ins” on the left side. Excel Add-ins are chosen in the “View and manage Microsoft Add-ins” and “Manage” boxes. Then, click “Go.”


    Regression 2

  4. In the Add-in dialog box, click on Analysis ToolPak, and click OK:


    Regression 3

    It will add the “Data Analysis” tools on the right-hand side to the Excel ribbon’s “Data” tab.

    Regression 4

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use Regression Analysis Tool In Excel?

We can use the data for Regression Analysis in Excel, once the 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 is added and enabled in the Excel workbook.

We will see, with an example, how to use Regression Analysis in Excel.

#Example

Consider the umbrellas sold data, with the months and rainfall, given in the image below.

Regression example 1

The steps to perform the Regression Analysis in Excel are as follows:

Regression example 1-1
  • Step 2: Click the “Regression” option, and click “OK” to enable the function.
Regression example 1-2
  • Step 3: On clicking the “Regression” dialog box, we must arrange the accompanying settings:
    • For the dependent variable, select the “Input Y Range”, which denotes the dependent data. Here, in the below-given screenshot, we have selected the range from $D$2:$D$13.
Regression example 1-3
  • Select the “Input X Range,” which denotes the independent data for the independent variable. Here, in the below-given screenshot, we have selected the range from $C$2:$C$13.
Regression example 1-4
  • Step 4: Click “OK” and analyze the data accordingly.
Regression example 1-5

When you run the Regression Analysis in Excel, we will get the following output.

example 1-6
example 1-7
example 1-8

You can also make a scatter plot in excelMake A Scatter Plot In ExcelScatter plot in excel is a two dimensional type of chart to represent data, it has various names such XY chart or Scatter diagram in excel, in this chart we have two sets of data on X and Y axis who are co-related to each other, this chart is mostly used in co-relation studies and regression studies of data.read more of these residuals.

How To Create Regression Chart In Excel?

We will consider an example to create the Regression Chart In Excel.

The steps to Create Regression Chart in Excel are,

  • Step 1: Select the data as given in the below screenshot.
example 1-9
  • Step 2: Tap on the “Insert” tab. In the “Charts” gathering, tap the “Scatter” diagram or some other as a required symbol. Select the chart which suits the information.
example 1-10
  • Step 3: We can modify the chart when required and fill in the hues and lines of your decision. For instance, we can pick alternate shading and utilize a strong line of a dashed line. We can customize the graph as we want to customize it.
example 1-11

Regression Analysis Using Excel Formula

We can calculate the Regression Using Excel Formula “y = bx + a”  

where,

  • x – independent variable
  • y – dependent variable
  • a – y-intercept
  • b – a slope of the regression line.

The steps to calculate the Regression Using Excel Formula are,

  1. First, in a dataset, ensure we have all the values for the variables in the formula.
  2. Next, in an empty cell, calculate the formula y = bx + awith the values.
  3. Finally, when we press “Enter”, we get the output.

Once we get the output, we can plot the Regression Graph as we learned in the previous section, but this time including the output value cell too.

Important Things To Note

Frequently Asked Questions (FAQs)

1. Explain the working of the Regression Analysis in Excel.

The Regression Analysis tool performs linear regression in excelLinear Regression In ExcelLinear Regression is a statistical excel tool that is used as a predictive analysis model to examine the relationship between two sets of data. Using this analysis, we can estimate the relationship between dependent and independent variables.read more examination using the “minimum squares” technique to fit a line through many observations.

You can examine how an individual dependent variable is influenced by the estimations of at least one independent variable. The Excel Regression Analysis tool helps you see how the dependent variable changes when one of the independent variables fluctuates and permits you to numerically figure out which of those variables truly has an effect.

For instance, you can investigate how such factors influence a sportsman’s performance as age, height, and weight. You can distribute shares in the execution measure to every one of these three components, given a lot of execution information, and then utilize the outcomes to foresee the execution of another person.

2. Name some of the scenarios to calculate the Regression Analysis in Excel.

1) Sales of shampoo are dependent upon the advertisement. If $1 million increases advertising expenditure, sales will be expected to increase by $23 million. If there were no advertising, we would expect sales without any increment.

2) House sales (selling price, number of bedrooms, location, size, design) predict the selling price of future sales in the same area.

3)Soft drink sales massively increase in summer when the weather is too hot. People purchase more and more soft drinks to keep them cool. The higher the temperature, the higher the sales, and vice versa.

4) In March, exam season started, and sales increased due to students purchasing exam pads. Exam pads sale depends upon the examination season.

3. What are the chart types that help to plot Regression Analysis in Excel?

A few chart types we can use to plot the Regression Analysis in Excel are Scatter, Line, Bar, etc.

Download Template

This article must help understand Regression Analysis in Excel with its formulas and examples. You can download the template here to use it instantly.

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

This article is a guide to Regression Analysis in Excel. Here we see relation between variables, run regression analysis, examples, downloadable excel template. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *