No shit sherlock is used to insult someone who is stating to blatant obvious. similar to "you
don't say" or "thanks captain obvious!"
if someone is saying
something obvious instead of saying "no fucking shit" you would
add "sherlock" to it just to insult them further.