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 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.
Short-hand for the word floccinaucinihilipilification, meaning the estimation of something as valueless (encountered mainly as an example of one of the longest words in the English language).