# How to Do Linear Regression Analysis in Excel

Regression Analysis is a technique to forecast the relationship between data variables of entities such as customers, orders, transactions etc. These are the parameters based on which business decisions can be taken for sales, finance and marketing operations. To understand this in a simpler way, let us take some examples.

### Example 1 – Risk Analytics in Retail Banking

Credit risk scores of customers are built by the banking industry to predict the customer’s delinquency behavior and to evaluate credit worthiness of each customer while processing loan applications.

### Example 2 – Marketing Analytics in eCommerce

To predict customer’s buying patterns based on the customer’s past transactions from an online portal. The e-commerce industry is highly competitive so they use analytics to predict customer’s purchasing patterns.

### Example 3 – Pricing Analytics in Stock Market

Stock brokers and investors use analytics to forecast stock pricing based on the past 52-week pricing trend and many other parameters. The analytics helps them to make the decisions on buying or selling the stocks based on the future pricing predictions.

### Example 4 – Behavioral Analytics in Human Resource Industry

Human resource departments / companies use analytics to predict employees’ behavioural patterns that will enable them to decide the increment or promotion to be given on a yearly basis.

### Regression Analysis Basics

Before we go for how to do regression in Excel taking a case study, let us understand the dataset and its components.

### Population vs. Sample

Population is referred to as all members of a defined group that are considered for studying information on data driven decisions. For example – Current inflation rates of EU countries.

Sample is a part of “population”. It can be biased or unbiased. For example – Current inflation rates of EU countries having per capita income < 50000 EURO per annum.

### Types of Data Variables

The below diagram shows types of data variables under each category.

### Data Snapshot

The table below gives an example of data snapshot with different data variables.

Let us understand the data variables available in the above table.

### Summarizing Data

The next step is to summarize data in a presentable format that will give the report on which analysis can be done. The ways to summarize the data are as follows:

1. Frequency Distribution – this is a simple way of counting distinct discrete values. For example – Number of credit cards owned by 3000 customers as sample data. Can be shown as tabular format or as a graph using Excel.

Steps to do this in Excel:

• Go to the Data tab in your Excel Worksheet.
• Click Advanced. The dialog is shown
• Go to the Number of Cards column. Sort them into ascending order of values.
• Insert the formula in # of customers to set the frequency.
• You will get the values as below.
• Go to the Insert tab. Click the Column icon. Select the 2-D Chart.
• The chart will be generated and shown as below.
1. Grouped Frequency Distribution – this is a method to summarize discrete variables having a large number of observations and range of values. For example – Number of customers falling under different salary ranges or groups.

Follow the steps in Excel as per instructions given in the screen below.

1. Cumulative Frequency Distribution – this is obtained by accumulating the frequencies to give the total number of observations up to and including the value or group in question. Illustration as per screen below.

Steps to calculate the cumulative frequency distribution are give in the screen below.

1. Stem Leaf Diagram – this technique is used in a limited set of data. For a large set of data, this method is not used. Example : Given age of 20 individuals in years.
1. Line Plots – this technique is also not suitable for a large set of data. It is not extensively used in the industry. Example: Given test scores of 20 students.

### Measure of Central Tendency – Median

The central tendency measure is robust to the effects of extreme observations.

The median is a value, which splits the data set into two equal halves. Example – Calculate the median for a sample of 3000 individuals having credit cards along with demonstration of extreme observations.

### Measure of Central Tendency – Mode

It is the value which occurs with the greatest frequency or the most typical value. For Example, finding the mode for a sample of 3000 individuals having credit cards. Excel has an in-built function “Mode” for granular data. For summarized data, it can be found easily by visual inspection. Illustration as below.

The different measures of spread are –

• Variance and Standard Deviation – below screen shows how to calculate the values.
• The Range – It is a very simple measure of spread defined, as its name suggests, by the difference between the largest and smallest observations in the data set.
• The Inter quartile range – the quartiles divide a set of data into four quarters. They are denoted by Q1, Q2 and Q3. Q1 is called the lower quartile and Q3 the upper quartile. Following image shows how to calculate Inter quartile Range for a sample of 3000 individuals having credit cards.

### Symmetry and Skewness of data

It represents the shape of the distribution of a dataset, that is, whether it is symmetric or skewed to one side or the other.The approximate shape of a distribution can be determined by looking at a  histogram. The below diagram shows mean, median, mode and variance for symmetric and skewed data.

Tabular representation of data as shown below.

After evaluating the dataset by the above measures and techniques, a regression analysis can be conducted. Now, the question is which type of regression model is required for predictions.

The next section describes the measures that will state the relationship between the data variables and determine how strong the relationship is between identified variables.

### Regression Analysis Models

Following are different techniques or formulas that are used to do regression analysis. These techniques are nothing but different formulas or equations that are used to determine the relationship between X and Y variables.

Covariance and correlation coefficient are the measures used in regression analysis.

• Covariance is a measure that helps to find out the direction of relationship between x variable and y variables. In simple terms, what happens to y when x increases or decreases.

Following equation is used to determine the covariance measure.

• Correlation coefficient is the measure that tells how strong the linear relation is between X and Y variables. It is termed as r value that determines the quality of a model. The parameter is represented by p or r values.
• Population correlation is denoted by ρ (rho)
• Sample correlation is denoted by r.

Following equation is used to determine the correlation coefficient measure for population data.

In real life data analytics, the correlation measure is calculated on sampling distribution. There are a couple of reasons why the sampling is used for analytics.

• The physical impossibility of checking all items in the population.
• The cost of studying all the items in a population.
• The sample results are usually adequate.
• Contacting the whole population would often be time consuming.

Features of ρ and r

• Unit free and ranges between -1 and 1
• The closer to -1, the stronger the negative linear relationship
• The closer to 1, the stronger the positive linear relationship
• The closer to 0, the weaker the linear relationship

### Tools used to do Regression Analysis

Following software tools are the most used in performing Regression Analysis. The oldest tool is Microsoft Excel.

As the spectrum of analytics is widened, dataset volume has increased, the data scientists have started using Python.

We will see a case study to run regression analysis in Excel.

### Case Study

An Auto Insurance company having the data of “Loss” amount and policy related information. The company wants to know the factors responsible for losses in multivariate fashion using a regression model.

Prior to running the regression model, following things needs to be in place.

• Variable identification –
• Identifying the dependent (response) variable.
• Identifying the independent (explanatory) variables.
• Variable categorization (e.g. Numeric, Categorical, Discrete, Continuous etc.)
• Creation of Data Dictionary
• Response variable exploration
• Distribution analysis
• Percentiles
• Variance
• Frequency distribution
• Outlier treatment
• Identify the outliers/threshold limit
• Cap/floor the values at the thresholds
• Independent variable analyses
• Identify the prospective independent variables (that can explain response variable)
• Bivariate analysis of response variable against independent variables
• Variable treatment /transformation
• Grouping of distinct values/levels
• Mathematical transformation e.g. log, splines etc.

After performing the above data related activities, the last step is iterative in terms of running the regression.

Fitting the regression (first time)

• Check for correlation between independent variables
• This is to take care of Multicollinearity
• Variable selection
• Check for the most suitable transformed variable
• Select the transformation giving the best fit
• Reject the statistically insignificant variables

Fitting the regression (iteration)

• Analysis of results
• Model comparison
• Model performance check
• Actual vs Predicted comparison

### Steps to do regression analysis in Excel

We will perform the following steps in Excel with reference to the case study explained in the section above.

#### Data Analysis

Step 1. Snapshot of Data and Data Description

The above data contains policy holders’ and loss amount information (variables)

• Policy Number
• Age
• Years of Driving Experience
• Number of Vehicles
• Gender
• Married
• Vehicle Age
• Fuel Type
• Losses (Dependent/Response Variable)

A Data Dictionary is formed as below.

Step 2. Frequency Distribution – Loss amount

Step 3. Calculate Capped Losses using grouped frequency distribution

Step 3. Perform variable analysis in Excel. The first bivariate profiling we will do for Age vs. Capped Loss for policies.

Step 4. The second bivariate profiling we will do for Years of Driving Experience vs. Capped Loss for policies.

Step 5. The next bivariate profiling we will do for Gender vs. Capped Loss for policies.

Step 6. The next bivariate profiling we will do for Marital Status vs. Capped Loss for policies.

Step 7: The next bivariate profiling we will do for Vehicle Age vs. Capped Loss for policies.

Step 8: The next bivariate profiling we will do for Vehicle Age Band vs. Capped Loss for policies.

Step 9: The next bivariate profiling we will do for Vehicles vs. Capped Loss for policies.

Step 10: The next bivariate profiling we will do for Fuel Type vs. Capped Loss for policies.

#### Preparing Excel for Regression Analysis

Step 1: Open Office icon from the toolbar.

Step 2: Go to Excel Options.

Step 4: From the Manage drop-down, select Excel Add-ins.

Step 5: Select the Analysis Toolpak check box and the Solver Add-in check box.

Step 6: Select the Data tab. Click Data Analysis

Step 7: In the Data Analysis dialog box, click Regression.

Step 8: Select the data cell reference range. Click OK.

Step 9: See the summary results as below.

Step 10: Picking up the variables based on the following activities.

We will perform the following tests to select by running :

• Multicollinearity
• Banding of variables
• Statistical significance of variables tested

### List of independent variables:

1. Age
2. Age Band
3. Years of Driving Experience
4. Number of Vehicles
5. Gender
6. Married
7. Vehicle Age
8. Vehicle Age Band
9. Fuel Type

“Age” and “Years of Driving Experience” are highly correlated (Correlation Coefficient = 0.9972). We can use either of the variables in regression.

Fit two separate models using either of the variables one at a time. Check for goodness of fit (R2 in this case).  The variable producing higher R2 gets accepted.

Age

Years of Driving Experience

• R2 for Age > R2 for Years of Driving Experience
• Reject Years of Driving Experience

Banding of Variables

• Investigate whether to use “Age” or “Age band”
• Fit regression independently using “Age” and “Age Band”
• Before fitting regression, “Age Band” needs to be converted to numerical form from categorical. Replace “Age Band” values with “Average Age” for the particular band.
• Regressions results using “Age” and “Average Age”
• R2 for Average Age > R2 for Age
• Select “Average Age”

Investigate whether to use “Vehicle Age” or “Vehicle Age band”

• Fit regression independently using “Vehicle Age” and “Vehicle Age Band”
• Before fitting regression, “Vehicle Age Band” needs to be converted to numerical form from categorical. Replace “Vehicle Age Band” values with “Vehicle Average Age” for the particular band.
• Regressions results using “Vehicle Age” and “Average Vehicle Age”
• R2 for Average Vehicle Age > R2 for Vehicle Age
• Select “Average Vehicle Age”

Now, we have identified the variables that can be used in fitting the regression model.

1. Age Band in the form of “Average Age” of the band (selected out of “Age” and  “Age Band”). Also got selected over “Years of Driving Experience”.
2. Number of Vehicles
3. Gender
4. Married
5. Vehicle Age Band in the form of “Average Vehicle Age” of the band (selected out of “Vehicle Age” and  “Vehicle Age Band”).
6. Fuel Type

Step 11: Categorical variables to be converted to their numerical equivalent (0,1)

1. Gender (F = 0 and M = 1)
2. Married (Married = 0 and Single = 1)
3. Fuel Type (P = 0, D = 1)

Snapshot of the final data on which we will run the multivariate regression.

Step 11: Run Regression.

Summary of Output

Results Interpretation

Significant Test

Significance test of coefficients based on Normal distribution

• H0: b is no different that 0 (i.e. 0 is the coefficient when the variable is not included in regression)
• H1: b is different than 0

Test statistic, Z = (b-0)/σ  (at 95% two tailed confidence interval, Z = 1.96)

Confidence interval = (b – 1.96 * σ, b + 1.96 * σ)

For the variable to be significant, the interval must not contain “0”.

Example1: Avg Age.

Confidence interval = (-5.560-1.96*0.065, -5.560+1.96*0.065) = (-5.688, -5.431)

No zero in the interval. Hence, significant.

Example2: Number of Vehicles

Confidence interval = (0.179-1.96*0.970, 0.179+1.96*0.970) = (-1.723, 2.080)

Zero is present in the interval. Hence, insignificant.

Summary output after removing insignificant variables.

Predicted Losses = 625.004932715948 – 5.5596551344537 * Avg Age + 50.8828923910091 * Gender Dummy + 78.4016899779131 * Married Dummy -15.1420259903571 * Avg Vehicle Age + 267.935139741526 * Fuel Type Dummy

Interpretation

### Takeaway

Running regression in Excel gives you more confidence on your model as you are running each step manually. It is a time consuming and lengthy process, but you get to learn more on datasets and output summary. Other tools such as R, SAS and Python give you quick results but you will not be able to know the algorithm behind the code that is executed and have given you the results.

## You may also like:

How to do Indent Text in Excel Sheet

How To Delete Multiple Rows in Excel Sheet at Once

How to Create a Waterfall Chart in Microsoft Excel?