It is frequently used in headlines or commentary to mock "revelations" that were already common knowledge, such as a study finding that hearing loss might increase the risk of depression or that fatigue might be linked to poor athletic performance.
The phrase is a vulgar, sarcastic retort used to mock someone who has just stated something painfully obvious. It functions as a "linguistic tool for pointing out when someone has achieved the remarkable feat of stating something painfully self-evident". Etymology and Origin No Shit Sherlock
A marketing and branding concept used to identify platitudes . If a company claims something inherent to its service (e.g., a law firm saying "we offer professional service"), it fails this test because the listener's natural reaction is the sarcastic retort.