In World of Warcraft, prior to the release of the Wrath of The Lich King expansion, a Hunter using the Survival talent tree was an example of this trope. Survival was the least played class/spec combination in the game, at somewhere between 3-7% of the overall playerbase, and was generally ridiculed. During an Expose Weakness, or ten second Deterrence proc, however, things changed. The spec went from a typical DPS output of 400 outside an instance, (barely enough to kill non-elite mobs during questing) during "crouching moron," mode, to 1200 when appropriately raid buffed and proccing EW. The dodge/parry chance during a ten second Deterrence activation also doubled, sometimes reaching a potential maximum of 110%; which meant that Surv could theoretically (and often did, practically) finish tanking 75%+ dead raid bosses after the main Warrior or Paladin tank died.
In the first game, Laharl issues a challenge that whoever wins a fight against him and his allies will be the next Overlord. Things look bad when the mid double-digits party faces off against an insanely over-leveled opponent. then the comic relief characters that hang around Laharl's castle jump in and defeat him in a few seconds flat. It's generally then that you realize that the levels they have actually mean something.
In Disgaea 2, Rozalin shows off a particularly fearsome variant of this. Having spent her entire, pampered life in a palace, surrounded by servants waiting on her every whim, she's a complete novice when it comes to fighting (thus giving the hero, Adell, an excellent opportunity to explain the combat-system to her...) It's up to the player how much he wants to level her, but she'll probably still only be around level 50 when you first encounter, and are soundly defeated by, a level 1200 Overlord. At which point a seal is broken, and she suddenly jumps to level 1700, learns a world-destroying special move, and kicks seven different shades of ass. Afterwards, when she snaps out of it, she's got no idea what happened.
The King of Fighters has a game play example in Hotaru Futuba. Sure she looks harmless and is usually so personality wise, but in the hands of a competent player, she's an utter monster.
Arado Balanga of Super Robot Wars Original Generation is exactly like this. He's normally a reckless idiot who gets shot down more than not, but if his partner Seolla (who he was trained to work as a perfect partner to) is threatened with death, he'll be there to take it and go down in flames and utterly kick ass.
Though it is worth mentioning that Arado isn't necessarily a bad pilot by normal means. His tendency to get shot down most of the time is because early on he was stuck using long ranged machines when he had been trained exclusively in close ranged combat. Once he gets his eventual upgrade to a mech that better suits his skills, the other characters note that his piloting performance goes way up, even without Seolla around.
Arado still makes stupid mistakes on a frequent enough basis, though, such as overdoing his flashier attacks and running out of fuel or energy. Luckily, it seems to happen mostly when the enemy's already been blasted out of the sky.
Demyx from Kingdom Hearts II is the villainous embodiment of this trope. His clumsy, cowardly, scatterbrained personality was stressed so much that even Jiminy's Journal called him "a poor fighter". He isn't.358/2 Days shows that he's not exactly a coward, he's just REALLY lazy (or rather Dismotivated). He prefers to bribe other people to do his missions for him, and his reaction to nearly half the Organization dies at Castle Oblivion is "remind me to underachieve twice as hard from now on".
And turned right back around for comedic effect in multiplayer, where Johnny's lack of nanomachines robs him of SOP-linking and the benefits it provides; if you try to link while standing still, he does an anime pose, and if you try while running, he does a Rainbow Six-esque hand signal. He also can't do CQC and will flail hilariously if you try. His diarrhea, however, turns him into a walking gas grenade as it will incapacitate anyone he runs by.
What's more is that the attack (like her personality) seems pretty innocent. How you say? It's a beautiful field of flowers that appears on screen, that's it.
In Pokémon Mystery Dungeon: Explorers of Time/Darkness, Wigglytuff seems to be nothing more than a simple, Perfect Apple-loving, uber-happy goof of a Pokémon. But if he gets upset, you'll find out exactly why Chatot seems to fear his temper.
The additional side stories for Wigglytuff (as a child Igglybuff) and for Team Charm from Explorers of Sky further explains this. Wigglytuff has a tremendous talent for solving puzzles and getting through traps, almost accidentally, hidden beneath the overtly cheerful demeanor. No wonder he's a Guildmaster despite seeming totally clueless.
King Trode from Dragon Quest VIII would be a great example. He spends most of the game just sitting in the wagon and generally complaining about various things like his curse or how you haven't found Dhoulmagus yet. But in the ending? He can be seen beating up several Argonian guards. His weapon of choice? A twig. You heard me. He beats up men twice his size, who are reportedly tough enough to kill Argon lizards, and he does it with a 2-inch piece of tree wood.
In Pokémon, at first Whitney seems like a generic teenage girl, with some Genki Girl mixed in. However she's renowned by the fanbase as being one of the hardest gym leaders in the games due to her Miltank.
A very popular fan-theory and urban legend for Pokémon Black and White was that Dumb Blonde Bianca would turn out to be the true Pokemon Champion of Unova. While that turned out to be a misunderstanding of the game data for battling her post-storyline, there's still the possibility that, depending on your choice of starter, Bianca can end up with a more robust and better-balanced team of Pokemon than Cheren.
Grit from Advance Wars is a top-notch distance weapons specialist (And considered an overpowered CO in earlier games by the players), but he hardly ever shows it due to his incredibly laid-back, agreeable demeanor.
Touhou is positively brimming with examples of this trope. Shall we start with YukariYakumo herself, wielder of powers far beyond godly who nonetheless prefers to spend her time goofing around, bothering Reimu, and generally acting like a little trickster imp? Or YuyukoSaigyouji, who is genuinely amiable and nice and enjoys a good meal and sake under the cherry blossoms far more than anything else in the world, but who has been heavily implied by the author to know a lot more than she lets on, being able to keep up with aforementioned Yukari? (Not to mention her power is to cause the death in anything that can die.) Maybe Komachi Onozuka, who is a God of Death and the Ferryman of the Dead — and also a complete slacker who runs low on her quota of souls ferried because she prefers to lay down near the Styx and catch a shuteye or twenty? Or Suika Ibuki, a little girl who looks like a perpetually-tanked seven year old with horns and an unquenchable thirst for parties — and can also bench-press a mountain? All in all, when you're in Gensokyo it certainly pays to not underestimate whatever little girls you may find.
Conversely, Rumia, the youkai of Darkness. She consistently walks into trees (the powers of Darkness rendering her blind), is nearly harmless, and is sought out by people hoping to evade the sun on hot summer days.
Also, Flandre Scarlet, an eternal (literally) Cheerful Child who loves to play with the humans who visit her home, and has absolutely no control over her ridonkulous levels of magical power. Even Marisa, who has faced all of those mentioned above and then some, makes it a point to avoid ever meeting Flandre whenever she goes to the Scarlet Mansion to steal books, not-so-subtly implying that she scares the crap out of her.
Also deserving of her own bullet-point is Cirno after starring in her own Gaiden Game, Great Fairy Wars. For all that she's rightfully regarded as the seriesdumbass, she shows the near-unique ability to destroy enemy projectiles in flight without using a spellcardnote Only above-mentioned Youmu has shown a similar skill, in a much more restricted way. Also, unlike every other PC, who only get knocked around when they lose a life until they take enough minor injuries/Clothing Damage to have lost a fight, Cirno out and out dies every time she gets hit, letting her fairy-issue Resurrective Immortality take over before flying back onto the field, unfazed.
The Black Baron of MadWorld. An Uncle Tomfoolery who is constantly killed by his own deathtraps and girlfriend, it's hard to believe he poses any real threat, even when he turns out to be the final boss. Most players saw an easy fight...and then he goes Super Saiyan, kicks at you with lightning and punches you with a black hole. Sadly, when he meets his final end, he's reverted back to his moron stage. This is much more evident in Anarchy Reigns, where he's a playable character and a boxer armed with flaming Power Fists.
In one instance of The Lord of the Rings Online, Radagast the Brown walks through a wight-infested swamp talking to little animals while the players have to fight all around him. In the end, he incapacitates who seems to be the endboss with a single hit, saving the players.
In this case, Emil seems to take a leaflet out of Neville's book here; over time, Emil loses the coward part of himself, even pulling a few moments of role reversal on Marta when the latter isn't up to her best (the pair lampshade this in an aptly named skit named "Role Reversal"). In fact, in the very last chapter of the game, he out and out seals away the Ratatosk side of his personality, and you get to play as Emil himself in battles from that point onward.
Even more so in "Super Mario Adventures" where, after being kidnapped by Bowser, she easily breaks herself out, finds out Mario has been captured trying to rescue her and proceeds to break back in and rescue Mario.
Toad fits into this as well.
One could argue whether Mario himself, a somewhat clumsy pasta-obsessed plumber fits into this category as well. Especially evident in the DiC cartoons.
Wario seems to be nothing but a fat, greedy slob with clumsy tricks that almost never work out. Then when his money is stolen, he goes piledriving dinosaurs and beating the Artifact of Doom so hard, it cries before exploding.
UDON played with this in the Street Fighter Legends, where Dan briefly managed to tap into the Dark Hadou, only to trip and fall on his face before he could attack.
Travis Touchdown, the main character of the No More Heroes series, is a very skilled swordsman, Made of Iron, can move at superhuman speeds and transform into a tiger, owns a Humongous Mecha the size of a large building and could probably cut an average human to pieces in the blink of an eye...if he's not too busy pleasuring himself to Moe Anime, playing video games, or clumsily hitting on his female opponents..
The Vortigaunts in the Half-Life series are an entire species of this trope. In Half-Life and its expansions they're a relatively weak mid-level enemy slaughtered by the dozens and used as manual labourers by the more powerful members of the Nihilanth's army. By Half-Life 2 they are freed from their enforced servitude and join Earth'sLa Résistance against the Combine, providing Gordon with cryptic statements and the occasional charge for his HEV suit. At the beginning of Episode One however they render the nigh-unstoppable GMan completely powerless, saving Gordon and Alyx in the process, and when three of them are faced with an army of Antlions in Episode Two they proceed to slaughter every last one, then use what is essentially Antlion feces to raise Alyx from the dead. Given that they were at the time also hunting and presumably killing the horrifically powerful Combine Advisors, hopefully they will return for the expected battle/s with them in Episode Three.
Maeda Keiji from Sengoku Basara is usually a laidback, jovial guy who enjoys drinking, pulling pranks, chasing girls and generally goofing around. However if you dare to hurt his friends, family or any innocent person when he's around, you'd better get the fuck out before he either punches you in the face or cuts you neatly in half with his BFS.
Dick Gumshoe in Ace Attorney. He barely passes as a detective (and then he got Flanderised) and despite his woobiesh qualities, he deserves most of the punishment he gets for his screw ups. But when someone he cares about is in danger, like Edgeworth, Maggey or Maya, he gains a drastic burst in competence. In 3-3, he's a surprisingly accurate source of information for Phoenix, gives the more important pieces of evidence to him and stands up to Tigre without even blinking, saving Wright and Maya from a possibly gruesome conclusion. All for his crush, Maggey.
Gumshoe has fought of members of both a Yakuza crime boss and a group of Mafia thugs, showing up the next day completely uninjured.
In the third case Investigations he repeatedly breaks away from Lang's men in order to give Edgeworth an important piece of information and in the fourth he actually pulls out a Big Damn Heroes moment. Though to be fair everyone was doing it by that point.
He was also part of an investigation where armed SWAT teams tracked an assassin, lead them to his hideout and steal vital evidence from the hideout while there was a firefight going on, then driving top speed to the courtroom through the busy LA streets, only stopping when he crashed, and even then, he was conscious and determined to keep going for a few seconds. He later turned up with little more than a bandaged head. Why did he do all this? His friend was in danger.
While you don't see it onscreen, the wimpy and easily-ignored Ron DeLite is also the Gentleman Thief Mask DeMasque.
Heck, Phoenix Wright himself. He does double duty as the world's Straight Man and Butt Monkey; is often behest to the whims of his immature sidekicks; and in the first two years of his career defeated three of the best prosecutors out there—including one who had not lost a single case in forty years of practicing law.
And the head of the State Police force, and a man who can blackmail Presidents.
Missile in Ghost Trick. It's just an adorable Pomeranian who doesn't understand certain human concepts and barks excitedly at everything. At the end, it is revealed that Ray, the desk lamp who guided Sissel at the start of the game was actually the time-travelling Missile. It failed its first try at saving Lynne and Kamila on its own, and thus chose to go back 10 years and wait the long span of time (especially long in dog years) for Sissel to appear so it can convince, or rather mislead Sissel into saving the two girls.
Hordes of hundreds of dead darkspawn did nothing more than get some blood on him. In the sequel, he freezes an Ogre solid and wipes out dozens of demons, up to and including a Pride Demon.
The Khajiit race in The Elder Scrolls series come across as quite thick. But just ask the wood-elves how their border-disputes with the Khajiit are going, the phrases 'vertical ambush' and 'terrifying humanoid lions' will probably come up.
Even better are the Argonians. While their females are known for being mages, they're generally considered a lesser race, uncivilized, poor, and beastly in nature. And yet, the black marsh was the last area conquered by the Empire. Better still, during the Oblivion crisis, the Argonians invaded Dagon's realm through the portals, forcing the Dremora to close them for fear of being overrun. Lizards indeed.
Sonic the Hedgehog: Big the Cat. Yes, that Big the Cat. He was originally voiced by Jon St. John, is now voiced by Kamina. He Took a Level in Badass in Sonic Chronicles: The Dark Brotherhood, where he started acting like a smart-ass. On top of this, he can lift cars over his head and uses his fishing rod as weapon with which to demolish giant robots and flying battleships and is capable of turning it into a mace with a giant spiked ball, usually taking out any enemy on screen. On top of this, he can catch bulltes in mid-air, something that not even Sonic can do. Oh and as mellow as he is, don't threaten one of his friends, especially Froggy or Cream.
Sonic himself can be this sometimes. Extremely laid back, constantly cracking jokes and running headlong into danger without a thought, he will regularly destroy entire armies of robots by simply jumping and spinning. Also, God help any bad guy if he gets together the seven Chaos Emeralds or, even worse, the Super Emeralds.
Katze Kotolnos from Endless Frontier is a Camp Gay cat person who is constantly making passes at protagonist Haken Browning. Then there comes a point where you have to fight him and he proceeds to kick your ass with his hands in his pockets. When you first fight him, he fights alongside Ezel, who you've been chasing for a while, and who wears an intimidating skull helmet and looks like a total Bad Ass. Katze is the more dangerous of the two.
Hewie of the H-Game Castle Fantasia 2 Renewal is this to degree. He acts a carefree moron who always sent one of his team member angry over and over again, but turns out he's a very great war strategist.
Piu-Piu of the Chaos Rings series for IOS devices. He's a short, weird looking humanoid who runs his mouth about random stuff, owns a shop to sell weapons, armor and other items is a greedy bastard, a cheapskate, and such a God-damn perve that when they tried to create the first Agent Prototype off of his given up perversion, it created a giant, red demon named Libido. He's the least likely person but then you realize in the second playthrough of the game that he actually is the leading scientist behind the Ark Project and that it was him who actually built the Ark spaceship and discovered Time Travelling. Then you make the mistake of challenging him, which makes you enter a bonus boss battle so hard that it would give every other boss in the game a run for their money.
The Aforementioned Boss: "I love you guys!" *shoots a giant Qualia jack-n-the-box at you*
Captain Quark of the Ratchet & Clank series. Despite being both a coward and an idiot, Qwark has managed to surprise Ratchet, Clank, and their enemies with bravery and cunning that lives up to his big-hero bragging. He helped Ratchet and Clank destroy Doctor Nefarious' Biobliterator in Up Your Arsenal, worked as a mole against Emperor Tachyon to steal vital information in Tools of Destruction, aided in defending the Fongoids from invasion and helped them escape a warship after they (and he) are captured, went undercover to steal plans from Doctor Nefarious to help Ratchet find Clank in A Crack in Time, and is one of the playable heroes in All 4 One and Full Frontal Assault.
Cielo in Digital Devil Saga. Usually a calm and cheery ALBINO BRAZILIAN WITH JAMAICAN ACCENT, until a guy from the security eats his comrades in order to get power. Cielo proceeds to make a badass speech. There's also the time that in order to protect Sera and Serph, he destroys two fighter jets by chewing on their pilots and cutting their wings, dying in the explosion.
Karl Blaine in Just Cause 2. Even before Rico meets him, he's established as the unreliable, comedy relief contact on the island nation who you have to rescue from a government sting. Later, Blaine's captured and successfully interrogated as years as a drunk in a tropical paradise have left him soft and out of practice as a spy. Much later, during Rico's assault on the evil president's hideout, Blaine sneaks out of his cell BY HIMSELF, and gets past the ninja guard to suicide bomb the President without any assistance from Rico.This doesn't stop Rico from taunting him for giving into the torture, however.
Roger Wilco will even admit he's far from the sharpest knife in the drawer. Hell, he's a marginally competent janitor who is prone to taking on-shift naps in the closet. When "it" hits fan, though? Tricks a nasty creature twice his size into blowing itself up. Kills an entire ship worth of Sariens to prevent them from nuking his home planet. Stops an evil genius from invading his home planet with a clone army (granted, it was an army of insurance salesmen...), rescues two programmers from slavery and beats their Bad Boss with a Humongous Mecha, destroyed the evil genius in a rematch after the evil genius took over his homeworld, took down the Confederation's shiny flagship with a garbage scow, and stopped another mad scientist from committing Grand Theft Me on a close friend. Do not piss off the Almighty Janitor!
Laguna is this in Final Fantasy VIII and Dissidia: Final Fantasy Duodecim. In the original game he's a goofy, laidback soldier with a terrible sense of direction who becomes the head of a rebellion to overthrow a sorceress and then the leader of the world's largest and most advanced nation. In Dissidia, he's as goofy and directionless as ever, is the only character to have no magic but still stands up to world destroying demi-gods like Sephiroth, Kefka and Exdeath. His boss is Cloud of Darkness, who is literally the personification of darkness itself.
If not even Vaan takes your directions seriously, then you go ahead and snipe Chaos you're probably this.
The Manga adaptation of Ocarina of Time seems to interpret Link this way, in contrast to his very serious and stoic appearance in the game. He's very sweetly boyish and mischievous, at times outright silly and clumsy, up through a large part of the manga - at least until the Bad Future elements of the adult section start to sink in.
Skyrim: Cicero the jester. He's a crazy loon dressed up like a complete idiot. He's also the Keeper of the Night Mother (meaning he was chosen by the Void to be the Night Mother's protector) and a deadly knife fighter. When he snaps after Astrid dismisses the Night Mother one time too many, he nearly kills the entire Dark Brotherhood in his fury. The guy who pursues him — a werewolf — nearly gets gutted for his troubles, while Cicero is apparently unharmed though he plays possum.
The shopkeeper from the Fantasy Quest turns out rather more adept that his demeanor suggests.
Wheatley from Portal 2. The "moron" is intentional as he's "the product of the greatest minds of a generation working together with the express purpose of building the dumbest moron who ever lived." This, however, precludes the fact that he was the person who woke Chell in the first place, willingly performed a series of personal efforts that he had been told would kill him, came up with the plan to sabotage GladOS's turret production and neurotoxin production, managed to outsmart both Chell and GladOS by laying a trap that relied on them assuming he's too stupid to do it properly, and demonstrated an incredible amount of foresight when it came time for the final battle. In the last case, Cutscene Power to the Max is the only reason Chell survives; without that, Wheatley would have succeed where GladOS failed. Of course, he fulfills his programming in that even if he managed to win... he still would have blown himself up. Because he couldn't be bothered to figure out how to keep the facility from blowing up.
In Team Fortress 2, we have the Pyro. A monstrous creature that instills fear in everyone, even to the people on the same team. It destroys everything in its path and leaves no survivors. It seems unstoppable, sadistic, and completely merciless, spreading chaos wherever it goes...but in its mind, it's in a beautiful wonderland, with fairies and giant lollipops and presents scattered across the land. It just wants to spread happiness and joy, blowing bubbles everywhere, while a constant loop of Do You Believe in Magic by The Lovin' Spoonful plays. The Pyro is a badass and isn't even aware of it.
Heavy: "I fear no man...but that...thing...it scares me"
Nanashi in Duel Savior Destiny is a complete ditz and has a tendency to accidentally lose her limbs, but it turns out that even without an Aether Relic she's so strong that she can match up to even Taiga and Lily, the strongest members of the playable cast. Another example is with Dahlia, though we know from the beginning that she's exceedingly competent.
Super Robot Wars UX: Richard takes up a foreigner act and uses a dialect, which consists of a mixture of easy words from languages, to pretend he's not a serious mercenary when he's on cover missions with Saya. Agnes is completely fooled by this and thinks they are comedians (comic story tellers).
PJ Berri in Parappa The Rapper is very shallow and lazy and pays minimal attention to his surroundings, especially compared to the more exuberant Parappa. However, he's a talented DJ who was performing on stage long before Parappa ever got to.
Lammy from Um Jammer Lammy is neurotic, so socially awkward that she can barely hold a conversation and basically useless unless her guitar is involved, at which point she becomes an unstoppable, optimistic rock goddess able to do whatever ridiculous, dangerous things she has to.
Gumpy from Jagged Alliance. He's one of the squad's explosives experts. He's also unfortunately an overweight, unattractive dork who cracks outdated pop-culture references with a speech impediment and nasty allergies, and as you'd expect, he can't fight for shit. However, if you and your Badass mercenaries can keep him alive for long enough, his high Wisdom score and aptitude for learning ensure he will eventually become just as Badass and competent as the rest of the mercs.
Star Control has the entire Spathi race. They are ridiculous, they are hilarious, they are easy to push around. They are also dangerously intelligent, cunning, and militarily competent if pushed into a corner. It would be very unwise to underestimate their abilities when their backs are against the wall.
Starbase Commander: The Spathi are like a cowardly, mobile clam... armed with a Howitzer.