How to do Spearman correlation in Excel

Spearman correlation - the basics

The Spearman correlation is the nonparametric version of the Pearson correlation coefficient that measure the degree of association between two variables based on their ranks.

The Pearson Product Moment Correlation tests the linear relationship between two continuous variables. Linear means a relationship when two variables change in the same direction at a constant rate.

Spearman Rank Correlation evaluates the monotonic relationship between the ranked values. In a monotonic relationship, the variables also tend to change together, but not necessarily at a constant rate.

How to calculate Spearman correlation in Excel with CORREL function

Regrettably, Excel does not have an inbuilt function for calculating the Spearman rank correlation coefficient. However, that does not mean you will have to rack your brain with the above formulas. By manipulating Excel a bit, we can come up with a much simpler way to do Spearman correlation.

As an example, let's try to find out if our physical activity has any relation to our blood pressure. In column B, we have the number of minutes that 10 men of the same age spend daily in a gym, and in column C, we have their systolic blood pressure.

To find the Spearman correlation coefficient in Excel, perform these steps:

1. Rank your data

   Because the Spearman correlation evaluates the associations between two variables based on their ranks, you need to rank your source data. This can be quickly done by using the Excel RANK.AVG function.

   To rank the first variable (physical activity), enter the below formula in D2 and then drag it down to D11:

   =RANK.AVG(B2,$B$2:$B$11,0)

   To rank the second variable (blood pressure), put the following formula in cell E2 and copy it down the column:

   =RANK.AVG(C2,$C$2:$C$11,0)

   For the formulas to work correctly, please be sure to lock the ranges with absolute cell references.

   At this point, your source data should look similar to this:
   

2. Find Spearman correlation coefficient
   With the ranks established, we can now use the Excel CORREL function to get Spearman's rho:

   =CORREL(D2:D11, E2:E11)

   The formula returns a coefficient of -0.7576 (rounded to 4 digits), which shows a fairly strong negative correlation and allows us to conclude that the more a person exercises, the lower their blood pressure.

   The Pearson correlation coefficient for the same sample (-0.7445) indicates a bit weaker correlation, but still statistically significant:
   

The beauty of this method is that it is quick, easy, and works regardless of whether there are ties in the ranking or not.

Calculate Spearman correlation coefficient in Excel with traditional formula

If you are not quite sure that the CORREL function has computed Spearman's rho right, you can verify the result with the traditional formula used in statistics. Here's how:

1. Find the difference between each pair of ranks (d) by subtracting one rank from the other:

   =D2-E2

   This formula goes to F2 and is then copied down the column.

2. Raise each rank difference to the power of two (d²):
   =F2^2

   This formula goes to column G.

3. Add up the squared differences:

   =SUM(G2:G11)

   This formula can go to any blank cell, G12 in our case.

   From the following screenshot, you will probably gain better understanding of the data arrangement:
   

4. Depending on whether your data set has any tied ranks or not, use one of these formulas to calculate the Spearman correlation coefficient.

In our example, there are no ties, so we can go with a simpler formula:

With d² equal to 290, and n (number of observations) equal to 10, the formula undergoes the following transformations:

As the result, you get -0.757575758, which perfectly agrees with the Spearman correlation coefficient calculated in the previous example.

In Microsoft Excel, the above calculations can be performed with the following equation:

=1-(6*G12/(10*(10^2-1)))

Where G12 is the sum of the squared rank differences (d²).

How to do Spearman correlation in Excel using a graph

The correlation coefficients in Excel only measure linear (Pearson) or monotonic (Spearman) relationships. However, other associations are possible. So, no matter which correlation you do, it is always a good idea to represent the relationship between the variables in a graph.

To draw a correlation graph for the ranked data, here's what you need to do:

1. Calculate the ranks by using the RANK.AVG function as explained in this example.
2. Select two columns with the ranks.
3. Insert an XY scatter chart. For this, click the Scatter chart icon on the Inset tab, in the Chats group.
4. Add a trendline to your chart. The fastest way is to click the Chart Elements button > Add Trendline….
5. Display R-squared value on the chart. Double-click the trendline to open its pane, switch to the Trendline Options tab and select the Display R-squared value on chart box.
6. Show more digits in the R² value for better accuracy.

As the result, you will get a visual representation of the relationship between the ranks. Additionally, you will get the Coefficient of Determination (R²), the square root of which is the Pearson correlation coefficient (r). But because you have plotted the ranked data, this Pearson's r is nothing else but Spearman's rho.

In our case, R² equals 0.5739210285. Use the SQRT function to find the square root:

=SQRT(0.5739210285)

…and you will get the already familiar coefficient of 0.757575758.

The downward slope in the graph exhibits a negative correlation, so we add the minus sign and get the correct Spearman correlation coefficient of -0.757575758.

Practice workbook

Spearman Rank Correlation in Excel (.xlsx file)