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

Get FREE Advanced Excel Exercises with Solutions!

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. Read More: How to Calculate P Value for Spearman Correlation in Excel

## 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. Read More: How to Calculate Spearman Correlation in Excel (3 Easy Methods)

## 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. And please visit our website Exceldemy to explore more.

## Related Articles Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  