How to Calculate Correlation Coefficient in Excel

Share on facebook
Share on twitter
Share on pinterest

In experiments, the next step after gain variables is finding relevance intensity among them. Excel makes the calculations easy by using the functions or data analysis tools In this tutorial, We’ll know what the correlation coefficient is and how it works and then clarify the calculation process in Excel.

What Is the Correlation Coefficient?

The correlation coefficient (a value between -1 and 1) tells you about the relationship between the two variables.

How the Correlation Coefficient shows the relationship between two variables?
Picture 1- The Correlation Coefficient

According to the picture () 

  • -1≤ρ<0: means the negative linear correlation (a negative direction relevance between variables)
  • 0<ρ≤1: means the positive linear correlation (a positive direction relevance between variables)
  • ρ=0: means no linear correlation (no relevance between variables)

Another way to figure the relationship between two variables is Covariance. But the amount of Covariance is affected by the units of variables measurement, making it challenging to compare large numbers. To standardize the amount of dependence between two variables, we use the correlation coefficient.

The Correlation Coefficient Formulas

We have two formulas for calculating the correlation coefficient in Excel.

  1. CORREL
  2. PEARSON

The CORREL formula is more accurate than the PEARSON function and is especially suitable for huge numbers.

Calculate the Correlation Coefficient by the CORREL Function

Method one:

Here we have two sets of data. So, to calculate the correlation coefficient, follow these steps:

  1. Select the cell that you want to put the result.
  2. Go to the Formulas tab.
  3. Select the More Function button.
  4. From the Statistical menu, pick the CORREL function.
  5. Now, select the Array1 (first set of data) and Array2 (second set of data).
  6. Press OK.
”Use the CORREL function to calculate the correlation coefficient.
Picture 2- The CORREL function
the-correlation-function-2.jpg” alt=”Use the CORREL function to calculate the correlation coefficient
Picture 3- The CORREL function (II)

Method two:

  1. Select an empty cell.
  2. Enter = to write the CORREL syntax.
  3. Select the Array1 (first set of data) and Array2 (second set of data).
  4. Press Enter.
Enter the CORREL syntax to calculate the correlation coefficient.
Picture 4 – The CORREL syntax

Calculate the Correlation Coefficient by the PEARSON Function

Calculating the correlation coefficient by the PEARSON function is exactly like the way you use the CORREL function. Here, we have two sets of data, and we have two ways to enter the PEARSON function.

Method one:

  1. Select the cell that you want to put the result.
  2. Go to the Formulas tab.
  3. Select the More Function button.
  4. From the Statistical menu, pick the PEARSON function.
  5. Now, select the Array1 (first set of data) and Array2 (second set of data).
  6. Press OK.
Use the PEARSON function to calculate the correlation coefficient.
Picture 5- The PEARSON function
alt=”Use the PEARSON function to calculate the correlation coefficient
Picture 6- The PEARSON function(II)

Method two:

  1. Select an empty cell.
  2. Enter = to write the PEARSON formula.
  3. Select the Array1 (first set of data) and Array2 (second set of data).
  4. Press Enter.
Enter the PEARSON syntax to calculate the correlation coefficient
Picture 7- The PEARSON syntax

Calculate the Correlation Coefficient by the Data Analysis

First, to enable the Data Analysis to follow these steps:

  1. Go to the File tab from the ribbon.
  2. Click on the Option
  3. Click on the Add-ins.
  4. From the Manage menu bar, select Excel Add-ins, and press Go.
  5. Check the Analysis ToolPak checkbox and press OK.
Video 1- Enable the Data Analysis in Excel

Now you can find the Data Analysis option on the Data tab from the ribbon. So, Calculate the Correlation Coefficient:

  1. Go to the Data tab. 
  2. From the Analysis group, select the Data Analysis.
  3. From the Data Analysis dialogue box, pick the Correlation, and press OK.
  4. In the Correlation dialogue box, select the data range as the input range.
  5. Check the columns or rows checkbox according to your data.
  6. If your data sets have labels, check the Labels in first row checkbox.
  7. Select an empty range to enter the Output range to see the result and press OK.
Use the Correlation tool from the Data Analysis tools to calculate the correlation coefficient.
Picture 8- The Correlation tool from the Data Analysis tools
How to use the Correlation tool from the Data Analysis tools to calculate the correlation coefficient?
Picture 9- The Correlation tool
the-correlation-tool-result.jpg” alt=”The result of using the Correlation tool from the Data Analysis tools.
Picture 10- The Correlation tool result

You can connect with us and ask our experts for your inquiries and get more Excel Support Services.

Reduce costs, accelerate tasks, and improve quality with Excel Automation Services.

Subscribe to our Newsletter

Share this post with your friends

Share on facebook
Share on google
Share on twitter
Share on linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *