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.

Examples

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.
House sales (selling price, no. of bedrooms, location, size, design) to predict the selling price of future sales in the same area.
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, higher the temperature sale will be high and vice versa.
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 in the extreme left-hand side, goes to Options at the end and click.

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

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

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

Popular Course in this category

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

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

Once Analysis Toolpak added and enabled in 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

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

Step 3: Once you clicked the Regression dialog box, need to arrange the accompanying settings:
- 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

- 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

Step 4: Click Ok, analyze your data accordingly.

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

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.

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:

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.

Things to Remember

Always check the value which is dependent and independent, otherwise, the analysis will be wrong.
If you test a huge number of data and thoroughly rank them on the basis of their validation period statistics.
Choose the data carefully to avoid any kind of error in excel analysis.
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.
Start practicing with small data to understand the better analysis and run regression analysis tool in excel easily.