It's used when:
* Your words unintentionally sound like a pun: You might say something like, "I'm feeling under the weather," and then add "no pun intended" because "under the weather" can also mean being sick.
* You want to avoid the awkwardness of a missed pun: If you make a joke that falls flat or is misinterpreted, you might say "no pun intended" to try and salvage the situation.
Essentially, "no pun intended" is a way to acknowledge that your words might sound like a pun, but you're not trying to be funny.