Used as a sarcastic comment for someone who stated the obvious, using a reference to the famous fiction detective Sherlock Holmes, it can be understood as "No way, you are Sherlock Holmes!" which can actually mean "Of course, you dumbass!".

when you're hungover as shit after a spending a night drunk as a skunk

Similar to 'thank you captain obvious'.
Something you say to a dumbass who points out a completely obvious thing!

Asshole version of 'indeed'.

You can reply with this when someone says something extremely obvious.

When you reply to spmebody who has just stated the very obvious ''no shit,sherlock' to imply that yep, everyone knows the answer already and there pronouncement has come as no surprise.

sherlock=sherlock holmes, famed detective of yore

A way of saying " No Duh " or " Obviously " but in a mean kind of way or sarcastically