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

**Table of Contents**hide

## Download Practice Workbook

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

## 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**

## 2 Ways to Find Spearman Rank Correlation Coefficient in Excel

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

❶ 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**

**Similar Readings**

**How to Do Correlation in Excel (3 Easy Methods)****Make Correlation Graph in Excel (with Easy Steps)****How to Calculate Intraclass Correlation Coefficient in Excel****Calculate Cross Correlation in Excel (2 Quick Ways)****How to Calculate Autocorrelation in Excel (2 Ways)**

### 2. Using Traditional Formula to Find Spearman Rank Correlation Coefficient in Excel

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

## Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.

## 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

**How to Make a Correlation Scatter Plot in Excel (2 Quick Methods)****Calculate Correlation Coefficient in Excel (3 Methods)****How to Interpret Correlation Table in Excel (A Complete Guideline)****Calculate Pearson Correlation Coefficient in Excel (4 Methods)****How to Make a Correlation Matrix in Excel (2 Handy Approaches)****Find Correlation between Two Variables in Excel**