How to Find Spearman Rank Correlation Coefficient in Excel (2 Ways)

In this article, you will learn 2 different ways to find the Spearman rank correlation coefficient in Excel.

What Is Spearman Rank Correlation Coefficient?

The Spearman rank correlation coefficient is a measure of how closely two variables are related. It’s utilized when dealing with ordinal data, or data that can be sorted from least to greatest. By squaring the difference between the ranks of the two variables, the Spearman rank correlation coefficient is computed. It is a measure of how strong the two variables’ link is.

It is generally represented by the symbol, ρ (“rho”). Here’s the traditional formula of the Spearman rank correlation coefficient.

Here,

ρ is the Spearman rank correlation coefficient
dᵢ is the subtraction between the ranks of the two variables
n is the total number of cases

1. Using CORREL Function to Find Spearman Rank Correlation Coefficient in Excel

We can use the CORREL function to determine the relation between two variables. This is exactly what we need to find the Spearman rank correlation coefficient in Excel.

Here are the steps to follow:

❶ Insert the following formula using the RANK.AVG function in cell D5.

=RANK.AVG(B5,$B$5:$B$11,1)

❷ Then press the ENTER button.

Formula Breakdown

B5 is the first cell of the column Exercise Period.
$B$5:$B$11 is the range of the column Exercise Period.
1 is used to rank in ascending order.

❸ Drag the Fill Handle icon from cell D5 to D11.

So we got the relative ranking of the data in the Exercise Period column.

❹ Again insert the following formula in cell E5.

=RANK.AVG(C5,$C$5:$C$11,1)

❺ Press ENTER.

Formula Breakdown

C5 is the first cell of the column Heart Rate.
$C$5:$C$11 is the range of the column Heart Rate.
1 is used to rank in ascending order.

❻ Drag the Fill Handle icon from cell E5 to E11.

Now we got the relative ranking of the data in the Heart Rate column.

Now it’s time to find the Spearman rank correlation coefficient using the CORREL function.

❼ Insert the following formula in cell E13.

=CORREL(D5:D11,E5:E11)

Formula Breakdown

D5:D11 is the range of the column Exercise Period.
E5:E11 is the range of the column Heart Rate.

❽ Now press the ENTER button.

So you will get the Spearman rank correlation coefficient in cell E13.

2. Finding Spearman Rank Correlation Coefficient with Traditional Excel Formula

The traditional formula to determine the Spearman rank correlation coefficient is,

Now I will show you how to use this formula to find the Spearman rank correlation coefficient in Excel.

❶ At first, insert the following formula in cell F5.

=(D5-E5)^2

This formula calculates the square of the difference between the ranking of two variables.

To learn how to get the ranking, click_here.

❷ Then press the ENTER button.

❸ Drag the Fill Handle icon from cell F5 to F11.

The result will be like this:

❹ Now insert the following formula in cell E13.

=1-(6*SUM(F5:F11)/(7*(7^2-1)))

Formula Breakdown

SUM(F5:F11) returns the summation of all values from cells F5 to F11.
1-(6*SUM(F5:F11) is the numerator of the traditional formula of Spearman rank correlation coefficient.
7 is the total number of cases
(7*(7^2-1))) is the denominator of the traditional formula of Spearman rank correlation coefficient.

❺ Finally, press ENTER.

Now you will get the Spearman rank correlation coefficient in cell E13.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Calculate Spearman Rank Correlation Coefficient.xlsx

Conclusion

To sum up, we have discussed 2 ways to calculate Spearman’s rank correlation coefficient in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.