How to Create a Regression Analysis Using Excel 2013

February 18, 2017

How to Create a Regression Analysis Using Excel 2013

Basically, regression is an analytical tool used to test the relationship that exists between two or more variables. These variables are the dependent and the independent variables. The dependent variable refers to the variable that is explained or is affected by any change in the independent variable(s). The independent variable(s) is (are) the explanatory factor(s) that depicts the value or influences the dependent variable.

In High School, we were thought regression analysis and that seem to be one of the most difficult topic in Elective Mathematics. We have to learn a very long equation on how to estimate the constant and the slope of a line. But these computations has been made easy by various statistical software such as Excel, STATA, OpenStat, SAS/STAT, to mention but few.

Analysts, statisticians, corporate entities and academic professionals use one of these software or a combination of them to study various variables ranging from health, culture, education, personality, customer reaction to a new product/service, etc.

This topic seeks to describe the steps in the creation of a regression analysis using Microsoft Excel 2013 version. The same procedure can be used for other MS Excel versions.

Steps to Follow

Step 1: Data Creation

When I say data I mean the dependent and the independent variables you want to estimate the association for. The dependent variable is advisable to be brought first before the independent variables. For easy analysis, I recommend that the data is arranged vertically in the form below:

Step 2: Go to Data Section

Step 3: Select Data Analysis

The Data Analysis bar which is located at the top right corner of your windows might not be visible if you haven’t used your Excel package to do any statistical computations. The various steps to be taken to make it visible are illustrated below:

Step 3.1: Go to File

Step 3.2: Click on Options

Step 3.3: Select Add-Ins

Step 3.4: Click on Analysis ToolPak and Select OK

Activate Analysis ToolPak by clicking on the small box beside it, and the select OK to make your Data Analysis Bar visible.

When all these sub-steps have been followed, your Data Analysis Bar will appear at the top right corner of your Excel window.

Click then on Data Analysis. A small window will appear on your screen in the form below:

Step 4: Select Regression and Click OK

Step 5: Highlight All Your Y Range

The Y range represents the dependent variable.

Step 6: Highlight All Your X Range

The X range represents all your independent variables.

Step 7: Select Labels and New Worksheet Ply, Click OK

Results: After following all these steps, your regression analysis summary output will appear in a new worksheet. Below is a glimpse of how it looks like: