Poor brain-fried River is a serious liability for most of the series, although there's a creepy savant-ish ability with a pistol in one episode, and intimidating cunning in another. In the movie, coming to terms with what caused her mental problems lets us see what she's really capable of. It's awesome in the classical sense of the word.
And when he really needs to, Shepherd Book can apply some serious boot to ass, even if he won't kill. Case in point in the comic series Better Days, where he chops through a group of killer death robots, samurai style. There's also his bad-ass line in War Stories, when asked if his religion forbids killing: "Quite specific. It is, however, somewhat fuzzier on the subject of kneecaps."
In fact, Wash was one of the softest, gentlest people you could ever meet. Then he got captured and tortured by a crime-lord, but he completely ignored his torture as he carried on a conversation he had started before the capture about the nature of his wife's relationship with their Captain. Best summarized by his growling, "my ass" response to Zoe telling him Niska will prolong Mal's torture for days: "Bastard's not gonna get days." Do NOT piss Wash off.
He later flies the ship to the rescue and rams the station where Mal is being held-without crashing by coming to a stop right into the station's landing bay. The same episode has River shooting three assailants without even looking at them; Kaylee is a little freaked at how easy it was.
How I Met Your Mother: Marshall Eriksen refused to fight and was mocked constantly by Ted and Barney, even though he said he had been in plenty of fights before. But after Ted got punched out by a really big guy, Marshall beat the living pulp out of the guy who punched out Ted, with a single punch. Turns out that the aforementioned (in-universe and on this entry) fights with his brothers (who are taller than him, he's "only 6'4'' (1.93 m)" after all) were serious business.
The main character of Due South, Constable Fraser, spends most of the series as a decent investigator whose fighting skills don't get shown much since, living in Chicago, he's out of his jurisdiction. In one episode, however, he and his partner, Ray, are pinned down in the cargo hold of a Great Lakes freighter, which conveniently crosses over into Canadian waters at the high point of the fight. Ray throws him his gun, Fraser jumps to catch it, and while still flying through the air shoots the detonator the bad guy was holding.
With a smile and a polite "Welcome to the Dominion of Canada." Fraser was incredible.
It should also be noted that Fraser managed to kick a lot of ass even when he wasn't armed. He routinely fought opponents hand to hand, and the few times he was injured, he was either significantly outnumbered or was going up against an unrepentant murderer who killed any cop who got in his way. It was Fraser's mild mannered and polite demeanor that threw people off. Very few expected the kindly Mountie who was polite to a fault to be able to take down opponents twice his size.
There's an episode of Star Trek: Voyager where everyone but Kes, an innocent and upbeat young alien woman, is incapacitated by an evil alien. He tries to attack her too and gets promptly beaten to a pulp. Her race was previously revealed to have untapped but potentially god-like Psychic Powers.
Not to mention the time Kes was possessed by a brutal dictator who, at first, assumed she'd make an easily cowed Body Surfing host. Once he realizes how powerful she really is, he tries to keep her body and her powers. Unfortunately, he also winds up making her really mad, at which point she tears his mind apart from within while he watches. Do NOT ever get this woman angry at you.
Inverted in "Fury": An aged Kes attacks Voyager in a Roaring Rampageof Revenge, and it takes a holo-recording from her young and upbeat self to stop it.
In Season 2 there is an episode where Baltar saves the day on Kobol by being better at strategic combat than a trained officer. That's partly because the trained officer in question has only training and no actual experience, but still, when Baltar can apply himself to a problem, he really is as smart as his reputation would have it.
Romo Lampkin has a moment of this in the 4th season. He stabs a guy during Tom Zarek and Felix Gaeta's attempted coup. With a Pen.
Adama in the miniseries. Sure he's a soldier, but his fighting days are long behind him and he's on the verge of retirement when we meet him. Yet he beats a Cylon to death with a flashlight.
When Cally, one of the deckhands, is threatened by a prisoner who wants to rape and/or kill her, she bites his frakkin' ear off!
In the first season, the planet's secret is revealed, a bomb is about to destroy the entire system and a massive space battle over ownership of the planet is occurring all around, with missiles attacking anything that gets close to the planet. On board the station a small group discover how to put things right, if they can get down to the planet. In walks the comic relief, Londo Mollari, who was a soldier before he was a drunk and if he had to choose how to die it wouldn't be liver failure. They take a shuttle and head out through the chaotic battle outside.
Ivanova:(in C&C) Whoever's piloting that shuttle must be a madman! Londo:(piloting the shuttle) Yes!!! Just like Fralis-12!!!
Morden: So what are you going to do, Mollari? Blow up the island? Londo: Actually, now that you mention it... Morden: NO!!! (boom)
Lennier, the meek assistant of Delenn, can lift people with one arm and can handle himself in a bar brawl. And that's before he joins the Rangers, becoming an ace pilot and even more formidable melee fighter.
Marcus Cole, early into season three. Shown to be a nice guy most of the time, people quickly learn not to make him angry. After Delenn gets captured he takes on an entire bar full of brawlers and assorted criminals to get the information he needs, stopping only after he's knocked everyone out and bemoaning that he now needs to wait for someone to wake up.
Lennier: I see they trained you well back home. Marcus: Well, they said I was carrying around a lot of repressed anger. Lennier: And? Marcus: I'm not repressed anymore!
You knew Al Bundy was going to start kicking ass and taking names on Married... with Children when either he said "Let's rock", or when Bad To the Bone started playing.
Actually all of the Bundys are like this. They all fight and seem to hate each other, but if someone starts attacking one of them they'll all come together and beat the crap out of anyone they meet in universe.
Xander Harris, from Buffy the Vampire Slayer was usually the Plucky Comic Relief (and Joss Whedon's personal Chew Toy). The other characters universally got onto him about how he just wasn't as cut out for fighting demons as they were (Spike called him a glorified bricklayer once) but every once in a while...
(after smashing Glory through a wall with a wrecking ball) Xander: And the glorified bricklayer picks up the spare!
Also, Giles. After his love interest is killed, he takes on Angelus and a bunch of vampires singlehanded and does rather well considering he's a librarian. And of course, blasting Dark Willow into a wall.
Not to mention murdering Ben in order to kill Glorificus, because it had to be done and he knew Buffy wouldn't.
Wesley from Angel diverges from his completely comical presentation in Buffy the Vampire Slayer and becomes a hardened badass. One of the most notable instances of this is when his father (or, rather, someone he believed was his father) double-crossed him. The climax comes when Wesley confronts him on the roof, and during the Breaking Speech proceeds to shoot the man half a dozen times.
iCarly: Gibby in "iPsycho". He rams down the front door of Nora's house and when Nora seems to get the upper hand in their fight, Gibby removes his shirt and resumes the battle.
Mrs. Benson and T-Bo in "iStill Psycho", when they save the gang (plus Spencer) from Nora and her just-as-crazy parents. They burst in with a motorcycle, T-Bone delivers an offscreen Curb-Stomp Battle to Nora's father while Mrs. Benson uses her fencing skills to take down Nora and her mom (who mind you were trying to kill her with fireplace forks).
Abby from NCIS does this at least twice, very notably in "Frame-Up" (3.09) when her assistant Charlesreveals himself asthe guy who set Tony up. So, we have Perky GothLab Rat Abby, unarmed, with no (apparent) combat skills, versus a knife-waving guy who seems intent to kill her. Gibbs and the others race into the lab to find the place completely torn up, and Abby sitting with her back to them in a chair.
Gibbs: Abby? Abby:(turning and pointing to Charles, who is tied up on the floor with his mouth taped shut and attempting to shout through the tape)Now can I Work Alone?
There's a later episode where Abby is abducted by the guy who has been stalking her all episode. Gibbs chases him down, finds a locked van with high-pitched screaming from the inside... and pulls open the door to find the screaming coming from the stalker, as Abby has him trussed up and is shocking the hell out of him with a taser.
Spencer Reid on Criminal Minds. He's the smart guy; basically a walking encyclopedia. He has been described in-universe as pipe cleaner with eyes. He even fails his gun qualification at the beginning of one episode. Basically, when it comes time for the action, Reid steps back and lets the big guys do their thing. Then he starts being forced into the dangerous situations... Sufficient to say, by later seasons, Reid is no longer being left behind when the team goes off to confront the unsub. (Except when he's recovering from the gunshot to the leg, but that's another story.)
James May on Top Gear is frequently the butt of jokes from co-presenters Clarkson and Hammond about how he can't drive fast. However, his taste runs to well-made cars that handle well, and on winding backroads he frequently leaves the other two behind.
On one episode, James May was being coached for a driving challenge by no less an authority than legendary racer Jackie Stewart. Stewart told May that he had the greatest potential as a driver of the three hosts.
James May disproved the whole "can't drive fast" bit when he took the Bugatti Veyron to the track. And pushed it to a jaw-dropping, record-breaking, Crowning Moment Of Awesome 407.5 km/h (253.2 mph)
Jeremy Clarkson, after seeing the rear end of James May's car: "Is THAT Captain Slow?"
In Lois and Clark this was Clark's entire M.O. To wit, when Superman is threatened that his Daily Planet colleagues will be killed if he is seen in the East Side, Clark goes undercover as a cop to take on Intergang.
In Get Smart, Maxwell Smart is usually The Fool, but when it's do or die, nothing can stop Smart from saving the day.
In season two of Supernatural, all we knew about Bobby was that he was a gruff old junk dealer who knew a lot of arcane lore and had helped Dean fix the Impala. Probably too old to hold his own in a fight, especially against a huge guy like Sam, aka "Sasquatch." So when Sam turned incredibly Evil (and incredibly hot in the process), and paid Bobby a "friendly visit," the audience did a collective uh-oh and hoped Dean would get there in time to save the day. Turned out, Bobby casually slipped some holy water in the beer and easily knocked out Sam while he was choking on the floor. Bobby is made of Bad Ass.
The first two times we see Castiel in a fight, he gets the crap kicked out of him. Then there's the first episode of Season 5, where he shows up after being thought dead, casually dispatches two other angels, and intimidates his former superior into running like a child pretty much by telling him to. From then on he kicks ass consistently.
Both heroes and villains alike throughout the franchise have their own version of this. For villains, whenever the Rangers gain the upper hand on the Monster of the Week, this Trope might as well be synonymous with another, as the Big Bad enlarges it to giant size. That's when the heroes use their own version of this Trope and break out the heavy artillery in the form of the Megazord, and the battle begins again on a far greater scale.
For individual cases, it happens numerous times throughout 'the early series, most often on the part of Bulk and Skull. For example, in season 2, after the Rangers lost their memories due to the Monster of the Week, Bulk and Skull stand up to the monster. They fail miserably, but it was their first really notable act of heroism. Then theres two at the end of In Space. The first one involves Alpha 6 and DECA kicking Ecliptor's ass in "The Impenetrable Web". Secondly is, of course, the widely accepted Crowning Moment Of Awesome of the entire series: the I Am Spartacus scene at the end of "Countdown To Destruction Part 2", when Astronema demands that the Power Rangers show themselves, or the planet will be destroyed. When they don't appear, Bulk and Skull claim that THEY'RE the Power Rangers and soon, so does the rest of the citizens of Angel Grove. Frustrated, Astronema demands that everyone is destroyed. Bulk and Skull then proceed to lead the citizens into a fight with the endless Mooks, where they shockingly hold their own fairly well for powerless humans.
The next season, Ninja Storm, has Cam, ostensibly a "mere" Techno Wizard, serving as support for the rangers from their headquarters. It makes it easy to forget that he's the son of Sensei Kanoi, the season's Zordon; when he's shown fighting, his martial arts skills are at least on par with those of the other five rangers. But he only gets to show them off once or twice...before he becomes the Sixth Ranger, that is.
Rodney McKay of Stargate Atlantis is not what most people would call an effective fighter. But as soon as he gets some enzyme in his system...
Rodney:And that's what happens when you back a brilliant scientist into a corner!
Throughout Doctor Who, the Doctor's companions (most especially Rory Williams) do this frequently. Amy Pond also has her moments, especially in "The Girl Who Waited" and "The Wedding Of River Song".
The Doctor has that effect:
Rory Williams: You have no idea how dangerous you make people to themselves when you're around!
Howlin' Mad Murdock of The A-Team did this all the time. He'd be wonderfully zany and hilarious, and then near the climax he'd pull out some sort of Badass stunt, usually with a helicopter.
Captain Mainwaring and the Home Guard platoon of Dad's Army were usually portrayed as incompetent bunglers, but even though they never really saw proper military action they nevertheless got a chance every so often to show that for all the clowning they had chops and were willing to use them to defend their homes and country.
Nazi pilot: If you don't put that gun down, I will shoot you!
Cpt. George Mainwaring: And if you don't put that gun down, I will shoot you!
Nazi pilot: I give you one last warning!
Cpt. George Mainwaring: And I give you one last warning!
Nazi pilot: One!
Cpt. George Mainwaring: One!
Nazi pilot: Two!
Cpt. George Mainwaring: Two!
Nazi pilot: Three!
Cpt. George Mainwaring: Three! If you shoot me, there are seven men to take my place!
And that's when you remember that several of the platoon are WWI veterans who know exactly what fighting in a war means.
Pierce in Community episode English as a Second Language. When the study group's grades are on the line he becomes surprisingly competent. He can really seduce someone if he really wants to. Like the replacement Spanish teacher.
The paintball episodes contain Let's Get Dangerous moments for most of its principle cast, none of whom had previously displayed any level of badassness.
In episode 5.05 of Primeval, when all of the ARC's troops are out dealing with anomalies, an anomaly appears in the car park. Lester, the government official who runs the ARC, goes after a dinosaur solo (albeit armed).
It should be pointed out that said dinosaur had just pressed Lester's previously-undiscovered Berserk Button - it had threatened his Jaguar.
Lester: OI! Have you ANY idea how much that thing cost?
Also towards the end of season 2, resident Ditz Jenny Lewis showed off some surprising efficiency with a rifle.
Jenny: Most of my friends loved the pony club, I preferred the clay pigeons"
The occasionally idiotic and childish MC Bat Commander in The Aquabats! Super Show! delivers on his attempt to save the world in "Showtime!" — after witnessing the death of SuperMagic PowerMan and Lanolin Lady, as well as Space Monster M blowing up a nearby city with his mech body, the Bat Commander becomes overly determined and aggressively competent and successfully coordinates the entire team to take Space Monster M down.
Happened often in the early seasons of Merlin. Merlin's default state, even to those who knew he had magic, was a clumsy, silly moron who was just as likely to screw things up as make them right. But threaten his friends, and you won't last five seconds. Of course, as he grew more into his role as Arthur's future advisor, this became normal and the moron was the rare thing to see.
Big Love's Bumbling Dad Bill has just learned that one of his mortal enemies, Alby, has been stalking his daughter Sarah. The next shot is of Bill, strolling down a walkway towards the door of a motel room. The only hint of the ass-kicking to come is that he's seen removing his watch. He knocks on the door, pushes his way in when Alby answers, and all we hear are the sounds of blows landing and Bill screaming, "Don't you EVER go near my daughter again!"