Perform A Spearman's Rank Correlation Test In Excel

In this tutorial, I will show you how to perform a Spearman's rank correlation test in Microsoft Excel. Firstly I will show you how to calculate the Spearman's rank correlation coefficient. Then I will show you how to calculate a p value from this coefficient value.
THE ONLINE GUIDE
toptipbio.com/...
PEARSON CORRELATION IN EXCEL
• How To Perform A Pears...
HOW I CREATED THIS TUTORIAL (AFFILIATE LINKS)
Laptop amzn.to/38bB7JG
Microphone amzn.to/2OFn1sd
Screen recorder & editor techsmith.z6rj...
UA-cam SEO www.tubebuddy....
Software (Microsoft Excel 365 ProPlus)
FOLLOW US
Website toptipbio.com/
Facebook / toptipbio
Twitter / toptipbio
AFFILIATE DISCLAIMER
Top Tip Bio is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to www.amazon.com. We will earn a commission from Amazon if a purchase is made through the affiliate links.

[@StevenBradburn]

THE ONLINE GUIDE
toptipbio.com/spearman-correlation-excel/

[@CH-dm1po]

This is incredibly helpful. Everything is explained really clearly so I actually understand the reason behind each step. Thank you!

[@StevenBradburn]

Excellent! So glad it was helpful for you :)

[@noemiszebenszki7508]

I am doing a university project and this video saved me, thank you! It was very useful and easy to understand!

[@StevenBradburn]

Excellent. Thanks for the feedback. Good luck with your project

[@judybrowes2102]

thank you so much this really helped me with my last Psych statistics assignment and you explained it very well.

[@glitterwitch19]

Never left a comment before, but thank you very much. Our teacher was trying to make us to do this manually in Excell

[@bernsbuenaobra473]

Great! Its important to remember that one does this when data is ordinal

[@raphaelsimbulan57]

Wow thank you for this! Helps a lot in my online assessments

[@minivashist]

Can't thank you enough for this amazing video....made it so easy to use the test..thank you 🤗

[@cassieklein6772]

This is SOOOOO helpful! Thank you so much for posting.

[@StevenBradburn]

Very welcome Cassie

[@scottdiver690]

Thank you for this, very helpful. Still not clear on 1 or 2 tailed test but that seems to be a tough one.

[@somnolent4339]

Thank you for explaining how to interpret the results, not just do them. this helped so much.

[@guhgondor6468]

Great tutorial! This tutorial helped me alot. Thank you!

[@bernsbuenaobra473]

Ok this is another great way of remembering this procedure when the data is ordinal

[@ogunwusijohn1909]

This is a great video with excellent explanation. Please I would like to know why you performed a two tailed test on the dataset. Thank you

[@StevenBradburn]

Thanks for the feedback Ogunwusi. So I performed a two-tailed analysis because I did not pre-specify a certain direction in my hypothesis. If my null hypothesis was 'There is no POSITIVE association between variable X and variable Y', then I would perform a one-tailed analysis because I am stating a positive association (as apposed to any [positive or negative] association). I'll create a tutorial on the differences between one-and two-tailed analyses in the future. Hope that helps. Steven

[@nastya-ananastya1832]

Thank you a lot, it was super useful! your way to explain is awesome

[@StevenBradburn]

Thanks :)

[@anndiamond7601]

Thank you so much. You are heaven sent!

[@S.12123]

Very well explained, thank you

[@pavelrogozin108]

Thank you, very helpful

[@eueueqi]

Can i use this for likert scale values ?

[@ohbennyboy]

Really helpful! Thank you!

[@kendesmarais9018]

You can lock column and row together by simply clicking F4.

[@mifeijoo]

don´t you need to put the ranking in order before?

[@RaoShravani]

Omg Thank You so so much. It literally helped me pass.😭🌻🌞❤

[@StevenBradburn]

Very welcome Shravani!

[@angelostaana8749]

hi, may i ask how to rank three same data? :((

[@mustafadar6266]

how is the degrees of freedom formula not: (n-1)

[@boblee1401]

hi, may I know if there is a paper that I can cite for the formula for t-statistic?

[@jiaqint961]

Fantastic video!

[@phoebekatesmeleciodelicana2252]

Thanks for saving me!🙏💕

[@JRZans]

Does it have to be a 2 tailed test every time? Or choosing 1 or 2 tailed test does not really matter that much?

[@hazelarcega3337]

THANK YOU SO MUCH!

[@inaoville3115]

Can this be used for two likert scale data?

[@alexm8859]

Is there a way to do this faster, I have a lot more values

[@zaynabwaraich1738]

What does the t-statistic and p-value mean?

[@elliejoensen2695]

saved my life!!

[@samdavison-wall4972]

Very good!

[@smsmsmsmsmsm]

Thank you so much

[@carolineguerra7655]

Thank you sir

[@elliot1453]

very helpful!

[@reyarcite4286]

Thank You

[@JustDeidei]

You saved me

[@sebastianszereday6073]

I've got a problem, my rs is a perfect 1 or a perfect -1, and my number of sampling units is 3. That results in a division by 0 when calculating the t-statistic. Can I do anything to get a number for my t-stats?

[@StevenBradburn]

Hi Sebastian. I would not advise to perform correlation analysis with only 3 samples

[@sebastianszereday6073]

@@StevenBradburn I did use sampling units with sample sizes of greater than 100 observations. These sampling units are transects of surveys conducted at individual sites, so I did 3 transects per site and the I am looking at relationship between adult and juvenile trees across these transects. If I get a perfect score, of 1 or -1, then I get a division by 0. Is that a bad idea?

[@nickluo75]

thanks man

[@LoffysDomain]

So the CORREL function in Excel is really only a Pearson correlation coefficient, i.e. not possible to apply on two variables with raw ordinal data?

[@1997alimaster]

I also believe that correl does not provide a Spearman rho. One should use the Spearman formula of rho = 1- (6*sum d^2)/(n^3-n). Correl gives a different outcome since it calculates Pearson.

[@elliot1453]

@@1997alimaster yes but since the data is ranked its measuring the correlation between the ranks, not the data itself. For my set of data I get the same answer doing it with the formula and on excel.

[@Kanasinani]

THANK YOU.

[@Kanasinani]

Especially for the explanation.

[@sayyedataki5499]

my coefficiant is -1 and bcus of this i cant find my t statistic why?

[@naila0899]

you should take abs value

[@wnaish7541]

it just says #value! in the cell?

[@charanpreetkaur3148]

My p value is coming 3.4584E-19. How can I interpret it

[@samsg]

it is very less than .05, so there is significant (positive or negative depends on the sign of your *r* value) correlation between the two arrays.

[@veehangisingh5159]

HI! WHY DOES IT SHOW WRONG FORMULA FOR ME?

[@alexandrufilipescu1301]

Hi, have you solved it?

[@bulbulroy8812]

My is showing #name?

[@winter_armamenter]

I use to suffer from statistics

[@drealslimshady]

i love u

[@RayanKhan]

My T statistic is showing #num!

[@StevenBradburn]

Is your correlation coefficient value a negative value? If so, be sure to include the ABS function in the T statistic formula as shown in the video 05:18

[@eueueqi]

@@StevenBradburn hi why is it even if I have included the ABS function, it still shows #num! ?

[@zeldaweeb1738]

@@StevenBradburn I also put in the ABS and still got #num!. I even decided to try to multiply my coefficient by -1 to make it positive and it still didn’t work.