In this guide, I will show you how to perform a Pearson correlation test in Microsoft Excel. This includes determining the Pearson correlation coefficient as well as a p value for the statistical test.

I have discussed how to perform a Spearman’s rank correlation test in Excel previously.

## What is a Pearson correlation test?

A Pearson correlation is a statistical test to determine the association between two continuous variables.

The output is given as the Pearson correlation coefficient (r) which is a value ranging from -1 to 1 to indicate the strength of the association.

The following values of r indicate the direction and strength of the association.

**r = -1**: A perfect negative association**r = 0**: No association**r = +1**: A perfect positive association

If you want to learn more about about the test, including the test assumptions, then check out my Pearson correlation explained article.

## How to perform a Pearson correlation test in Excel

In Excel, there is a function available to calculate the Pearson correlation coefficient. However, there is no simple means of calculating a p-value for this. A way around this is to firstly calculate a t statistic which will then be used to determine the p-value.

### 1. Calculate the Pearson correlation coefficient in Excel

In this section, I will show you how to calculate the Pearson correlation coefficient in Excel, which is straightforward.

In Excel, click on an empty cell where you want the correlation coefficient to be entered. Then enter the following formula.

=PEARSON(array1, array2)

Simply replace ‘**array1**‘ with the range of cells containing the first variable and replace ‘**array2**‘ with the range of cells containing the second variable.

For the example above, the Pearson correlation coefficient (r) is ‘**0.76**‘.

### 2. Calculate the t-statistic from the coefficient value

The next step is to convert the Pearson correlation coefficient value to

In order to determine the number of pairs, simply count them manually or use the count function (**=COUNT**). Each pair should be a pair, so remove any entries that are not a pair.

The equation used to convert r to the t-statistic can be found below.

The formula to do this in Excel can be found below.

=(r*SQRT(n-2))/(SQRT(1-r^2))

Simply replace the ‘**r**‘ with the correlation coefficient value and replace the ‘**n**‘ with the number of observations in the analysis.

For the example in this guide, the formula used in Excel can be seen below.

Note, if your coefficient value is negative, then use the following formula:

=(ABS(r)*SQRT(n-2))/(SQRT(1-ABS(r)^2))

The addition of the ABS function converts the coefficient value to an absolute (positive) number. Otherwise, a negative coefficient value will bring up an error.

### 3. Calculate the p-value from the t statistic

The final step in the process of calculating the p-value for a Pearson correlation test in Excel is to convert the t-statistic to a p-value.

Before this can be done, we just need to calculate a final piece of information: the number of degrees of freedom (DF). The DF can be found by subtracting 2 from n (**n – 2**).

Now we are ready to calculate the p-value. To do this, simply use the **=TDIST** function in Excel.

Simply enter the formula below.

=TDIST(x, deg_freedom, tails)

Replace the ‘**x**‘ with the t statistic created previously and replace the ‘**deg_freedom**‘ with the DF. Finally, for the tails, enter the number ‘**1**‘ for a one-tailed analysis or a ‘**2**‘ for a two-tailed analysis. If you are unsure about which to use, use a two-tailed analysis (‘**2**‘).

Below is a screenshot for how this looks in Excel by using the example.

In the example, the p value is ‘**0.006**‘. Therefore, there is a significant positive correlation (r=0.76) between participant ages and their BMI.

## Conclusion

There is no easy way to calculate a

Microsoft Excel version used: 365 ProPlus

Thank you so much Steven, you saved my and my research! Just one question: as Spearman’s rank correlation coefficient can be easily calculated with Excel, can I apply the same procedure to get the p-value for Spearman? Thank you!

Hi Raf,

Thanks for your feedback 🙂

I’ve also got a Spearman rank tutorial in Excel; see here: https://toptipbio.com/spearman-correlation-excel/

Spearman rank has a few different steps at the beginning to ‘rank’ the data.

All the best

Steven

Thank you so much. very useful for me. i have applied in my work

Thanks so much for breaking it down like this! Definitely helpful.

Very useful and I used it in my works. thanks a lot.

Fantastic! Thank you! This is exactly what I needed. I do not have a stats package on my computer and I have been trying to wrangle a lot of data. Cheers!

Many thanks Susan 🙂