The **correlation coefficient** is one of those things you come across very often in statistical analysis. The Pearson correlation coefficient is a simple, but effective way to express the linear **correlation between two variables**. Excel, being the data analysis tool it is, provides some excellent ways to calculate correlation coefficients. In this tutorial, I am going to show you how to calculate the Pearson correlation coefficient in Excel.

**Table of Contents**hide

## Download Practice Workbook

The dataset used for this example is attached below for reference. You can download it from here and try the steps yourself as you go through the methods.

## What Is Pearson Correlation Coefficient?

As mentioned earlier, the Pearson correlation coefficient is a measurement of linear correlation between two variables. Mathematically speaking, it is the ratio of the covariance and product of the standard deviation of the two variables. In the formula, the Pearson correlation coefficient(r) of two variables X and Y would be

Because of the nature of this formula, the correlation coefficient always results in a value between -1 to 1. In some cases, the value can be 0 indicating no correlation between the variables. For other extreme ones, a value of -1 or 1 indicates a perfect negative or positive linear correlation between the two. So, generally speaking, the closer the value is to 0, the lower the correlation. The further away from 0, the value is, the higher the correlation.

For example, shoe size and foot size as two variables will always have a perfect positive correlation. Take a look at the following dataset.

As the shoe size went up as the foot size went up, this indicates a positive linear correlation.

But, most of the variables in real-life scenarios don’t represent a correlation. In most cases, the correlation coefficient would be closer to zero. For example, let’s consider foot size with the IQ of the person.

As a result, the graph would look something like this, indicating no linear correlation between the two variables.

## 4 Easy Ways to Calculate Pearson Correlation Coefficient in Excel

In Excel, there are several methods to calculate the Pearson correlation coefficient. Firstly, you can calculate the values manually and find out the value of the formula described above. Also, Excel has two dedicated functions to find out correlations and Pearson correlations between variables. There is also an add-in tool you can use to find out correlations between two variables.

To demonstrate all the methods, I will be using the same dataset below.

I have gone through each method in their respective sub-sections. Follow each one to find the suitable one for you. Or if you have a particular one in mind find it in the table above.

### 1. Calculate Pearson Correlation Coefficient in Excel Manually

There is always the old school “by hand” method to calculate the Pearson correlation coefficient between two variables in Excel. In the event that you follow this method, you need to find out all the values of averages, differences, squares, and summations and calculate the Pearson correlation coefficient from them manually. I will be considering Age as X variable and BMI as variable Y from the formula and find the value of r. We will bump into the **AVERAGE****, ****SUM****, **and **SQRT** functions in this method.

For a detailed guide, follow these steps.

**Steps:**

- First, find the average age by writing the following formula. I have selected cell
**C12**to the value of average age.

`=AVERAGE(C5:C10)`

- Then find out the average BMI value by writing the following formula.

`=AVERAGE(D5:D10)`

I have selected cell **D12 **to store the value.

- Now, to find out
**X**, write down the following formula._{i}-X̅

`=C5-$C$12`

- Now, press
**Enter**. - Then, select the cell again. Now click and drag the
**Fill Handle Icon**to fill out the rest of the column.

- In a similar manner, find out the value of
**Y**by writing the following formula._{i}-Y̅

`=D5-$D$12`

- In like manner, press Enter. And then fill out the rest of the column with the
**Fill Handle Icon**.

- Now, find out the value of
**(X**by using the formula:_{i}-X̅)*(Y_{i}-Y̅)

`=F5*G5`

- Click and drag the
**Fill Handle Icon**to fill out the rest of the column as shown above.

- Now, find out
**(X**write down the following formula:_{i}-X̅)^{2}

`=F5*F5`

- Click and drag the
**Fill Handle Icon**to fill out the rest of the column.

- To find out the value of
**(Y**write down the following formula:_{i}-Y̅)^{2}

`=G5*G5`

- Click and drag the
**Fill Handle Icon**bar as shown above. It will fill out the values for the rest of the column.

- To find the sum of
**(X**write down the following formula. Then press_{i}-X̅)*(Y_{i}-Y̅)**Enter**.

`=SUM(H5:H10)`

- To find the sum of
**(X**and_{i}-X̅)^{2}**(Y**in this sheet, click and drag the_{i}-Y̅)^{2}**Fill Handle Icon**to the right to fill out the row with the same formula.

- Finally to find the Pearson Coefficient write down the following formula. I have used the cell references of the particular sheet.

`=H12/SQRT(I12*J12)`

- Now press
**Enter**on your keyboard. At this instant, you will have the Pearson Coefficient of the two variables (age and BMI) manually.

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

### 2. Using PEARSON Function to Calculate Pearson Correlation Coefficient

The basic method to find the Pearson correlation coefficient in Excel is a comparatively long process. On the contrary, Using functions to find out the Pearson correlation coefficient is a quick and easy process that leaves the long calculations to Excel and just finds out the value of the correlation coefficient of two variables.

Excel has a dedicated **PEARSON ****function** to find the correlation coefficient of two variables from the values. The function takes two arrays as arguments and returns the Pearson correlation coefficient.

For a more detailed guide, follow the steps.

**Steps:**

- First, select the cell you want to put the returned value in. I have selected cell
**D12**for demonstration.

- Write down the following formula in the cell.

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

- Then press
**Enter**on your keyboard. You will have the Pearson correlation coefficient directly.

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

### 3. Utilizing CORREL Function in Excel

There is another dedicated function to find out the value of correlation coefficients between two variables. It is **the CORREL function**. Just like the previous function, this function also takes two arrays as arguments and returns the correlation coefficient value of the two, which is the same as the Pearson correlation coefficient.

For a more detailed guide, follow these steps.

**Steps:**

- First, select the cell you want to store the value into. I have selected cell
**D12**here for that purpose.

- Then write down the following formula in the cell.

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

- Now, press
**Enter**on your keyboard. Thus, you will have the value of the correlation coefficient.

**Read More:** **How to Calculate P Value for Spearman Correlation in Excel**

### 4. Calculate Pearson Correlation Coefficient in Excel Using Add-ins

In addition to the previous methods described, Excel also has an add-in to find out the correlation coefficient between two variables. You need to add in the **Data Analysis **tool first to find out the coefficient in this method. This method is particularly helpful if you want a correlation coefficient between more than two variables at the same time.

Follow these steps to add the tool and find the correlation coefficient.

**Steps:**

- First, go to the
**File**tab in your ribbon and select**Options**. - An
**Excel Options**box will pop up. Select the**Add-ins**tab from it. - Besides
**Manage**, make sure the**Excel Add-ins**option is selected. - After that click on
**Go**.

- Next, in the
**Add-ins**box that appeared, check the**Analysis Toolpak**. After that, click on**OK**. Now you will have your**Data Analysis**tools available.

- Now, go to the
**Data**tab in your ribbon. - Then, in the
**Analysis**group, select**Data Analysis**.

- Next, in the
**Data Analysis**box, select**Correlation**from the**Analysis Tools**. Then click on**OK**.

- In the
**Correlation**box, select the input range**$C$4:$D$10**. - Then in the
**Grouped By**field, select**Columns**as the variables are grouped as columns. - After that, check the
**Labels in first row**. - Then in the
**Output options,**select**Output Range**. I have selected**$B$12**here.

- Finally, click on
**OK**. You will have all the correlation values between the variables.

In this example, there are only two variables. So the correlation coefficients are displayed as a 2X2 **matrix** with all the combinations of variables.

**Read More:**** How to Make a Correlation Matrix in Excel (2 Handy Approaches)**

## Conclusion

That concludes the guide on how to calculate the Pearson correlation coefficient in Excel. Hope you have found this guide helpful and informative. If you have any questions or suggestions for us, let us know below. For more guides like this, visit **Exceldemy.com**.

## Related Articles

**How to Interpret Correlation Table in Excel (A Complete Guideline)****Make a Correlation Scatter Plot in Excel (2 Quick Methods)****How to Calculate Autocorrelation in Excel (2 Ways)****Calculate Cross Correlation in Excel (2 Quick Ways)****How to Do Correlation in Excel (3 Easy Methods)****Calculate Spearman Correlation in Excel (3 Easy Methods)**

helpt me a lot when im doign my very exting ict wwork q ½.