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.