I talked a bit before about what inspires cool gaming moments . The hardly subtle (the b is silent) tone of that post was about GMs molding their games to the players to make cool things happen. This is one of many ways to get players to talk about a game’s moments after the fact, but it is far from the only one. Here are a few more ways that I can think of.
When the players overcome something that is not viable in the real world it is incredibly cool. When the players overcome a challenge that Indiana Jones or Neo would have issues dealing with it is just plain incredible. Insurmountable traps, awe inspiring village destroying enemies, life or death of a nation negotiations, etc. These types of impossible things happen all the time in movies and movies are a good place to style impossible odds off of. Though they seem impossible the heroes always have a way to defeat it. Conveying the feel of an impossible odd while convincing the players they have a chance at beating it is what will hook them and make them feel great when they do.
Personal Impact on the Characters
Is fighting a random encounter of 4 bugbears as interesting as fighting bugbears who had raided the PC’s home village? Not usually. If a character has a personal investment in a challenge they are facing, then the player is usually more engaged in the action. Tailoring encounters to these kinds of flags can turn a so-so encounter into one that makes the players feel more rewarded. Throw a ranger against his favored enemy. Link an encounter to a character before leading the group into it. Watch for the players cues or flat out ask them what kind of things they would like to take on.
Noted and Notable Enemies
The Big Bad Evil Guy. Players know they are going to take him/her/it on at some point. Who and what the BBEG is can be important. Making the BBEG someone big in the world setting can make players salivate to take it out. Making the BBEG a magnificent bastard  (think Gaius Baltar from Battlestar Galactica) can have a huge impact. If the villain is full of intrigue and unique details, seems to be one step ahead of the party, and never seems to let defeat get it down, then it is going to be glorious when the characters get to hand it the final defeat.
Rewards of Renown
Some people do it for the money, some people do it for the fame. Making sure your players know that they are going to be big people in the world if they beat a challenge might be all the motivation they need. The key factor of a reward of renown is not that it is given to the player, but that they feel the effects of it. A king granting a title might affect a player less than a village staring at the characters in awe asking: “Are you really going to kill the dragon . . .? OH GREAT SAVIOR!”. Another effective technique is to have the characters overhear some tidbit about one of their previous adventures. “Truly this Dashite fellow must be 7 feet tall and shoot fire from his eyes to have defeated Sardaukar.” This gives the players a sense that their actions impact the world and have meaning. They will be even more stoked to take on the next thing with its impossible odds.
The WTF? factor
One thing that I’ve noticed is that players have great fun when engaged in moments of WTF (What the Fudge). If the situation is so absurd that it boggles the mind then it makes an impact. Some horrible beast that the characters are fighting might start preaching pacifism. The players might bust through the last door of the dungeon only to find the BBEG having dinner with his family and venting about the rigors of running an evil empire. Then there is always the Calzone Golem .
Now what advice do you have for making challenges that the players love to beat? A conversation is multiplied in worth when it is shared.