WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Regression Analysis in Excel

Regression Analysis in Excel

What is Regression Analysis in Excel?

Regression is done to define relationships between two or more variables in a data set, in statistics regression is done by some complex formulas but excel has provided us with tools for regression analysis which is in the analysis tookpak of the excel, click on data analysis and then on regression to do regression analysis on excel.

Explained

The Regression analysis tool performs linear regression in excel examination by utilizing the “minimum squares” technique to fit a line through a lot of observations. You can examine how an individual dependent variable is influenced by the estimations of at least one independent variable. For instance, you can investigate how a sportsman’s performance is influenced by such factors as age, height, and weight. You can distribute shares in the execution measure to every one of these three components, in view of a lot of execution information, and after that, utilization the outcomes to foresee the execution of another person.

Regression Analysis tool in Excel helps you to see how the dependent variable changes when one of the independent variables fluctuates and permits to numerically figure out which of those variables truly has an effect.

Regression in Excel

Examples

  1. Sales of shampoo are dependent upon the advertisement. If advertising expenditure is increased by one Million, then sales will be expected to increase by 23 million, and if there was no advertising, we would expect sales without any increment.
  2. House sales (selling price, no. of bedrooms, location, size, design) to predict the selling price of future sales in the same area.
  3. The sale of soft drinks massively increased in summer when it’s too hot weather. People purchase a more and more soft drink to make them cool; the higher the temperature, the sale will be high and vice versa.
  4. In March, when exam season has started, then the number of exam pads sales increased due to students purchasing exam pad. Exam Pads sale depends upon the examination season.

How to Run Regression Analysis Tool in Excel?

  • Step 1: You need to enable the Analysis ToolPak add-in.
  • Step 2: In your Excel, click on the file on the extreme left-hand side, goes to Options at the end, and click.

Regression Analysis 1

  • Step 3: Once you clicked on options, select Add-ins on the left side, Excel Add-ins is selected in the view and manage box, and click Go.

Regression Analysis 2

  • Step 4: In the Add-in dialog box, click on Analysis Toolpak, and click OK:

Regression Analysis 3

This will add the Data Analysis tools on the right-hand side to the Data tab of our Excel ribbon.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Regression Analysis 4

How to Use Regression Analysis Tool in Excel?

Data to be used for Regression Analysis in Excel:

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

Regression example 1

Once Analysis Toolpak added and enabled in the excel workbook, follow the steps as mentioned below to practice the analysis of regression in Excel:

  • Step 1: On the Data tab in the Excel ribbon, click the Data Analysis

Regression example 1-1

  • Step 2: Click on the Regression and click OK to enable the function.

Regression example 1-2

  • Step 3: Once you clicked the Regression dialog box, need to arrange the accompanying settings:
    • For the dependent variable, select the Input Y, which denotes the data which is dependent. Here in the below-given screenshot, I have selected the range from $D$2:$D$13

Regression example 1-3

    • Independent variable, Select the Input X, which denotes the data which is independent in nature. Here in the below-given screenshot, I have selected the range from $C$2:$C$13

Regression example 1-4

  • Step 4: Click Ok, analyze your data accordingly.

Regression example 1-5

When you run the Regression analysis in Excel, the following output will come:

example 1-6

example 1-7

example 1-8

You can also make a scatter plot in excel of these residuals.

Steps to Create Regression Chart in Excel

  • Step 1: Select the data as given in the below screenshot.

example 1-9

  • Step 2: Tap on the Inset tab, in the Charts gathering, tap the Scatter diagram or some other as a required symbol, and select the chart which suits your information:

example 1-10

  • Step 3: You can modify your chart as a when required, fill the hues and lines likewise of your decision. For instance, you 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

Things to Remember

  1. Always check the value which is dependent and independent; otherwise, the analysis will be wrong.
  2. If you test a huge number of data and thoroughly rank them on the basis of their validation period statistics.
  3. Choose the data carefully to avoid any kind of error in excel analysis.
  4. You can optionally check any of the boxes at the bottom of the screen, although none of these is necessary to obtain the line best-fit formula.
  5. Start practicing with small data to understand the better analysis and run regression analysis tool in excel easily.

Recommended Articles

This has been a step by step guide to Regression Analysis in Excel. Here we discuss how to Run Regression in Excel, its interpretation, and how to use this tool along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Examples of Normal Distribution Graph in Excel
  • Regression vs. ANOVA
  • Excel Exponential Smoothing
  • Exponential Function Excel
18 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Regression Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More