Spearman’s Rank Correlation Hypothesis Testing

On this webpage we show how to use Spearman’s rank correlation for hypothesis testing. In particular, we show how to test whether there is a correlation between two random variables by testing whether or not Spearman’s rho = 0 (the null hypothesis).

For low values of rho, a table of critical values can be used (see Spearman’s Rho Table). For higher values (generally about n > 10), Theorem 1 of Correlation Testing via t Test and Theorem 1 of Correlation Testing via Fisher Transformation is applied using Spearman’s rho in place of Pearson’s correlation r.

In general, however, Kendall’s tau is often the preferred non-parametric approach since it has more desirable statistical properties.

Example 1: Repeat the analysis for Example 1 of Correlation Testing via t Test using Spearman’s rho, i.e. test whether Spearman’s rho is significantly different from zero based on the sample data in range B4:C18 of Figure 1.

Spearman's rho hypothesis testing

Figure 1 – Hypothesis testing of Spearman’s rho

Spearman’s rho is the correlation coefficient on the ranked data, namely CORREL(D4:D18,E4:E18) = -.674. Alternatively it can be computed using the Real Statistics formula =SCORREL(D4:D18,E4:E18).

We now use the table in Spearman’s Rho Table to find the critical value of .521 for the two-tail test where = 15 and  = .05. Since the absolute value of rho is larger than the critical value, we reject the null hypothesis that there is no correlation.

Since n = 15 ≥ 10, we can use a t-test instead of the table. By Theorem 1 of Correlation Testing via t Test, we use the test statistic


Since |t| = 3.29 > 2.16 = tcrit = TINV(.05,13), we again conclude that there is a significant negative correlation between the number of cigarettes smoked and longevity. The details of the analysis are shown in Figure 2.

For Excel 2007 users, replace the formula in cell H11 by TINV(H10,H7) and the formula in cell H12 by TDIST(ABS(H9),H7,2).

Observation: To conduct a one-tail test use the table in Spearman’s Rho Table with α multiplied by 2.

Real Statistics Excel Function: The following function is provided in the Real Statistics Resource Pack:

RhoCRIT(n, α, tails, h) = the critical value of the Spearman’s rho test for samples of size n, for the given value of alpha (default .05), and tails = 1 or 2 (default). If h = TRUE (default) harmonic interpolation is used; otherwise linear interpolation is used.

SCORREL(R1, R2, lab, tails, alpha): an array function which outputs a column range consisting of Spearman’s correlation coefficient (rho) and the t-stat and p-value which test the null hypothesis that rho = 0. If lab = TRUE then a column of labels are added to the output (default is FALSE). tails = 1 or 2 (default), alpha = significant level (default .05)

For Example 1, RhoCRIT(15, .05, 2) = .521, as described above.

For Example 1 of Spearman’s Correlation, SCORREL(B4:B18,C4:C18,TRUE) returns the output shown in Figure 2.

Spearman's rho function output

Figure 2 – Output from SCORREL function

52 Responses to Spearman’s Rank Correlation Hypothesis Testing

  1. jana says:

    Hi! Thanks for a great tool + instructions! I noticed that SCORREL spits out the error message #VALUE! when the rank of parameters x and y are the same and the spearman rho is 1.0. What does this mean? Is it not possible to calculate a p value if the correlation is supersignificant? How do I interpret/report this? Perhaps the issue is that I am working with n < 15? Would it be more useful to perform a different type of test?

    Many thanks,


    • Charles says:

      With the data I am using I don’t see an error value.
      If you send me an Excel file with your data and calculations I will try to figure out what is going on.
      You can find my email address at Contact Us.

  2. Eugene says:

    Hi Charles,

    I followed the installation process and successfully added RealStats-2007. However,
    it doesn’t show on my Add-Ins. The Solver doesn’t show either. I cant figure what the problem is since both add-ins appear as Active Add-Ins at Windows Button….Excel Options…..Add-Ins. I have been using SigmaPlot and “Resampling Stats for Excel” add-ins for several months.
    I need some help.

    • Charles says:

      When you say that Solver doesn’t show on your Add-Ins, are you referring to the list of addins that is displayed when you press Alt-TI? It would would surprising that Solver doesn’t show on this list at all.

  3. Abdelkader says:

    Hi Charle,
    Really you’re doing a great work.
    I would participate and suggest a formula to get the exact rank.
    In cell D4 the formula is:
    =((COUNTIF(B$4:B$100,”=” & B4)*(COUNTIF(B$4:B$100,”<" & B4)+1))+((COUNTIF(B$4:B$100,"=" & B4)))*(COUNTIF(B$4:B$100,"=" & B4)-1)/2)/COUNTIF(B$4:B$100,"=" & B4)
    In cell E4 same formula just replace column B with C
    Pls try it and let me know your view point.

  4. GERARDO says:

    Dr. Buenas noches, escuseme, como puedo determinar la correlación de Spearman con tres o más variables?.

    Dr Good evening, excuse me ¿how can I do a Spearman Correlation with three or more variables?

  5. Jan says:

    The =SCORREL($L$22:$L$37;Q22:Q37;TRUE) just returns “roh”, but not all the values and fields. Am I using it wrong?

    Anyway, Thank you for the Statistics Package.

  6. The knowledge shared is so much helpful. Keep it up.
    As a Lecturer in the Statistics department I do appreciate.

    By Narris Twesigye.

  7. Mary says:

    How does one interpret the spearman statistical test result if the statistical p-value is equal to the alpha value (i.e. statistical p=0.05 while alpha=0.05) although the spearman rho calculated (absolute) is found to be less than the tabulated (critical) spearman rho?

    • Charles says:

      The value of alpha = .05 is a somewhat arbitrary number, and so in any case, the best thing is to simply report the p-value without worrying too much as to whether it is a significant result or not. It is obviously significant for alpha of.051 and not significant for alpha of .025.

      Regarding the critical value, what was your calculated Spearman’s rho value and what did the table say was the critical value? Given a p-value of .05, I presume that the calculated and critical rho values are almost the same. If not, probably something is wrong.


  8. Hoplite says:


    (re-posting as the punctuation messed it up.)

    Thanks for a great website – I am making my way through it. You make a complex subject easy to understand.

    I am puzzled by this example of life expectancy and cigarettes in figure 2. As I understand it H0 is that they are uncorrelated. The alpha is 0.05 and the p-value of the spearman rho statistic is 0.005821. By comparing the t stat to the critical t you conclude that H0 should not be rejected as t stat is less than t crit (i.e. they are uncorrelated). Clearly they are correlated as a simple xy graph will show. Should you not be comparing H12 to H10 and concluding that it is significant and rejecting the null hypothesis. Elsewhere on your website for significance you talk about comparing abs(rho) to rho-crit in order to check for significance and clearly here abs(-0.674) greater than 0.521 so it is significant. Should you be comparing abs(t stat) to t-crit?

    Or have I mis-understood something here?!

    Thanks again for a great website.

    • Charles says:

      In your comment you state that “By comparing the t stat to the critical t you conclude that H0 should not be rejected”.

      I don’t see where this is stated. On the webpage I clearly state that the null hypothesis should be rejected.


      • Hoplite says:


        Sorry I wasn’t very clear. In figure 2 above in cell H13 the result is ‘no’ for significance.

        • Charles says:

          Yes, you are correct. I have now replaced Figure 2 with one that contains the correct formula. Thanks for identifying this error.

  9. Waqas says:

    Dear Sir
    please tell me what should be the answer if values remain same. i mean if i take values 1,1,1,1,1 for variable A and 1,1,1,1,1 for variable B in spearman rho correlation.

    • Charles says:

      It will be undefined since the variance of each sample is 0 and so the denominator of the formula used to compute the correlation coefficient will be zero.

  10. Tim says:

    Hi Charles. Thanks for making Real-Statistics available to all. Do you know of a way to use SCORREL when there are missing data? For example suppose we have want to calculate SCORREL(A5:A19,B5:B19,TRUE) but we delete the observation in cell B10. Is there an easy way to do this? I can revise to SCORREL((A5:A9,A11:A19),(B5:B9,B11:B19),TRUE) to do the calculation but I have 100s of correlations to calculate and there are random missing values scattered throughout my data set. Any thoughts would be appreciated. Thanks, Tim.

    • Charles says:


      Suppose your data is stored say in range A1:B100 and you are trying to calculate SCORREL(A1:A100,B1:B100). Highlight range D1:E100 and enter the array formula =DELROWBLANK(A1:B100) in the highlighted range and press Ctrl-Shft-Enter. The result will be the same data with any pairs with one or two empty cells removed. Now suppose 10 rows were removed (thus the range D91:E100 contains #N/A). The result you are now looking for can be calculated using the formula =SCORREL(D1:D90,E1:E90).

      You can also use the Reformatting a Data Range by Rows data analysis tool to remove rows with empty cells.

      The above assumes that missing data is equivalent to empty cells. If instead missing data is equivalent to some non-numeric value (e.g the word “missing”) then you would use the array formula DELROWNonNum instead.

      In a future release I will automatically remove non-numeric entries from the data before calculating SCORREL.


  11. Veton says:

    Thank you Charles, but could you tell me how exactly can I use SCORREL function to correlate the questionnaire and the observation?

    • Charles says:

      The referenced webpage describes how to use the SCORREL function or the Correlation data analysis tool to show the correlation between between two paired data sets. If you need need further information, you need to explain further what additional information you need.

  12. Veton says:

    Hi! Could somebody please help on my Master`s thesis I am doing. I have an observation instrument and a questionnaire to compare and make a correlation between them based on some categories and points. I need Spearman`s correlation function to do this in Excel 2007. I would be so grateful if you could help me.

    • Charles says:

      You can do this as explained on the referenced webpage. In particular, you can use the SCORREL function found in the Real Statistics Resource Pack. There is a version for Excel 2007 which you can download for free.

  13. Richard says:

    Excel freezes everytime i try using the =Scorrel function!! i really do need it, what should i do?? thanks!

  14. Charles,

    I’m experimenting with the Spearman Rho, using ranked data that contain ties. The examples given in text books use rank date with no ties. My interest is comparing the rho value using SCORREL and the conventional 1-sum(d^2)/n(n^2-1) formula.



  15. Mart says:

    Dear Charles,
    The clarity of your presentation and examples combined with the comprehensive cross-referencing of all relevant techniques make your site a real winner. After 25 years without touching stats it has been a tough week getting back into it. I have fought with the examples on many other sites but the simplicity of your presentation has allowed me to quickly overcome the hurdles I had faced elsewhere. Thank-you for your outstanding work,

  16. natalie says:

    i am now doing a proyect based on spearman and i have come to a problem, what should i do if i have only one sample instead, i have nothing to compare that sample with. i was wondering how could i calculate it, if its possible to do so.

    • Charles says:

      Correlation coefficients such as Spearman’s only make sense if there are at least two data sets. Sorry, but I don’t know of any way to calculate this coefficient with only one sample.

  17. Lalit says:

    Sir, the command for finding critical value of t statistic ie.RhoCrit do not work if n= 4:30. Please give suggetion, because it takes more time to get critical value in other way.

  18. Kate says:

    Sir, thank You for such perfect resource!
    Just one question. Sir, in case of significant amount of the connected ranks does the spearsman rho show still correct result? Starting from which number of connected rank groups the correction for spearsman rho coefficient should be applied? or it will be not applicable at all?

    • Charles says:

      By connected ranks do you mean tied ranks?

      • Lily says:

        Yes, could you please explain how you would do this if there are tied ranks in one of the values. I know there is a formula, but I don’t understand how exactly you would substitute values into that formula.

        • Charles says:

          If there are no ties you can use Property 1 of the referenced page to calculate Spearman’s rho. The more ties, the less accurate the results.
          Even if there are ties, however, you can use the approach shown in Example 3 of the referenced page (using RANK.AVG and CORREL). I believe this approach produces the correct result even if there are a lot of ties.

    • Charles says:

      If there are no ties you can use Property 1 to calculate Spearman’s rho. The more ties, the less accurate the result. In any case you can use the approach shown in Example 3 of the referenced page. I believe this approach produces the correct result even if there are a lot of ties.

  19. kirk barrett says:

    thanks for the explanation.

    I think it would be a little clearer if you explained that even tho
    its called “spearman’s rho”, if is often represented r with a subscript of s (to distinguish it from pearson’s corr. coeff)

    Also the calculation of t shows “r” (implying pearson’s coef)
    when i believe it should be “r sub s”

    • Charles says:

      You are correct. I actually used r with a subscripted s in one place on the webpage but not in the others. I have now made this change. Thanks for your valuable suggestions.

  20. Vinh says:

    What is the different between Pearson’s correlation and Spearman’s rho ?
    What’s the correlation we should use?


    • Charles says:

      Pearson’s correlation is the usual correlation coefficient calculated on the raw data. Spearman’s rho is Pearson’s correlation calculated on the ranks of the raw data. Generally you should use Pearson’s correlation.

  21. valen says:

    kindly give me the answer of this..How will i rank the same 4 scores example 1,2,3,4,5,5,5,5,6,7,8

    • Charles says:

      I don’t completely understand your question, but the rankings for the 11 numbers you listed are 1, 2, 3, 4, 6.5, 6.5, 6.5,6.5, 9, 10, 11.

  22. Colin says:

    In example 1, you wrote:”We now calculate both correlation coefficients as follows:

    Pearson’s correlation = CORREL(A4:A13,B4:B13) = -0.036

    Spearman’s rho = CORREL(C4:C13,D4:D13) = -0.115

    The above analysis shows there isn’t much of a correlation between IQ and listening to rap music based on the sample, although the Spearman’s rho is closer to zero (indicating independent samples) than the Pearson’s.”

    Spearman’s rho is closer to zero?

  23. Taufiq says:

    Hi ! I tried using the formula RhoCRIT to find a critical value for my data, but all it shows is #N/A .

    I already installed the resources from the site following the step-by-step guide, so it should’ve worked.

    My sample size is 275, alpha 0.05, 2 tailed. Can you use the formula to calculate it ? Thanks !

  24. Walid says:

    Thank you so much. Life has become much easier with real-statistics.
    Walid (MSc, PhD)

Leave a Reply

Your email address will not be published. Required fields are marked *