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

**Table of Contents**hide

## Basics of Correlation Coefficient

The correlation coefficient defines the strength of the 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 the positive correlation coefficient is greater than 0.

**Negative correlation coefficient** defines that when one variable increases, another variable decreases. The value of the 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.

## How to Calculate Correlation Coefficient in Excel: 3 Methods

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**.

`=CORREL(C5:C10,D5:D10)`

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 a 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 the** CORREL Function** table.

**Read More: **How to Calculate Spearman Correlation in Excel

### 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

### Method-3: Using PEARSON Function

Here, we will use the **PEARSON function** to calculate the correlation coefficients in the following using the** 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**.

`=PEARSON(C5:C10,D5:D10)`

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 the** PEARSON Function** table.

**Read More:** How to Calculate Pearson Correlation Coefficient in Excel

**Download Workbook**

## 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.