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

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.

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.

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

I have selected cell D12 to store the value.

• Now, to find out X_i-X̅, write down the following formula.

• 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_i-Y̅ by writing the following formula.

• 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_i-X̅)*(Y_i-Y̅) by using the formula:

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

• Now, find out (X_i-X̅)² write down the following formula:

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

• To find out the value of (Y_i-Y̅)² write down the following formula:

• 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_i-X̅)*(Y_i-Y̅) write down the following formula. Then press Enter.

• To find the sum of (X_i-X̅)²and (Y_i-Y̅)² in this sheet, click and drag the 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.

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

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

• 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)