How to Do Correlation and Regression Analysis in Excel

Get FREE Advanced Excel Exercises with Solutions!

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


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.


How to Do Correlation and Regression Analysis in Excel: 2 Quick Steps

In Excel, there are several methods to perform Correlation and Regression analysis, such as using the CORREL, PEARSON, INTERCEPT, and 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

Read More: How to Calculate Partial Correlation in Excel 


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.

Read More: How to Calculate Cross Correlation in Excel


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

Read More: How to Calculate Autocorrelation in Excel


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

Read More: How to Make Correlation Graph 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


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Excel Correlation | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo