"It's like a crime drama about a detective who can only concentrate when he's around pastry, so every week the crime has to conveniently take place in a bakery, or within walking distance of a pie shop."
In Tokyo Mew Mew, while Mew Lettuce's powers are less useful than those of the other Mews, she is useful on a few occasions. In one filler episode, she, being a bookworm, can resist falling asleep as a Chimera Anima made from a book reads aloud, and can counterattack. In another, when the Mews get bound in silk by a spider-like Chimera Anima, she can still use her castanets, which only require her fingers, and defeats the monster.
Season 2 of Darker than Black has the character April, who in the first season used her ability (essentially to make it rain) primarily as support for her partner who can freeze things, gets a freak chance to use her ability offensively. She happens to fight a Fragile Speedster with Super Speed and none of the Required Secondary Powers, which combined with the freezing temperatures made each raindrop impact with the force of a bullet.
In SD Gundam Force, the Gundivers were only good for underwater missions, and as such were only useful whenever something important fell into the sea. By the final battle of the series they were upgraded with flight capabilities, becoming the Gunchoppers.
Lampshaded, but MASSIVELY subverted with the fishman Jinbe claiming he isn't very much use for battles on land. This trope DID come into play when ultimately his aquatic abilities were essential to escaping an island prison, or even rescuing drowning devil fruit users. Despite his self-depreciating statement, in future land-based battles he turns out to be a Lightning Bruiser and able to go Curb Stomp several strong opponents, (including another Warlord) with a single hit. All in all, he ends up "merely" very badass on land and practically unstoppable in water.
During the Thriller Bark arc we get that the "Ghost Princess Perona" is able to immobilize her oponents and render them useless with her Negative Ghost Attack, she was able to bring down Luffy´s strongest crew members along as him, however, only one person could stand to her powers, the Lovable Coward Usopp.
Even before then, he still showed why he was on the Straw Hat Pirates in Enies Lobby. With Spandam escaping with Nico Robin, Franky thrown off the bridge and Luffy occupied, Usopp was the only one who could stop him by "sniping him from the Tower of Justice at least a mile away with a slingshot".
Submarimon from Digimon Adventure 02 is the of Armadillomon's Armor Digivoltions that can only used in water because it's... a submarine. However, it has flight abilities, but it was used only one time outside of water, during the Grand Finale.
Aquaman, of course, was notorious for such convenient obstacles, both as an Plot Tailored to the Party as part of a team, and alone in his Filmation cartoon (down to Aquaman actually becoming an astronaut because they needed to explore a water planet). Modern authors usually subvert this however, pointing out that he's at least a Badass Normal on dry land, having him use his additional minor powers or a skillful application of his Required Secondary Powers.
This trope is parodied in Shadowpact where, after having his magical trident thrown into the middle of the Pacific Ocean by The Spectre, Blue Devil reappears with it to fight the unleashed Seven Deadly Sins. Someone asks him how he got it back, and there's a one panel flashback of Aquaman retrieving it for him before going off to deal with his own problems. Instead of admitting he needed Aquaman's help, he just says "I'd rather not talk about it" and keeps going.
Specifically, they've taken his super-tough "ability to survive crushing ocean depths" and super-strong "ability to swim really fast" and used them a lot more. His telepathy is also shown to have some limited degree of usefulness with non-marine species Depending on the Writer.
Some writers go to extremes by combining "can summon and control sea creatures" with "every living thing on Earth has evolved from sea life" to giving him power over more or less everything.
Aquaman was able to induce a seizure in the Martian speedster Züm in an early issue of Morrison's JLA by attacking the parts of his Martian brain that had evolved from sea creatures.
In another issue of JLA, as part of the World War III storyline kickoff, Mageddon initiated a riot at a super villain lockdown and the Justice League were sent in to contain it since the warden and all guards had gone cuckoo as well. While the rest of the team was handling things elsewhere in the building, Aquaman all by his lonesome busted into a room filled with about fifty bad guys with light-based abilities. He just stared them down and said:
Aquaman: "Most of your powers are dependent on light. My eyes are adapted to see at six thousand fathoms. Think about it."
Aquaman: That's your weakness, Namor. You're too noble to cheat.
In the JLA/Avengers crossover, Aquaman is able to shut down a whole group of Marvel Atlanteans with his telepathy; he comments that their minds are more complex than what he usually uses it on, and Namor (himself half-Atlantean) says that the telepathy gave him a pounding headache.
Perhaps Aquaman's most impressive showing is in the Earth 2 graphic novel. When evil counterparts of the Justice League from a parallel universe invade Washington DC, Aquaman shows up to stop them — with the entire Atlantean navy backing him up.
On other hand, the original Aqualad knew his limits at times in The New Teen Titans, Robin wanted him to rejoin the team, but he declined saying that his abilities are of little use out of an aquatic environment, but he promised to help out when he could really help.
In the first annual, Cypher, whose superpower was the ability to learn languages really really fast, ends up saving the solar system because he's the only one who can read an alien instruction manual.
With his recent resurrection, Cypher has turned this into Heart Is an Awesome Power, in addition to vindicating numerous fan theories about how his powers would work in the age of modern computers.
Chew involves a detective who gets psychic impressions from whatever he eats. Fortunately, he works for the FDA, and thus encounters a lot of food-related crimes. And since he can eat things besides food, he's quite useful to other agencies too.
The comic X-Men Legacy was a particularly egregious abuser of this trope for a stretch of issues: It seemed like every single crisis could only be solved by two peoples' powers at once, or one person's power with another person's knowledge. Good thing Rogue's power is to absorb the powers, skills, and attributes of anyone she touches. It got to seem like less of a team than a bank of power donors and one person who ever actually does anything, blurring the line between this trope and Plot Tailored to the Party... This Looks Like a Job For Aquaman Tailored to the Party.
As blogger Chris Sims points out (with a reference to Knight Boat, no less), DC's annual Rudolph the Red-Nosed Reindeer comic ran on this trope.
In his first appearance in GI Joe A Real American Hero Marvel, Barbecue the firefighter is disdained by Shipwreck, who doesn't see the point in a combat unit having a fireman attached to it. Shortly afterward, Barbecue justifies his position with the team by ably dealing with several fires caused by a Cobra surprise attack.
The number of villains who thought putting Mister Miracle in some elaborate deathtrap was logical was pretty astonishing. You'd think sooner or later someone would come up with a plan that did not involve confining him in something he was sure to escape from.
Though not Super Power related, the murder case in Legally Blonde seems tailor made just so that Elle could help win it. What comes in handy during the proceedings isn't her law training, or her studying and work at Harvard Law, but the fact that she's sorority sisters with the defendant, and knows fashion.
Parodied in the superhero comedy Mystery Men, in which the "Invisible Boy" can become invisible only when absolutely nobody is looking at him, including himself. After spending most of the movie without finding any use for his powers, he becomes invisible to disable an automated security system which cannot detect him in his invisible state.
Fred Astaire's gambler character in Swing Time runs into an inordinate amount of problems best solved by gambling and/or dancing.
Not superpower-related, but improbably convenient, is the climax to G.I. Joe: The Rise of Cobra. The organization has a huge (like the size of Lake Huron) underground water tank and probably spends at least 80% of its annual budget on submarines. And not the big, slow-moving submarines that real navies use; we're talking about two-seat submarines that have the speed and maneuverability of fighter jets because they're imported from Naboo. And then it just so happens that Cobra's main base of operations is underwater.
Team America: World Police parodied this trope with Gary's acting talents, to the point where Da Chief says that sending his agents off to an aerial dogfight without an actor was like lambs to the slaughter.
At the end, all the kids with "lame" powers such as melting, glowing, turning into a guinea pig, and controlling plants work together to thwart the villain and save their parents.
Controlling plants is a subversion as she simply didn't want to take the test in response to the bias opinion most of the main superheroes have of this trope. Her powers were awesome all along.
Zoolander is a comedy thriller that revolves around male modeling. At one point, the main characters meet up with a mysterious informant (played by David Duchovny) who reveals that throughout history male models have been brainwashed into becoming assassins. Why male models? They are always in peak physical condition, keep getting invited to exclusive events and most importantly have low intelligence and tend to do as they are told. All characteristics of the perfect assassin.
Richard B. Riddick in Pitch Black had escaped a life sentence from a prison where he was told he'd never see the light of day. So it made sense that he had his eyes surgically altered to see in the dark. Fortunately for him, the ship transporting him after he escaped crashed on a planet that happened to plunge into darkness during an extended eclipse. Coincidentally, this was the only time light-phobic monsters swarmed from underground caves to feed. Guess who leads the survivors to safety?
The prequel tie-in videogame Escape from Butcher Bay reveals that Riddick's unique night-vision eyes are actually supernatural in origin, so this might also a literal Deus ex Machina. However, in any case, it's rather convenient that a killer whose modus operandi is to stalk enemies from the shadows should be granted the uncanny ability to see in the dark.
Percy Jackson and the Olympians: Sea of Monsters begins to feel like at points with Percy. Have to escape from cages in the bottom of a ship? Percy can control the sea to rock the boat until the literal deus ex plot device falls within reach. Percy needs to escape his archnemesis and reach the lifeboat his friends have stolen? Call up a wave and start surfing that bad boy (and also use said wave to fling the villain into the ocean when he attempts to follow him). Gotta save yourself and your friends as well as a reluctant ally after you've all been swallowed by a sea monster? Take control over the water inside the monster's stomach until it belches you back to the surface again. There's rarely a problem that gets solved in the film by Percy, up until the final boss battle that doesn't somehow require him to use his mystical water powers.
The Poseidon Adventure: As the survivors try to reach rescue, there's a need to get a rope across a submerged passageway that's too long to swim across. Oh, wait: as a young woman, Belle was a champion swimmer who once crossed the English Channel. On the other hand, that was when she was young, and she's years out of practice and out of shape. She succeeds in getting the rope across, but the effort kills her.
Lampshaded and parodied in the Sidekicks series with Exact Change Kid. Just when they actually need exact change for a bus ride to the villain's lair, it turns out he left his utility belt home (with all his change) and they have to go on foot.
Journey to the West. Xuanzang can sit perfectly still for up to three years and he's very proud of this fact. It only came in handy that one time the group was challenged to a meditation duel on their journey.
Alexey Pekhov's Peresmeshnik (Mockingbird). The protagonist's special ability is to copy any other person's voice, which, by the measures of his world, is a weak and useless superablilty (most of nobility there can do much stronger things). This proves useful as he fights the Final Boss, though.
The Ultra Violets's Cheri has the ability to talk to animals and superhuman math skills. Where would the latter come in handy, you might ask? A poker game.
Live Action TV
Each episode of the 1970s live-action Saturday-morning show Electra Woman And Dyna Girl would begin with the titular heroines being given a newly developed module for their wrist "Electro-comps," giving them a new (and sometimes seemingly useless) power. Later in the episode, the duo would inevitably encounter a villain whose evil plot can only be thwarted by an imaginative use of the new power...which would then never be mentioned again in subsequent episodes.
In Thunderbirds, Alan and Thunderbird 3 are specialized for space rescues; Gordon and Thunderbird 4 for marine rescues. When these situations don't turn up, these craft aren't used. Gordon rarely plays a significant role when the rescue isn't at sea, but Alan (either because or in spite of the fact that space rescues are the rarest type) often goes on other missions (but not as the prime character, though).
On Stargate SG-1, Dr. Elizabeth Weir is assigned as the new commander of the Stargate Program based apparently on her expertise as a diplomat. Everyone (especially her) questions how this qualifies her to run the Stargate Program, which is primarily a military operation. In the Season Eight premier "New Order", she gets to use her skills during a negotiation with the Goa'uld, before leaving to head the Atlantis expedition.
On Psych, Gus's expertise in prescription drugs from his actual job as a pharmaceutical salesman comes up a lot more than you'd expect during his investigations. Gus also uses his "Super Sniffer" to uncover crucial evidence and can open safes.
Charlie possesses musical abilities, but is generally useless to the other survivors. However, during the season 3 finale the only way to turn off the jamming equipment set up by the Others is with a number combination, which happens to be the tune to "Good Vibrations."
Likewise, the otherwise useless Shannon pulls her weight with her ability to speak French, when French transmissions, maps and documents turn up.
Juliet and Bernard were, respectively, a fertility specialist and a dentist. These skills came in handy when somebody at camp needed their appendix removed - that somebody just so happened to be Jack, who would have performed the procedure himself if it was anybody else. Bernard's line of work gave him knowledge of anesthesia, and Juliet's medical background, although not specific to surgery, gave her the necessary skills to perform the procedure without incident.
Subverted in Robin Hood with the series' Load Kate. Her occupation is that of a potter, but despite introducing an entire shelf full of hideous-looking pots, there never comes a time when she's given the opportunity to throw them at anyone (or in fact, when her ability to shape clay comes in handy at any point). Instead the writers give her arbitrary moments of usefulness that could have been just as easily achieved by any one of her fellow male outlaws, most of which involved Kate finding the episode's McGuffin.
It seemed that every villain in Airwolf had access to a missile-launching helicopter(s), which allows the hero to fight them with his helicopter.
One was a television related issue that only Pete could solve.
Pete's archery expertise has come in handy in more often than you'd expect i.e. it has been useful at all.
Parodied on 30 Rock episode I Heart Connecticut, with the fictional NBC show "Who Nose?" about an investigative reporter who must compensate for a lack of smell.
Reporter: You under estimated me, Congressman, because I can’t smell. But you made one mistake: You let me see the documents.
Kamen Rider Fourze's powers revolve around using Astro Switches to have attachments on his four limbs, with each of those 40 Switches having different uses. At one point after another, each of those Switches (as ridiculous as some of them may be) found some explicit use during the course of the show. An egregious example, however, is when the Musca Zodiarts is treated by everyone as unstoppable unless Fourze uses the Net Switch, which just happens to have been confiscated by a teacher.
While all versions have had this to some extent, the 60's live action Batman series cranked it up to eleven with the specifically useful things Batman has in his utility belt at any given time. Bat Shark Repellent is a perfect example.
Dr. Johnson from Scrubs occasionally got his moment in the sun as a dermatologist (one had him confirm a melanoma diagnosis), which annoyed the shit out of Dr. Cox, who considered it a mostly useless specialty. Whether it is or not, dermatologists in the US in real life make almost $200,000 a year.
In the pilot for an Aquaman series from the creators of Smallville, the villain had A.C. at her mercy, cornering him on land while he's dry (he has to be wet to use his strength). So she knocks him out and puts him on a boat and drags him out to sea so that he can fight and defeat her. Of course, he's inside the boat, so he still isn't strong yet. Fortunately there's a pitcher full of plain tap water lying around in the cabin for some reason for him to dump over his own head.
The show seems to take this backward, in that due to how incompetent most of the characters are other than KITT, the plot always seems contrived to happen in basements and otherwise deep inside buildings more than you would think.
Considering that in the pilot episode KITT smashes right through a wall to rescue Michael, adopting basements and elevated floors as hideouts may be borderline Dangerously Genre Savvy on the villains' part.
In one The Far Side comic, the town's karate club are excited to see a group of plank-shaped and wall-shaped aliens invading Earth.
Comedian Dara O'Briain had a bit about various actors who'd played the Milky Bar kid (one of whom actually was in the audience, or so they say). He asked the audience what superpower a hypothetical Milky Bar kid superhero might possess, to which they provided a series of bizarre answers such as "super-taste", "the ability to turn people into chocolate", etc. O'Briain promptly mused that, if the hypothetical superhero possessed such a superpower, each episode of the hypothetical TV series would consist of this (and then provided an example of how absurd a crime for which "super-taste" would be necessary to solve it would be).
In LEGO Batman 2, in certain areas, Joker-faced/Two-Face-faced/Riddler graffiti can only be removed by ...AQUAMAN.
Link collects a wide variety of tools and weapons. Most are useful. Others...not so much. But you can expect that the new shiny toy you just got will be used in the same dungeon you found it in, often to defeat the boss.
In older games, however, the reward might not be linked to the dungeon. Later games did this more and more, with some items ending as utterly useless aside from its one gimmick. So basically, the series flanderized itself into this.
In Soul Calibur 2, Link is, tier wise, the worst character in the game, but some weapon master objectives are easily achieved through his few strengths (read:ringouts).
In Super Smash Bros. Melee, the best character for the sandbag minigame is Yoshi, who is normally one of the worst characters in the game (he is the only character that has both no recovery for an up-B and no extra natural jumps.), because one of his attacks does very good damage without any knockback if you land it right.
And similarly to THAT, Donkey Kong, usually a mediocre character, has an attack that can make Multi-Man Melee a piece of cake.
The fact that these two are perfect for those two events is slightly lampshaded; getting a really high score in Homerun Melee unlocks a Yoshi stage, and beating 15 Minute Melee unlocks a DK stage.
The final two Event Matches in Melee are also like this. Event Match # 50 is a fight against Master Hand and Crazy Hand. Ganondorf works really well in this match because, though he is the slowest character in the game, the bosses stay stationary enough to let him put his massive power to use and destroy them in seconds. Event Match # 51 is against Giga Bowser, Ganondorf, and Mewtwo. Though the other two are still tough to beat, Giga Bowser is a perfect target for Jigglypuff's Rest attack. This attack is among the most powerful in the game, but has such a small hitbox that it's very difficult to use. Giga Bowser's just so huge that he's pretty much forced to take the attack.
The whole universe of Metroid is like this. Why, when their sworn enemy's most unique ability involves rolling up into a little ball, the Space Pirates persist in building their bases and ships with little passageways that can only be navigated by a ball-shaped object of exactly that size, no one knows.
There is also the Wild Mass Guessing that the Chozo just left a huge imprint in the technology of the galaxy - and the Chozo saw it fit to make it so any structures taking advantage of their tech were easily navigable by someone with an ability that, oh surprise, only those with access to supersecret Chozo tech could have. It's less that they don't realize the problems, and more that they just don't know which parts they can remove without the whole system going to pieces, so they prefer to just pray Samus doesn't find out about this base, the poor optimistic fools. This "theory" is inspired by the fact that it seems from in-universe diaries in Prime that between getting rid of the morphball-sized conduits and killing hundreds of their own in a humoristically macabre research to duplicate the morphball tech to be able to patrol them, the second was considered less costly by the pirate high command.
Justified by the need of having ventilation systems, that Samus would bomb anyway if they are blocked. In the nature, digestive tracts that are just too conveniently placed between rooms are the paths Samus takes.
Advance Wars has maps that, while normally quite the challenge, become ridiculously easy when the right CO steps up to the plate, and that's NOT counting COs already established as Game Breakers. The map Megalopolis is a prime example: There are no less than 6 Comm Towers on the map, and Javier dominates any map when four or more are in his control.
Mega Man Powered Up (an Enhanced Remake of the original Mega Man 1) allows you to play as any of the six original Robot Masters (and two new ones, given the proper conditions), so all of the stages from the original game were redesigned to allow them to make it to the end without getting stuck.
Final Fantasy III has the very much subpar "Scholar" job, which can cast low level white and black magic, as well as attack enemies with books. But their special ability should make up for that right? They get the ability to analyze the enemy to get info on their health, and drops and elemental weaknesses/resistances, which is.. not very useful compared to other jobs. Except for that one boss right after you get access to the scholar job, the one who relies on changing his weak spot around to change his elemental weakness! You'll want a scholar for that fight. Not for any others though.
Final Fantasy V features a Class and Level System. While many jobs are very useful from the get-go (such as Monk or Ninja) others, like Bard, don't get to do much... but the Bard does very well against the two Bonus Bosses (as Romeo's Ballad can temporarily stop Omega, and Apollo's Harp deals lots of Damage to Shinryu).
He can also do a lot of damage to Undead enemies with Requiem. Many strong enemies are undead, some very surprisingly so.
Final Fantasy VI has Celes' Awesome, but Impractical Runic ability. While being able to absorb enemies' magic attacks and earn MP back from them sounds cool, against weak enemies in Random Encounters there's kind of no need since they don't use magic that often and are easily dispatched anyway, and is only useful if you really need to refill your MP. As a result some bosses were deliberately designed to be near impossible to beat WITHOUT using Runic. In fact, the first boss you fight right after recruiting Celes fits this—it casts a lot of magic and the Genre Savvy Celes recognizes this and tells the player to use Runic in anticipation of its spells. For many players, this is the only time they ever use the ability, since Runic will also absorb any friendly spells you cast, like Cure.
Incidentally, almost every character's unique ability in the second half of the game amounts to a Slap-on-the-Wrist Nuke at best. With a well-kitted team, it's almost always preferable to use Fight or cast Ultima, as most of the normal abilities just can't measure up to its damage potential.
Runic becomes more useful with a perm-hasted Sabin, as he can heal with mantra in between his Bum Rush frenzy.
In the first game, your first gym battle is against an opponent whose team is specifically designed to counter your part of the Elemental Rock-Paper-Scissors triangle. However, due to events you'll do before the fight, you'll end up with a Pokémon who directly counters their part, turning the odds in your favour. The Pokémon themselves are pretty mediocre statistically and will probably only serve you for this one battle and not much else.
In Pokémon Black 2 and White 2, however, Elesa still retains her difficulty level if your own Mons are underlevelled... unless you use a Sandslash with Rock Tomb and Dig. Sandslash's ground typing locks Elesa's Mons out of Volt Switch, and allows Sandslash to keep on attacking.
In competitive battles, several Pokemon are only useful for one situation or to counter one thing. Quagsire is only used to counter enemy stat boosts for stall teams, while Aerodactyl is used to quickly get Stealth Rock on the field for of heavy offensive teams. Half the Uber meta game revolves around countering Kyogre.
Poison and Steel Mons have always been rather lame; Both are notorious for having the worst offensive Mons in the seriesnote Previously, Poison was only super-effective against Grass, and Steel was only super-effective against Ice and Rock. All three of those types have enough weaknesses that Poison or Steel attacks are almost never needed to hit them hard. In the metagame, the only times they're used are to take advantage of STAB bonuses, make use of the priority move Bullet Punch, or when there are pretty much no other options., but they fill this role in Pokémon X and Y where Fairy-Types are the new powerhouses, and players will need to be packing Mons with good Steel and Poison attacks to deal super-effective damage to the Fairy-type Pokémon, who can hit hard.
In Starcraft II, a lot of of the new units are introduced in missions specifically designed in such a way to make the new unit obscenely useful. The worst example of this is the Diamondback—it's a unit that can attack on the move and does bonus damage to armored targets, and just so happens to be introduced in a mission where you need to chase down fast-moving armored trains. After this mission you'll never build them again because they're rather expensive and simply aren't very useful in comparison to more versatile, cheaper units like the Goliath and siege tank.
Though the game justifies it fairly well by explaining that your ship's engineer can only prepare new hardware so fast and he makes a point of working on stuff that is specifically going to be useful for the next mission.
In Multiplayer, there exists a 'Hard Counter' mentality, where the standard response to "Unit X is Overpowered!" is "Make more of Unit Y." While there are indeed quite a few units designed with such purposes in mind, higher level play has shown that with sufficient skill anything can hard-counter anything else barring targeting limitations (air-only or ground-only).
This is the case in many RTS games, such as those in the Command and Conquer and Supreme Commander series, where they introduce a new unit, building and power each level.
Just get a new player character in a Golden Sun game? Expect a dungeon revolving around some trick that only they can perform. Likewise, old characters learning new tricks frequently leads to a dungeon in which that skill is required above all else.
The most obvious offender in Dark Dawn is in the lead-up to the final dungeon. A gate can only be opened by walking a very specific path that is only "visible" by use of Sveta's Track Psynergy, a power that is needed NOWHERE ELSE IN THE GAME (not even in her debut dungeon when she demonstrates it, that one focuses more on her Slap Psynergy).
Eoleo's Thermal is up there too: He knows it...for the hell of it, as otherwise he's a carbon copy of Tyrell, it takes a while for it to get used, and then it's only used for a short bit. Sure, he joins late, but Himi joins later and her Search psyenergy gets way more plot and mileage!
In Craggy Peak's Zodiac-themed dungeon, there is a very confusing puzzle involving moving around statues of goats. The nearby stone tablet is cryptic ("The goat leaves no trace behind."). Looks like a job for Insight Psynergy!
Lampshaded in the Spiderman 2 game. When someone tells Spidey that a ship is sinking near of the coast, he says something like: "This looks like a job for...some kind of swimming superhero! ...But since none are around, I guess I'll have to do."
It makes sense that the actual test chambers in Portal can all be solved using portals, as they were specifically designed with this purpose in mind. However, the maintenance corridors and so on were ostensibly not designed as such, and yet they are all perfectly suited to navigation via portals. This lends a certain credence to a popular fan theory that The Game Never Stopped.
Each hero in Superhero League of Hoboken has one main power, which can range from necessary mobility (being able to swim), combat-worthy (induce rust, put animals to sleep, raise cholesterol) and always useful; supportive (see through pizza boxes, clean almost any mess, vanquish baked goods) and nice to have along when the need calls for it; and highly specific (eat spicy food, fold road maps) which are called for once, maybe twice in the whole game, to complete a very specific challenge. (The hostel is buried in old, unfolded roadmaps! This looks like a job for Princess Glovebox!)
In the DS rerelease of Super Mario 64, Wario is one of the new playable characters. For the most part, he's useless compared to the other characters, but he is the only one able to break the black bricks.
Mega Man Day in the Limelight 2 gives us Bubble Man as a playable character. He's the only character that can swim, but you arn't likely to use him much outside the water.
xkcd: Invoked in the Alt Text of "Etymology Man", wherein they pedantically dissect the etymology of "tidal wave" until they are surrounded by the rising water.
"I can't believe I'm saying this, but I wish Aquaman were here instead—HE'D be able to help."
Lampshaded in the infrequent Supers theme of Irregular Webcomic!, when the GM complains that it's really hard to come up with an uncontrived Eigen Plot to fit Dino Boy, Captain Spatula, Refractive Man, and Worm-Master. Hilariously, outside of the one strip where he managed it, Worm-Master is an aversion - his loyal worm hordes are never any use.
There was an episode devoted to Mekaneck complaining about his relatively useless power. Almost every obstacle in the episode (a maze of long narrow corridors, toxic gas that remains low to the ground, and the villain holding the Artifact Of The Week from a high altitude) seemed tailor-made for a person whose neck extends.
The show also had a subversion during a later episode, by giving the villain Stinkor - a humanoid skunklike character with the power to, well...stink - an upgrade that turns his rotten B.O. into directed jets of corrosive gas. As such, what was a laughably weak villain that the original series' creators swore they would never introduce to the series became such a big threat to He-Man and gang, that - during his debut episode - Skeletor not only recruited him as a minion, but also excluded from his usual punishment to failed minions.
An episode of The Tick had Arthur and the Tick follow the normally useless Sewer Urchin on a mission into the sewers where it turns out his abilities are phenomenally useful, he has a cool hideout, he is respected and feared by the natives, and he is unflappable in the face of the sewer's strange and horrifying obstacles. He describes himself as "the apotheosis of cool" in his realm.
Said almost word for word by Gear in Static Shock when Static and Gear fight a water-using Bang Baby. Static's response? "He'll have to get in line."
This was the main failing of the Legion of Super Heroes cartoon episode "Karate Kid" - Karate Kid earns his place in the Legion not because he's a Badass Normal who can keep up with the rest of the Legion on a usual mission but because the villain of the week happens to obtain a way to nullify superpowers. Particularly unfortunate since Karate Kid in the LSH comics embodies the Charles Atlas Superpower and doesn't require such contrivances.
To a lesser degree 'Phantoms' had Phantom Girl save the Legion from being trapped in the Phantom Zone by using her intangibility (though Brainiac 5 and Lightning Lad helped amplify it to phase everyone out).
By season two of Dino Squad, the writers were apparently at a loss as to why vain, fashion-obsessed Caruso was still a member of the team. The result was an episode devoted to his battle against the Big Bad's guards and a horde of voracious giant ants - both of which helpfully turned out to be terribly vulnerable to yoga and scented hand cream.
Averted and yet not by Teen Titans. While Aqualad does only show up when the ocean is in trouble for the most part, he's proven that he's perfectly capable of fighting on dry land. (Most people tend to forget he's also got super strength - AND Hydrokinesis.)
Averted in that the Aqualad of that continuity can harden his water into solid objects like swords, have durability that can withstand Superboy's bounding, and electric eel tattoos on his body. He's was also the team's leader during the first season.
However, Lagoon Boy in season 2 plays this trope straight. He lacks Aqualad's weapons and electrical abilities and so usually only gets used in missions near bodies of water. The Heroes Unlimited nature of the second season makes this far less obvious, though.
This does get a lampshade when Lagoon Boy whines about being "stereotyped as the water guy" but it's at least as much because he's obnoxious and annoying and no one really likes working with him unless the situation absolutely requires it.
Doubly subverted by its version of Aquaman. He straight up tells the League to shove off, and only later, after making nice, does he say he'll help out...when he can. He doesn't really care about the surface world and their problems.
Not to mention that not only does he have limited invulnerability, strength, and is a general badass, he also commands the most powerful and advanced navy in the Justice League continuity. He's no longer the guy you call for underwater base missions; he's the guy you call if you want the enemy's base and possibly nation pounded into damp gravel and blackened craters by an armada of battleships.
On the subject of that Aquaman fight, the Shape Shifter (who's name is Shifter) fights Aquaman underwater, as a T-rex. Her brother Downpour gets a bitchslap after trying to punch Aquaman for harming his sister.
In the Justice League, his Required Secondary Powers are actually enough to have the advantage over Wonder Woman even before he enters the ocean and becomes far stronger. Superman still trashed him after he beat her, but it was rather awesome.
The Atom, even though he only has one power, the ability to shrink averts this trope. We only see him use his powers twice on the show (once to take down the heart of a world eating nano machines and to run from Amazo didn't work) he still is a genius physicist and the League on occasion has called on him for his unique expert opinion. Still is rarely seen on the show.
Related to the above the first showing of Aquaman in the DCAU is in Superman: The Animated Series, he's shown having been captured and imprisoned by Luthor looking pretty weak. It turns out he was trying diplomacy first to stop one of Luthor's projects that was damaging the ocean particularly badly. When one of Lex's mooks tries and shoot him in the back with a harpoon gun (the kind used to kill whales), he catches it, summons forth a fucking scary naval armada and obliterates Luthor's state of the art boat without even blinking.
Triply subverted where Aquaman has garnered an almost Memetic Badass status. Not only is he useful on both land, sea, blood streams, and other planets but also is a Boisterous Bruiser who embodies all the wonderful cheesiness of a silver age comic book hero. Say it with us: OUTRAGEOUS!
It's sometimes played straight, though, such as "Aquaman's Outrageous Adventure!", in which Clock King holds someone hostage right next to a lobster tank and the finale (but not most of the episode) takes place by a harbor. Other times, Aquaman keeps his powers useful through Achievements in Ignorance.
Double subverted in the episode "Night of the Batmen!" Aquaman, filling in for an injured Batman, fights The Penguin. Penguin makes a daring escape on his submarine* Praise Poseidon!, but then quickly steers the sub onto land and cuts through the city streets * How does Batman do this alone?.
In an episode of Kim Possible, Mr. Stoppable, the Actuary, spends an entire episode trying to be a hero to his son. He finally succeeds when his number-crunching skills turns out to be very useful against a one-shot villain with a very specific gimmick:
Kim: Did you know your dad had it in him?
Ron: No, but who knew we’d be facing a math-crazed villain?
Happens in every single episode of the original 1973/74 season, usually in the form of a villain-caused problem occurring on, under or near a body of water. In addition there were several water-based opponents on the show, such as the episodes "Dr. Pelagian's War", "The Weather Maker" and "The Watermen".
Averted in one episode where he and Black Vulcan (who has electricity powers) go to save a nuclear submarine, and Aquaman utterly fails at being a help at all, mostly making things worse.
In fact the TV series is where most of the notion of Aquaman's uselessness comes from, due to the writers not being allowed to show him hitting anyone, so they were left with "being good at swimming" and "can ask fish for help."
An episode of Garfield and Friends has some X-men Expies. Our Gambit expy had...Super Cottage Cheese control powers... Fortunately, Jon, being a complete moron, had been scammed into buying a very expensive yogurt-dispenser with a warranty (Well made, just priced the same as a mansion). Fortunately, those Cottage Cheese powers end up working on the yogurt.
In one episode Timmy wishes for SUPERFRIENDS and gets a whole team of superheroes. One of them is a blatant expy of Aquaman who is only useful in water. When Timmy tries to get rid of them:
A.J.:(When asked for coordinates) It's a planet filled with water.
Wet Willy: I say we go!!
A full episode dealt with Timmy wishing for a world of Superheroes after being shafted by ordinary job folks in time of need (fireman, milkman, postman, etc). After Nega-Chin strips away his and his friends powers. Said people wind up aiding in helping Timmy defeated the Nega-Chin.
Fry has no superpowers; he also has no Delta brainwaves, making him the only organism more advanced than a tree who can battle the Brain invasion. He has also saved the planet with his knowledge of 20th century television, and his 20th century garbage making skills. Among other things.
Also, Dr. Zoidberg, whenever his unique powers are required - for example in "Mothers Day" when Leela gets him to open a tin can with his claws. "Hooray! I'm useful! I'm having a wonderful time!"
Bender was built initially designed as a "bending unit". In other words, a robot whose sole purpose was to bend objects. Not just objects, but specific ones, namely girders. Not just girders, mind you, but specifically girders used for making suicide booths. This is all explained in the Pilot Episode, in which he later gets "deprogrammed" to bend universally, but his primary function remains throughout the series, where he and his employees frequently find themselves in situations where the solution just happens to involve bending an object. Though being Futurama of course, it's subverted quite often. Where Bender would declare some tasks as "primitive, degenerate forms of bending" and just do them anyway.
It's also surprising how many thing Hermes has to limbo under to save the day.
One episode had the babies imagining themselves as superheroes from Phil's favourite TV show. The first adventure they went on utilised every baby except Chuckie (whose power was he drove their van). So Chuckie came up with the next adventure that made him the hero. This time everyone except Kimi was used so it was her turn for the next adventure...
An earlier episode, "Mega Diaper Babies", had Tommy, Chuckie, Phil and Lil pretending to be their favorite heroes, the Mega Hyper Heroes * Basically expies of the Fantastic Four and the Power Rangers. While Tommy, Phil and Lil had useful abilities * Shapeshifting, rapid fire spitballs and being a dotted line armed with an airplane, Chuckie only had "the smell of two babies". However, it's that power that defeats Angelitron * Angelica basically playing along, despite the fact that she stole their toys and wins the day.
In Sabrina: The Animated Series when they end up getting sucked into Harvey's comic book world, Sabrina has the seemingly useless power of making squids shoot out of her hands. Harvey's power is to make anything he draws come to life and when he runs out of ink seconds away from defeating the Big Bad, Sabrina suddenly remembers what squids can make...
In Star Wars mythos, the Jedi Council had its own version of Aquaman in the form of Jedi Master Kit Fisto, and one episode of the original Star Wars: Clone Wars had him featured as the protagonist, leading a battalion of Clone troopers against the Quarran Isolation League, rather successfully, at that. (He had no dialogue, sadly, but that series was kind of short on it as a whole.) Of course, seeing as he was on the Council in the first place, it's likely that situations like this tended to arise more often than you'd imagine.