How to Calculate Correlation Coefficient in Excel (3 Methods)

There are several methods to calculate the correlation coefficient in Excel. Among them, we will demonstrate to you 3 simple, easy, and effective methods.

Download Workbook

Basics of Correlation Coefficient

The correlation coefficient defines the strength of relationship between two variables.

There are 3 types of correlation between data, these are positive correlation, negative correlation, and no relationship.

Positive correlation coefficient defines that when one variable increases, another variable also increases. The value of positive correlation coefficient is greater than 0.

Negative correlation coefficient defines that when one variable increases, other variable decreases. The value of negative correlation coefficient is less than 0.

No relationship correlation coefficient defines that there is no relationship between two variables, and the value is close or equal to 0.

3 Methods to Calculate Correlation Coefficient in Excel

The following Employee List table shows the Name, Age, Hours of Work, and Salary columns. We will find out the correlation coefficient between Age, Hours of Work, and Salary. Here, we used Excel 365. You can use any available Excel version.

Method-1: Using CORREL Function to Calculate Correlation Coefficient

Here, in the following Using CORREL Function table, we will calculate the correlation coefficient by using the CORREL function. In cells H5, I6, and J7 we put the value 1, as the correlation coefficient between two same variables is 1. Now, we have to calculate the correlation coefficient for the other cells.

➤ First of all, to find the correlation coefficient between Age and Hours of Work, we have to type the following formula in cell I5.

Here, the CORREL function returns the correlation coefficient of two cell ranges.

C5:C10 is array 1, and D5:D10 is array 2.

➤ After that, press ENTER.

Now, we can see the correlation coefficient in cell I5, the value is greater than 0. Hence, we know that the correlation coefficient between Age and Hours of Work is positive.

➤ In the similar way, we use the CORREL function in other cells to find out the correlation coefficient between variables.

Finally, we can see the result in the following Using CORREL Function table.

Read More: How to Do Correlation in Excel (3 Easy Methods)

Method-2: Calculate Correlation Coefficient Using Data Analysis Tool

we can calculate the correlation coefficient in a quicker and easier way by using the Data Analysis tool. In this method, we’re going to use that analysis tool to calculate the coefficient.

➤ First of all, we will go to the Data tab > select Data Analysis.

Afterward, a Data Analysis window will appear.

➤ Select Analysis Tools as Correlation > click OK.

After that, a Correlation window will appear.

➤ Give the location of the Input Range and Output Range by clicking on the upward arrow > click OK.

➤ After that, we selected Input Range as $C$4:$E$10 > as our data has a header, we mark on Labels in first row > selected Output Range as $G$4:$I$10 > click OK.

Finally, we can see the correlation coefficients in the following table.

Read More: How to Find Spearman Rank Correlation Coefficient in Excel (2 Ways)

Similar Readings

• How to Calculate Autocorrelation in Excel (2 Ways)
• Make Correlation Graph in Excel (with Easy Steps)
• How to Calculate Spearman Correlation in Excel (3 Easy Methods)
• Find Correlation between Two Variables in Excel
• How to Calculate Partial Correlation in Excel (3 Suitable Ways)

Method-3: Using PEARSON Function

Here, we will use the PEARSON function to calculate the correlation coefficients in the following Using Pearson Function table.

Here, we put value 1 in cells where the two variables are the same.

➤ First of all, we will type the following formula in cell I5 to find out the correlation coefficient between Age and Hours of Work.

Here,

PEARSON returns pearson product-moment correlation coefficient that ranges from -1,0 and 1.

The first array is C5:C10, and the 2nd array is D5:D10.

➤ After that, press ENTER.

Now, we can see the result in cell I5.

Here, the correlation coefficient in cell I5 is greater than 0, which indicates a positive correlation.

➤ After that, we will use the PEARSON function in other cells as well to calculate correlation coefficients.

Finally, we can see the result in the following Using PEARSON Function table.

Read More: How to Calculate Pearson Correlation Coefficient in Excel (4 Methods)

Conclusion

Here, we tried to show you 3 methods to calculate the correlation coefficient in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, feel free to know us in the comment section.

Related Articles

• How to Make a Correlation Scatter Plot in Excel (2 Quick Methods)
• Interpret Correlation Table in Excel (A Complete Guideline)
• How to Calculate P Value for Spearman Correlation in Excel
• Make a Correlation Matrix in Excel (2 Handy Approaches)
• How to Calculate Intraclass Correlation Coefficient in Excel