Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Do Correlation and Regression Analysis in Excel

In statistical analysis, performing Correlation and Regression calculations is the popular way to analyze and predict future events. However, there are several ways to calculate Correlation and Regression for a specific set of data. In this article, we will show a simple demonstration of how to do Correlation and Regression Analysis in Excel.
The below image depicts a complete output of Linear Regression analysis which you will get upon completion of the task.

correlation and regression in excel


Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.


Introduction to Correlation and Regression

Correlation is an expression of how closely two variables are linearly related to each other. It is a typical technique for describing apparent connections without explicitly stating any cause and consequence.
In statistical analysis, Correlation is measured by a coefficient denoted by “r”.
Let’s assume a set of data labeled by two variables X and Y. Thus the Correlation Coefficient can be formulated such as

Formula of the Correlation Coefficient

Where,

  • and ȳ are the sample means AVERAGE of X variable and AVERAGE of Y variable respectively.

On the other hand, Regression analysis is a statistical technique devoted to estimating the connection between one dependent and two or more independent variables. It can be used to simulate the long-term link between variables and evaluate the future outcome of the dependent variable. For Linear Regression Analysis, a linear line equation can be formulated as below,

Y=mX+C

Where,

  • Y is the dependent variable, and X is the independent variable. m is the slope of the straight line.

Here, we have chosen a dataset named “Financial Statement of ABC in First Week” to accomplish our task. However, you may select any dataset that is suitable for you.

Sample Dataset for doing Correlation and Regression Analysis in Excel

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


2 Quick Steps to Perform Correlation and Regression Analysis in Excel

In Excel, there are several methods to perform Correlation and Regression analysis, such as using the CORREL, PEARSON, INTERCEPT, SLOPE functions, or utilizing Scatter Chart, Analysis ToolPak Add-in, etc. However, as our primary objective is to make an Excel sheet with both Correlation and Regression analysis for one specific dataset, we will incorporate two different methods to accomplish the task. However, in the following section, we will add one more method to help you understand the relevant methods and functions.


Step 1: Calculate the Correlation Coefficient

  • Write the following formula in cell C13.
=CORREL(C5:C11,D5:D11)
  • Press the ENTER button to see the output.

Correlation coefficient calculation using the CORREL function


Step 2: Compute the Constant and Intercepting value for Regression Line

  • Enter the following formula in cell C16 to calculate the slope value.
=SLOPE(D5:D11,C5:C11)
  • Hit the ENTER button to see the output.

Calculation of Slope value of Regression line using SLOPE function

  • Following that, write the following formula in cell C17 to compute the intercepting value of the Regression line.
=INTERCEPT(D5:D11,C5:C11)
  • Hit the ENTER button afterward.

Calculation of intercept value in correlation and regression analysis in excel

Here, the Correlation Coefficient indicates how closely the data point aligns with the Regression line. The given dataset’s correlation coefficient value of 0.952 indicates that the data points are closely aligned with the created trendline. However, the Slope value of the Regression line shows the amount of profit change per the number of cars sold. The intercept value indicates that the profit will be -11.940 when the number of cars sold is zero.


How to Do Correlation Analysis in Excel

Although we have seen one of the ways to find the Correlation Coefficient for a set of data in the previous method, here we are adding one more method at your disposal for your better understanding. Other than the CORREL function, here we will use the PEARSON function to accomplish the task.

📌 Steps:

  • Go to the C13 cell >> enter the formula as given below >> press the ENTER key.
=PEARSON(C5:C11,D5:D11)

Performing Correlation analysis using PEARSON function


How to Accomplish Regression Analysis in Excel

There are several methods to perform a Regression analysis in Excel. Here, we will see how to do Regression analysis by using an Excel Chart.

📌 Steps:

  • Select the range of the dataset from C5:D11, then navigate to the Insert tab >> Charts group >> Insert Scatter or Bubble Chart group >> Scatter.

Inserting Scatter plot for creating linear regression line

You will get an output as given below

Linear Regression Line without trendline

  • After that, click one of the data points on your chart, then do Right-click on your mouse.
  • Press on the Add Trendline option.

Format the trendline of Linear Regression analysis

  • Now on the right side of your Excel interface, a wizard will appear.
  • Select Linear as your Trendline option.
  • Later, select Display Equation on Chart.

Format wizard for the trendline of Linear Regression analysis

Finally, you will get the final output along with the Trendline as given below.

correlation and regression in excel


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.

Do it yourself


Conclusion

In this article, we have discussed how to do Correlation and Regression Analysis in Excel. As you have already understood, there are plenty of ways to do this task. So before going through a specific method, ensure the method you choose aligns with your work. Further, If you have any queries, feel free to comment below and we will get back to you soon.

Mohammad Shah Miran

Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. Miran is dedicated to staying current with the latest trends and technologies and is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo