One Piece has plenty, as Word of God has said the Devil Fruits themselves don't become more powerful, the users just use them better with more creativity.
Rubber Man Luffy, since among the other Devil Fruit abilities his one is rather low-tier until he starts inventing other uses for it. Especially noticeable when Luffy goes up against God Eneru, as due to his rubber body not conducting electricity Luffy was probably one of the few people on the planet who could've defeated him without needing a high level of Haki. Luffy also gets particularly clever with his abilities by taking advantage of the fact that his entire body, including heart, blood vessels and bones, are all rubber. For his "Gear Second" technique he pumps his blood flow to a pressure level that would kill any normal human, giving him Super Speed. For "Gear Third" he inflates his bones to increase his hitting power significantly, at the cost of speed.
Kaku boasts about gaining the power to turn into a giraffe◊ AND becomes more powerful thanks to it. When he reveals his power, everybody in the room laughs at it despite him warning them not to underestimate the destructive power of the giraffe. When they persist, he gets angry and combines his power with his Tempest Kick to cut the Tower of Justice in half from the inside.
In fact the CP9 were this for Zoan type fruit users (ability to turn into an animal) since until then animal powers were rather lack luster compared to most Paramecia (get a super power) and all Logia (become a nigh invincible force of nature). Until them the only notable Zoan user was Chopper who had to use special medicines to tap into different forms in order to fight effectively. But the CP9 leader points out that Zoan fruits also grant the user extra animal-like strength and alter their bodies (like growing claws) and since all of CP9 were highly powerful martial artists, in their hands Zoan fruits became utterly terrifying by making already powerful fighters that much stronger.
This is prevalent all over the series. Scratchmen Apoo, who can control sound effects, thanks to the large number of onomatopoeia in Japanese, he can do such things as cut anything or anyone that can possibly be cut under normal conditions. Wapol can merge anything into his body by eating it; as one result, he invented a metal so useful he was reappointed a lesser king by the World Nobles. And a main character who accidentally combined the power "to not die once" with getting lost, with the result being functional immortality and inhuman abilities. Batholomew Kuma, with the ability to push things, is one of the most powerful characters in the story, especially since his power includes the ability to push abstract concepts like pain and exhaustion. Baroque Works Member Mr. 3. His power allows him to create an endless supply of candle wax from his body. He was also able to make this wax hard as steel and make keys out of it, but its greatest use didn't come in until he used it to stop the flow of the Warden Magellan's impossibly corrosive and utterly deadly poisonous flood. Doflamingo has the power to create strings. Which he uses to control people, cut nearly anything in half, and fly.
In a more literal version of this trope, the author states that Sanji is able to set his leg ablaze without hurting it because his heart burns hotter.
Nami's Tempo before it was upgraded, was basically a party gag that she managed to rework into being able to create lightning and summon storms.
In the Doflamingo Pirates, we have Sugar, a little girl who ate the Hobby Hobby Fruit. It gives her the ability to turn people and animals into Living Toys. The secondary abilities of the fruit are what really make it shine. It also allows Sugar to create verbal contracts that essentially brainwash the toys into doing whatever she wants and there's nothing they can do about it. Anyone that knew who the toys were before their transformations will forget all about them, so it's not like their families or friends will seek revenge. The Hobby Hobby Fruit also halts the aging process the moment they eat it, so Sugar isn't really a "little girl". And all it takes to become a toy is by lightly touching the victim, no matter how strong they are. Sugar is far from a powerhouse, but if this fruit was consumed by a Lightning Bruiser in his/her prime, it could easily be a Story Breaker Power.
The Law of Ueki bases its entire setting on this. Nearly every power (with precious few exceptions) is weak or weird, but with proper application can become deadly.
Everyone in JoJo's Bizarre Adventure. Powers like healing, making maps and summoning zippers have all deadly combat applications when in the hands of a stand user. In a world full of guys who can stop time, have super strength, and can even reset reality, it's amazing to see what such seemingly under-powered abilities as controlling sound effects and drawing manga can do.
Koyomi in Yoku Wakaru Gendai Mahou has the ability to turn any spell into a basin. It seems like a lame power at first, until everyone around her realizes that it can transform any spell, regardless of who cast it or how powerful it is, effectively giving her a universal Power Nullifier. Koyomi herself never actually catches onto this.
The main character has the power to manipulate... bananas! In a world where there's more traditional powers like water manipulation and superhuman strength, this seems like a pretty lame power to end up with. But Nana later explains (and demonstrates) she can manipulate everything about the banana, including size, weight, and hardness. Cue the absurd, but unusually badass scene of her using the banana as a bludgeon, morphing into a shield, and then morphing it into a sword. The fight ends with the villain taking a hostage, and demanding that Nana, "drop [her] banana!"
A maid whose power is to "do housework really fast". She uses a sword in the form of a mop so her movements are extremely fast.
In Aphorism, each character's power comes from a specific Japanese kanji. For example, the kanji for "change" allows someone to change their appearance; the kanji for "katana" allows the user to use ...a katana. In comparison, the kanji "righteous" is pretty much useless in combat, and its user usually winds up being the Damsel in Distress.Then we learn that the way the kanji manifests itself depends on how the user chooses to interpret the word. And then she realizes that the kanji for "righteous" and "subjugate" look very similar...
In Iris Zero, most of the Irises have seemingly useless powers like seeing what mood someone is in that they use to do things like manipulate people.
Unico can work all sorts of magic for those who love him, and he's very good at making friends. He also has the power to stop wars, anywhere he goes, just by existing. This power forces the gods to intervene and try to get rid of him because he seriously messes up their plans.
We have Fiamma of the Right, one of the most powerful characters in the series, outclassing monstrosities such as a fully-awakened Accelerator. His power? "The Holy Right", the ability to use all other Biblical abilities relating to the right hand. Lame? This is the ability that allows him to destroy an entire city in one fell swoop, teleport instantaneously to another location, and create a FlamingBFSforty kilometers in length.
Cendrillon has the power of Cinderella. This gives her incredible speed and agility to casually dodge a cluster bomb strike (based on Cinderella's dancing and the pumpkin carriage) and lets her remotely mutilate the feet of anybody with a different shoe size than her. After all, she can do anything that has to do with the story of Cinderella.
Paper Masters in Read or Die have the power to mentally control and manipulate paper, which also evidently comes at the cost of such extreme bibliomania that it can functionally ruin their lives (they can be hard-pressed to resist the urge to read or enter a bookstore when they should be, say, working, and they typically spend hundreds to thousands of dollars a week on new books for their collection). However, like the Banana no Nana example above, Paper Masters can control everything about paper and control all paper around them — leading to stunts like creating plane-swallowing dragon-beasts from masses of paper, turning a simple page into a sword that can cut through steel, and more. There's a reason why "Paper Master" is now considered an example of Stock Superpowers.
The Tsuchikage has Dust Release as his bloodline power. No, not blasting people with dust like Gaara does with sand, creating dust by pulverizing everything in its area of effect. Dust Style has been translated as Particle Style, and one of these abilities creates a massive explosion. It's not dust. It's molecules, atoms, and a nuclear explosion.
The title character himself once used his "Sexy No Jutsu", which transforms him into a hot girl, to successfully stun the Third Hokage (the head of his village and one of the most powerful ninjas alive) in order to steal something. The ability is generally treated as a joke.
680+ chapters later, Naruto uses the "Reverse Harem no Jutsu" to distract Kaguya Ootsutsuki, the mother of the Rikudo Sennin for a few seconds and then he and Sasuke attacks her with Taijutsu.
Konan's paper jutsus seem pretty tame, especially compared to her partner. Then we, and the Big Bad, are reminded that the primary explosives in this series are made from paper tags when Konan busts out 600 billion of them.
Among the Tailed Beasts, Kurama's ability is that he can sense negative emotions. His brethren have abilities like "can generate lightning" or "can control sand." However, his ability makes for a flawless Enemy Detecting Radar, rendering it impossible for his enemies (or Naruto's, for that matter) to send spies or infiltration units or even hide, severely limiting possible strategies to use against him.
In the anime adaptation of Medaka Box, Fude Ezumachi can change the colours of things. He manages to disable one of the most powerful characters in the series by turning their skin splotchy blue, causing their body to react as if it had been heavily bruised. He also displays the ability to make things brittle by turning them grey, can shake his enemies' confidence by turning them pink, and implies that he can use the colour red to open massive wounds that bypass any defense.
A few nen abilities seem like this. Kortopi of the Phantom Troupe and his ability to copy items doesn't seem particularly useful to a Carnival of Killers. Except that things he can copy extend to entire buildings, and he can sense the positions of his copied items as well as anyone inside the particularly large ones, making him a good Enemy Detecting Radar. It also turns out to come in handy when the Troupe needs to Fake The Dead.
Hisoka with his Bungee Gum. His ability lets him make his nen stretchy and sticky like gum, and apply any kind of texture to it. He also happens to be one of the strongest and most dangerous fighters in the series, especially since his ability opens up all kinds of opportunities for misdirection (fitting his Monster Clown magician persona.)
Played for Laughs initially when Gon's mentor Biscuit reveals her nen ability to be...a personal masseuse. But then it turns out that it's actually a very good support ability, allowing Gon to get seven days worth of rest in only a half hour (and she's badass enough not to need an offensive nen ability especially when she reveals her Brawn Hilda true form.)
Villain Eran Dayan was allergic to men, getting hives whenever a male came near him. Initially played for laughs... In the same scene it allows him to realize that Ryo had disguised himself as a woman, and when Ryo moves for the kill later in the arc he discovers that Eran can use his allergy to sense his presence, negating any kind of disguise and hiding technique.
From the same arc, Ryo has the ability to make his Gag Penisretire in his body. Found out when he infiltrated Eran's manor disguised as a woman only to have Eran's Dragon check his gender by watching his crotch (a standard procedure).
In the sequel Aquarion EVOL, Andy's power is...to dig. While this seems useless at first, as all he can do is basically cut away large sections of ground to dig tunnel's slightly faster than normal, he eventually is able to create instant pitfalls in solid steel, and towards the finale can even generate black holes. His love interest MIX has the opposite power, she can fill holes, and while this initially seems limited to just to filling up Andy's work, she's later shown to be able to instantly repair battle damage to the Aquarion, and fill up the openings in enemy gun barrels and thrusters, causing them to destroy themselves when they fire or move.
In Stealth Symphony, Slice's blood, once it leaves his body, coagulates into gold coins. This is his only innate ability, and he shows no fighting skill greater than an average human. However, he has spent his years using these gold coins to buy powerful magical weaponry he keeps on his person, as well as build a business empire so that he can influence high-ranking government officials. He currently owns at least three such magical items (one of which is even called MacGuffin), which together allows him to go toe-to-toe against the strongest combatants in the world. As the coins are physically much larger in volume than the blood they transformed from, he also sometimes deliberately sticks a bleeding hand down opponents' throats to burst them from the inside.
"He could control every creature that lives in the sea. But I don't think either of you know what that really means. Do you know, do you understand, do you have any idea how much life there is in just one single square mile of sea? I don't think you do... and if you multiply that by lots of miles in every direction... I'd never seen anything like it in my whole life... and God as my witness, I hope to never see it again."
Perhaps the most awesome (and funny) example is Aquaman's friendship with sea life used to defeat Namor. Poor Namor never knew what hit him when he had an orca dropped on him.
A few writers give him the ability to command not only sea life, but also any animal with any connection to the sea, even vestigial or ancestral. Considering life originated in the ocean and every animal has an aquatic common ancestor, that means he can control every animal that has ever existed, including humans.
It even included martians kind-of impersonating humans (or possibly just humanoid aliens), most likely indicating that the author had forgotten about his own reveal.
Earlier than that, an issue of Ultimates 2 shows what happens when she uses those energy blasts of hers at full size.
Plastic Man. Hoo boy, Plastic Man. Many villains underestimate Plas for his goofy demeanor and the stigma that stretching powers are useless. Well, Plas doesn't have stretching powers, he's a shapeshifter, and possibly the greatest in The DCU at that. He can change his size, shape and density, enabling him to shrug off damage that could kill many members of the Justice League outright and raise the density of his fist to be able to explode your head with one blow(but he's too nice for that). His shapeshifting is so radical that he can pretty much hide in plain sight any time he wants by changing shape into everyday things, too, so you won't see him coming. Oh, and as a side effect to his shapeshifting powers, his brain changes shape so drastically and constantly that he's effectively immune to psychic powers. Pissing off Plastic Man is a terrible isea.
Rising Stars has Laurel Darkhaven, who can telekinetically manipulate very, very small objects. Such as your carotid artery. She becomes a government assassin. She later uses the ability to control "very small things" to telekinetically sift EVERY inch of arable soil under the entire Middle East in order to make the entire region fertile again. The results can be seen from ORBIT! Turns out many powers are like this because of implications or aspects directly hidden. Poet's powers are supposed to be just minor energy abilities, but they're actually control over the Power itself...
Peter once ripped part of Norman Osborn's face off when he stuck to it with his stickum' powers during American Son. Not how Stan Lee imagined it being used, but awesome regardless.
On a similar note, Spider-Man's clone Kaine once used his sticking powers to tear off a piece of wall and beat the Rhino over the head with it. He would also use his sticking powers routinely to leave the "Mark of Kaine" on his victims, in the same manner that Spidey would later use them in the above-mentioned American Son example.
Mr. Brownstone was a minor villain who could teleport matter... a few grams at a time. He used those powers to become a drug dealer catering to wealthy clients wishing to indulge in heroin without any nasty needle marks. Naturally, he could also teleport drugs to people's systems against their will...
This is a grand tradition for the Legion of Super-Heroes. Most characters have only one power, and it's not always something very impressive.
One of their first recruits was Triplicate Girl, with the ability to transform from one ordinary teenage girl... to threeordinary teenage girls. Useful for doing chores and pretty fun in bed, but not much good in combat. At least, not until she became a master of Tri-Jitsu, a martial art based around the fact that you have six arms, six legs, and three potential points of attack to coordinate from. She is also an expert infiltrator. Go somewhere, leave a body behind, and walk out.
Matter-Eater Lad can... eat anything. Hardly a power to write home about. However, seeing as Tenzil Kem's personal definition of "stuff" includes laser beams and doomsday computers, you might want to keep him around in case you need to get rid of something. In one story he ate a supposedly-indestructible wish-granting device and so saved The Legion from the invincible monster it had created after everyone else failed. It drove him mad, but hey, he saved the universe! In at least one version, being able to bite through and chew up anything meant the Required Secondary Power of acidic saliva, providing a potentially nasty ranged attack.
Chemical Kid can alter chemical reactions. In the New 52, he is terrified and asks why everyone is counting on him to subdue a rampaging Daxamite (Daxamites are just as powerful as Kryptonians). Element Lad talks him through slowing down the chemical reactions in the Daxamite's brain, causing him to pass out, then reversing the chemical reactions in his skin that let him absorb and process sunlight, taking away his powers. This means Chemical Kid can defeat Superman if he wanted to. "Altering chemical reactions" has every potential to be lethal if it can affect living organisms, which kind of depend on carefully balanced and regulated chemical processes to keep working. (Note, though, that things like transmuting elements or causing/affecting nuclear reactions don't technically fall under "chemistry".)
Almost all of the Substitute Heroes qualify in one form or another. Color Kid is perhaps the best example of the trope. He can make things change color. Doesn't sound like much, but during his tenure he turns green kryptonite into (harmless to Superman) blue kryptonite, switches the color of the sky and the ground (confusing fighter pilots), create clouds of "black" to blind opponents (and power up his teammate Night Girl, who loses her powers in daylight), change someone's entire body to match a wall to provide camouflage, and fire day-glo beams that blind and confuse opponents. Turning yellow sunlight into red might instantly de-power any daxamite or kryptonian opponent as well, depending on the writer's interpretation of that weakness.
Batman villain The Ten-Eyed Man is an interesting case. Fans usually interpret this as him having ten additional weak points. But as most people will attest, eyes are pretty useful. Eyes on your hands? Makes you one of the best shooters in the DC universe, due to an unerring aim. Makes it a bit hard to pull the trigger, though... It can make him nearly impossible to sneak up on, as long as he holds one hand behind his back.
The Ten-Eyed Man is actually nearly the inverse of this trope, since his early appearances tended to heavily play up the "awesome" of being able to look at things by waving at them and downplay the "heart" of not being able to use his fingertips for anything. This led to ludicrous scenes where jailers at Arkham kept his hands locked in a specialized box, because his power to see in all directions (but only when he has his arms outstretched) apparently made him impossible to contain normally.
Stormwatch: Team Achilles had a character whose superpower was to make plants grow really fast... and he worked as an assassin. The thing is, most people at any given time have seeds in their digestive tract from the vegetation they've eaten, and growing those up to full plants in a few seconds leads to a nasty death from internal injuries and/or choking.
Which is itself a Shout-Out to Swamp Thing, once Alan Moore thought about what a character with complete empathy with/control over plants could do when he reworked the character. This very method leads to a striking Body Horror demise for one of Swampy's foes after he eats a burger containing tomato slices, lettuce, pickles, onions...
Doug "Cypher" Ramsey of the X-Men junior team New Mutants originally had the power of comprehending languages, and that was it. Then he died and came back, with his powers expanding to all forms of "language". This includes computer language (making him a master hacker and programmer), arcane languages (letting him cast spells), body language (giving him the ability to fight all the New Mutants at the same time and win), and even the structure of buildings, allowing him to pinpoint their weak spots instantly. Took a Level in Badass, indeed.
Marvel's Razorback has the mutant ability to instinctively operate any vehicle. Including Alien Humongous Mecha that didn't exactly come with instructions.
In Secret Avengers, the Scientist Supreme informs D-list supervillain Mentallo that his limited psionic abilities are quite laughable, but could be invaluable if utilized correctly. Cut to the next issue, where Mentallo throws the U.S. government into a state of emergency after mentally hijacking their entire fleet of Iron Patriotdrones.
Dove of the Teen Titans is a low-level Flying Brick, who also has the power of "perfect peace". In the Blackest Night crossover, this not only allowed the previous Dove to not come back as a zombie, but also allowed the current Dove to destroy hordes of the otherwise-unstoppable emotion-powered zombies at once.
One of the Captain Planet comic books actually has Ma-Ti lamenting over how lame "Heart" is as a power after Wheeler makes fun of him for it. Later in the issue, Ma-Ti uses his ring to reach and understand the hearts of all the creatures in the forest to help the other planeteers, including bears.
Marvel's very own Fad SuperDazzler has the ability to absorb sound and convert it into light. This may not sound like much at first glace, but she can do things like blinding people with bright flashes (duh), create a strobe effect that upsets equilibrium, create holograms, and even Frickin' Laser Beams. She's also immune to sound based attacks, because they just make her stronger. This ability is shown to be obscenely powerful, as Galactus once recruited her to retrieve one of his Heralds and exposed her to unimaginable sounds, including the explosion of an entire galaxy (yeah yeah, no sound in space. He's Galactus, he has no care for your paltry human physics), to boost her to sufficient levels. In fact, Black Bolt of the The Inhumans, who's considered one of the top-tier powerhouses of the Marvel Universe, would literally have NO WAY of defeating Dazzler with his strongest attack. It's also suggested that one day, she could expand this property to cover other fundamental forms of energy. Ever set off a nuclear explosion with a boombox?
Some of the Falcon's powers include talking to birds and seeing what they see. It doesn't sound too promising until you realize that these birds can aid him in battle and act as his spies from everywhere. It's been implied that between Nick Fury and all of the resources of S.H.I.E.L.D., and Falcon with all of the pigeons in New York, Falcon has the better intelligence network. His powers also affect other creatures that have some sort of avian ancestry. An issue of The Avengers has him rescuing the team from a group of raptor-like aliens by using his abilities to force the creatures to flee. People also forget that his wings are pretty powerful melee weapons, as demonstrated when he decapitated an Ultron drone during Avengers Disassembled. This was lampshaded during Ultimate Nightmare, when Black Widow jokingly said that Falcon wouldn't impress Unicorn by flapping his wings, only to retract her statement when he used them to eviscerate the villain.
Cixi's power from Lanfeust is to change water's state between liquid, solid and gas. Said like this, it doesn't seem that powerful, but it becomes horribly creepy when you realize humans are mostly made of water. At one point, Big Bad Thanos has her causing a man's blood to boil until he is literally burnt from the inside.
Heck, the comic is filled with examples of the trope. For example, after Thanos manages to take over Troy's capital, a man whose power is reading the future in animal entrails (e.g. a haruspex) uses said power (shown earlier to be somewhat useful but nothing special by the setting's standards) to run a very efficient underground insurgency, and one of his best elements is a big, middle-aged woman with the power induce horrible, crippling indigestion in people just by looking at them (something she does to mooks with great relish).
In Luminosity, Elspeth's powers all relate to effective communication. On its own, it can be used as an Exposition Beam or to be magically convincing, but only of things she genuinely believes. Not useless, but far from a Story Breaker Power. But it gets very interesting when someone capable of Power Copying gets involved and starts using it on Elspeth herself and in combination with other peoples' powers. By the end of the novel, she can resurrect the dead. (Human sacrifice required.)
In Shinobi Of The High Seas, like in canon, Ms. Valentine can change her weight anywhere between 1 kilogram and 10,000 kilograms. At first, she merely uses it to float up and then crush people. Later on she learns that her physical strength is the same no matter how much she weighs and that she can increase the weight of other people/objects. Cue Ms. Valentine one-shotting a Pacifista by axe kicking it with a 5,000 kilo leg.
Jinx in Consequence of Misunderstandings shows just how deadly "bad luck" is. If used on an object, it generally breaks. If used on a person, it causes anything from a sprained ankle or broken bone to a heart attack or brain aneurysm.
In The Cutie Mark Crusaders Use Their Brainsssss, Scootaloo has the talent of... turning things orange. Well, "gamboge," technically. This seems completely useless and has the added insult of her cutie mark being the exact same color as her coat, and thus effectively invisible. It becomes critical to saving Equestria from, yes, a Zombie Apocalypse.
In Pony POV Series, Lemon Hearts' special talent is lemons. just lemons. However, she's very creative with this to the point of being a Gifted Unicorn. For reference, most Gifted Ponies have Special Talents like command time or incredible lightning abilities. Lemon Hearts is on par with them simply by being exceptionally creative with it. There's a reason the Royal Guard is wary of her.
Lemon Hearts: *While chasing a pair of Changelings down with a stallion sized, monstrous lemon based Familiar* "HOW'S THAT FOR AN ANGRY MOTHER LEMON?!"
Brittany in Blue Belle has Prehensile Hair that she can also change the length, color, and style of. She stakes an entire church full of vampires by using her hair to fling pencils at them, acting like a human minigun. Buffy decides to check her email while she waits for Brittany to finish.
Sky High: Sidekicks were often underestimated in their usefulness. The viewer is initially led to believe Ethan's ability to turn into a puddle is useless. Near the end of the film, however, it turns out to be absolutely perfect for setting up a sneak attack. Lash never saw Ethan coming. Also note that, right before he takes down Lash, Ethan demonstrates the ability to flow vertically, something he'd never done before then.
Also worth noting that the coach who decides whether you're a hero or a sidekick doesn't really think about his decisions and just goes with his first impressions, much like a comic book reader who looks to what seems cool rather than useful. For instance, the kid who can turn into a rock monster immediately gets named a 'hero' despite being incredibly clumsy and useless.
In Star Trek (2009), Kirk asks Sulu about his combat experience while en-route to fight hostile Romulans. "Fencing", he replies simply. Once the fight begins, Sulu produces a sci-fi collapsible sword which he promptly puts to use. The "fencing" line and ensuing scene was a Shout-Out to Star Trek: The Original Series with the infamous Sulu shirtless fencing scene from "The Naked Time".
In The Hobbit, Gandalf believes that the best way to keep the forces of darkness away is to embrace kindness in all its forms, no matter how small, and this is the primary reason why he believes in Bilbo so strongly. Which works at first sight only thematically, but per Gandalf's style ends up working pragmatically, too. Bilbo's niceness, lack of ambition and general rustic charm makes him a sort of Morality Pet of the Dwarfs, generally acting as a voice of reason and an inhibitor of conflict. Moreover, it makes him mostly resistant to the lure of the Ring, which is important in the sequel.
The main characters of Mur Lafferty's superhero novel Playing for Keeps are examples of this trope. In particular, the heroine, Keepsie, has the superpower that nothing that belongs to her can be taken from her without her consent. Anyone who tries is paralyzed until they stop trying. In the beginning of the novel, she considers this to be a pretty useless power. Once she figures out that her life counts as something which belongs to her...
"You're breathing my air."
In the Rose of the Prophet trilogy by Margaret Weis and Tracy Hickman, a wizard named Mathew teaches several women how to cast a simple spell that will create a fog to help them escape from their enemies. The chief component of spell is water and Mathew has never cast the spell in a desert environment. While they provided all the water they could for the spell to work, it was not enough. What they didn't know is when the spell is not given enough water, it will seek out water from any source possible. And what is the human body mostly made of?
Door from Neverwhere can... open doors. Thing is, it doesn't matter where the door goes or whether there was even a door there to begin with. The Big Bad is treating her as a Living MacGuffin to get her to open a door to Heaven; she opens a new door on top of the existing door and throws him into outer space instead. Wow. Not to mention that early in the story she is driven into a corner by her pursuers, and literally opens one of them. Yes, she can open doors in human bodies - the results are messy.
The powers in Brandon Sanderson's Alcatraz Series are all examples of this trope. The main character, Alcatraz has the gift of breaking whatever he touches, which he uses to break himself out of prison later. His grandpa has the power to arrive late to things, which comes in handy when he is being tortured and decides to arrive late to the pain! One of the other characters has the ability to say complete nonsense, which allows him to become unable to give away their secrets to the bad guys. Later Talents include bad dancing that doubles as kung fu fighting and "getting lost" from a flying glass dragon to the ground. The Oculator lenses also apply. There are lenses that let you shoot wind, fireballs, inflict terrible pain... and the one that is the most powerful is a pair of glasses that lets you understand, read, and write any language. Knowledge is power.
In his short story "Leaks", David Langford mentions a wild talent with the ability to incinerate individual grains of dust—but only one at a time. He gets the idea of focusing his power on gunpowder, and goes on to have an illustrious superhero career as Mr Misfire, the Man Whose Adversaries Shoot Themselves in the Foot.
Similarly, the main character is targeted by a Mad Scientist who wants to see if his power can be expanded to other fields. The main character's power? Teleporting a pint of beer between glasses. Can it be expanded? Yes, to include any liquid in any container. Including, to the mad scientist's chagrin, the blood in the human circulatory system. The scientist similarly theorises the narrator could teleport an airborne plane's fuel away. By the end of the story the main character even moves beyond liquids, teleporting money out of the pocket of his superiors.
From the same short story anthology, Temps, the heroine of Roz Kaveney's Totally Trashed can materialize random pieces of litter and garbage out of the air. She stops a homicidal robot by covering it in old stamps and scraps of paper that block its sensors, and discovers that when she really gets mad, she can drop an old, rusted-out car on top of someone. Later she finds out that many of the things she materializes are actually potentially valuable antiques from alternate universes or timelines, such as a tarnished, slightly dented Roman centurion's shield that looks at first glance like a garbage can lid and old newspapers from other timelines that prove the existence of alternate universes, drawing in the interest of Nobel Prize-hungry researchers.
Almost the entire anthology (and its sequel, Euro Temps) is like this. While there is the occasional temp who can make the Gods jealous with their powers, most have abilities of this sort, such as the woman who Always Knows Exactly Where David Earnshaw Is - as long as he's in Britain.
Don't let his power fool you, Briar Moss' power is that of plants. You might think he can only grow plants, but in Tris' Book he works with his three foster sisters to create killer thorns to protect the temple he's currently calling home. And in Street Magic of The Circle Opens, he's considered a full mage, and on his own he uses a basket woven from dried reeds to bind up and hold down a girl he was trying to convince she needed magic training, use roses to torture an allergic gang member for information, and in the climax uses plants to tear down walls, cause wooden doors to come alive as if they're still trees, and use a thorny vine to kill someone by having the thorn grow THROUGH someone trying to come up behind him.
Lady Sandrilene Fa Toren will tie you up with your own clothing (or anything else that could be considered "thread" or "woven material") if you try to defy her. Thread is an awesome power.
Sandry: The cloth-head tied [my bonds] with ribbons. I suppose it didn't occur to him ribbons are made of cloth. * later* Sandry: (her captors are now encased and gagged in woven cocoons, the ringleader tied to a rock in a similar predicament) You can tell all Namorn this is what happens when I'm vexed.
Even more impressive is in the first book, where she uses sympathetic magic involving thread to "tie" the four main characters' magics together. This is because her power doesn't just extend to physical thread, but also any magic that can be visualised as string, such as life and nothing.
In the first quartet, her consideration of escaping bonds by un-weaving knots leads to the idea of enacting a Defeat by Modesty.
In the Lioness Rampant quartet, Alanna uses thread magic to dump an arrogant male student on his ass when he starts deriding "women's work".
In Elana Frink's short story "The Los Angeles Women's Auxiliary Superhero League", the character Jane is initially embarrassed about her power of "niceness". It turns out that she can mesmerize people by speaking to them gently in a soothing tone of voice, to the point where she can stun would-be muggers and reduce a supervillain to a drooling vegetable. She later gets the superhero name of "Hypnotique" which she likes much better than "the nice one".
Useless abilities are referred to as "Spot On The Wall" powers, meaning that they do useless things like making a colored spot appear on a wall. One character literally has that power, but it turns out that she can use her power to make mosaic images with amazing detail. Furthermore, she doesn't even have to know what she's making in order to create it. In one scenario, she makes an image of an "operations manual" to a device in order to learn how to use the device.
Irene's power is to make plants grow faster, which is actually a pretty useful ability, but seemingly mundane. However, Xanth is full of a ridiculous number of plants with very specific powers, and armed with the right seeds Irene can produce virtually anything.
Lacuna has the power to change text, which growing up she primarily used for pranks. As an adult, however, she ran up against the mighty Com Pewter, a Reality Warper who can make anything in his vicinity happen...via text appearing on his screen.
The eponymous item in The Sword of Shannara Trilogy. Its power, which is hidden for much of the first book, is revealed to be telling the truth about whatever it touches. Lame, right? Well, no not quite. You see, the Sword not only tells the truth, it forces whomever its touching to accept that truth. This comes in pretty handy as the Big Bad is revealed to be Dead All Along and deeply in denial about it. Bye bye Brona. But that's only one use right? Wrong again. The Sword shows up in almost every book in the series, proving to be the perfect antidote to every Manipulative Bastard and Evil Mentor out there. It saves Par from being possessed by Rimmer Dall in The Heritage of Shannara by revealing the latter's lies, and sends the Ilse Witch into a Heel Realisation-induced Villainous BSOD after revealing the lies she has built her life on. So what's a lame power again?
It also has some more practical uses, such as being able to guide an airship through a maelstrom while flying blind.
Unfortunately, it's only as good as a normal sword in situations that would require a normal sword, such as being ambushed by ten bandits.
A side character in The Wheel of Time is a very weak Asha'man who can channel just a little of the One Power, and therefore stands miles below the main characters. Fortunately for him, he's very good at making Gateways, to the point that he can make Gateways hundreds of times better than he should have been able to. The character uses this with quite a bit of imagination, and due to the unique properties of gateways using his skill to cut an iron chain is the least of it. At one points, he opens a huge gateway on a battlefield with the other side inside a frikkin' volcano. He could hold his own in combat against the best, if not for the fact that an enemy channeler could just shield him.
Digger Downs can literally "sniff out" aces, but also happens to be a paparazzi who works in the magazine about aces.
Astronomer won Superpower Lottery, but employed a lot of aces and weak deuces and taught them to use their powers well. Did you know that if you can control nearby water, it also extends to bodily fluids?
Popinjay arguably takes the cake. He has a power to teleport... only other people, only if he can "shoot" them with a pretend gun (you know the gesture), and only to places he's been to numerous times and knows excellently. He works as a private eye and, amongst other things, teleports people to jail. However, later into the story we find out that the location from his recurring nightmare that makes him wet himself apparently works, too.
While the Nasuverse has a notable share of insanely durable or insanely powerful characters, there is at least one character per setting, who while having a power that sounds unspectacular, can produce results as equally ridiculous as their overpowered brethren. Consider:
Asagami Fujino: MildMannered High School student with the psychic power to... turn objects clockwise or counter clockwise? Uses? How about twisting victims into a bloody pulp by sight alone? Or reducing a whole suspension bridge into wreckage?
Kiritsugu from Fate/Zero has the dual origin of severing and binding, which translates to breaking things and then putting them back together, which causes irreparable degradation. That doesn't sound very useful, but when his target is a mage's Magic Circuits... well, there's a reason people call him the Magus Killer.
Billy Harrow, the main character of China Mieville's Kraken, is a hapless museum curator and preserver of dead animal specimens. In fact, it turns out that he is the saint of museum curators who preserve dead animal specimens. This isn't all that impressive, until he realizes that his preservationist powers extend to reanimating zombie lab specimens as his familiars, and briefly stopping time. He's also a pretty capable Barrier Warrior.
A Feruchemist can use the fictional metal Atium to alter his or her age, but because Feruchemy is an Equivalent Exchange based magic system, in order to become young they'd have to spend an equivalent amount of time old, and as such, this power is considered worthless except as a disguise. Enter the Lord Ruler, who can do both Feruchemy and its sister magic system, Allomancy. The interaction between the two powers lets him, among other things, exponentially increase the output of his Feruchemy, so that instead of a worthless power, in his hands Atium gives him what amounts to a closed loop of infinite youth- thereby making him functionally immortal.
Tin, as an Allomantic metal, allows one to have Super Senses, but if you're just a Misting instead of a Mistborn (who is able to use all Allomantic powers) it essentially turns you into just a human watchdog. Useful, but not as devastatingly powerful as the powers granted by other metals like pewter, iron, or steel. Spook, who can only use tin, is Overshadowed by Awesome thanks to his comrades' far greater physical combat abilities. Then in Hero Of Ages he eventually burns so much tin he effectively becomes a "tin savant" whose senses are so intensely acute that he can locate enemies based solely on the sounds of their heartbeats and outmaneuver opponents by sensing what they're doing based on tiny things like the rustle of their feet on the ground.
In The Alloy of Law Marasi is a Cadmium Misting, a person who can burn Cadmium to create a bubble of slowed time; time moves slower within the bubble than outside it. Initially this power is considered rather useless, as there aren't many times that someone needs to move slower than the rest of the world. However during the final battle with Miles, she uses this ability once all of Miles's mooks are gone and he's distracted. Since Miles doesn't notice the bubble has gone up, this allows plenty of time for Wayne to get the police and come back to Miles's hideout, where they capture him.
He-Man villain Stinkor wasn't allowed to appear on the cartoon itself because his power of smelling really horrible was deemed too ridiculous. However, a supplemental book for the rejected Stinkor episode was in fact published. Turns out that Stinkor smelled so bad it sapped even He-Man's strength and Stinkor came closer to beating He-Man than almost any of Skeletor's other servants.
The Hunger Games: Peeta was a cake decorator at his family's bakery, which doesn't seem like it would be too useful in a death match. Until, that is, he uses it to paint his body to camouflage himself by the river. In Catching Fire, Katniss notes that out of all the Hunger Games' victors Peeta is the one who is best suited to lead a rebellion, because whereas the others are all deadly fighters he possesses enough goodness to make people rally around him through the things he says.
John Taylor of Night Side has a gift that lets him find anything. Big deal, right? Well, with only the slightest application of learned magic, he can find the activating key to any spell, holes in dimensions, the one pin on the grenade-belt that's looser than the others, the glass jaw of an eldritch abomination, etc. He calls taking bullets out of opponents' guns his "party trick", and routinely counters villains' threats with offers to do likewise to their favorite internal organs.
Harry Potter: The Power of Love is so potent that Lily sacrificing herself to save her son Harry allowed him to survive the killing curse that gave him his scar, and when Harry dies (sort of) to save the inhabitants of Hogwarts they became incredibly resistant to anything the Death Eaters can throw at them.
Neville is considered a loser by most of Hogwarts, with his only redeeming feature being Herbology. He weaponizes it, hurling screaming plants that cause instant death off the walls of the castle and carting around man-eating tentacle plants, during the Battle of Hogwarts. He potentially killed the entire first wave of Death Eaters by throwing a potted plant at them.
For the Deathly Hallows Dumbledore regards the Invisibility Cloak as this. The Elder Wand is powerful but also makes you a target. The Resurrection Stone doesn't really solve anything and in the end makes you long for something that can never be. On the surface the Invisibility Cloak is the weakest of the three since experienced wizards have other ways of turning invisible but as Dumbledore points out it's the only one of the Hallows that lets you protect someone else.
In the Book of Swords series, Woundhealer destroys Shieldbreaker (the most powerful Sword) and saves the hero's life when Woundhealer is stabbed into his heart. The narrative explains this by stating that there is no power greater than the human heart.
In the third trilogy of the Kushiel's Legacy series, a group of occultists manage to summon up a demon from the Ars Goetia, who teaches them the language of ants. This is treated as a prank on the demon's part and a useless nuisance until one of the occultists learns to "speak" the language of ants and command them. And moves into the Amazon jungle, which is chock-full of soldier ant colonies. At that point it stops being a nuisance, takes a flying leap past Awesome and lands right in a lake of terror.
In Percy Jackson and the Olympians, the children of Demeter can make plants grow. This comes in handy in the Battle of Manhattan, where Percy has them use their powers to block up the Brooklyn Battery Tunnel against an invading army of monsters.
Bethany is a young girl who happens to be really, really good at math. Supernaturally good, almost. As it turns out, this actually gives her amazing divination abilities ( and it runs in the family).
The butler Jam Crinklecut has the magical ability to "always be ready to serve." As it turns out, if you get stuck in the wilderness, having a guy who can summon food from nowhere is extremely useful.
Molly Carpenter lives in a setting where most problems are solved with super strength, giant fireballs, or hordes of undead servants. Her forte? Illusions. At first, it's mostly good for hiding from a fight. As time goes on she learns to dazzle and disorient enemies with a "one woman rave" of light and sound, and eventually to create convincing duplicates of herself to draw enemy fire wherever she wants it. Now that the 14th book has had her forced into the position of a lesser queen of the fae, who are physically incapable of lying, she could wreak havoc with the entire supernatural power structure by speaking only through lying illusions of herself.
Boots from The Underland Chronicles, The Chosen One of the cockroaches. Given how valuable the cockroaches prove time and time again in the series, having them completely on your side is an awesome power indeed.
In Relativity, the villain "Cricket" has the power to talk to insects (and spiders), which doesn't sound very useful... but, he does manage to talk some tarantulas into stealing some small but valuable items for him.
Tasha from The Gods Of Dream sneaks into Eloria as a talking mouse. She then proceeds to be incredibly useful several times, from chewing through ropes to incapacitate an Incubus, wielding a ring around her neck to purify Loor's influence, and acting as a lookout to help them get through a maze. She even ends up laying the finishing blow on the Big Bad, because she's the only one who can get close enough.
One of the Objects is a pair of Scissors which can rotate things around a point, which seems pretty limited until you realize that the center of rotation can be outside of what's being rotated. The protagonist finds out the hard way when the previous owner uses the scissors to toss him around the room like a ragdoll.
The Clock, which can sublimate manganese. How often does one encounter manganese, you ask? Turns out it's a component of quite a few alloys, including brass and stainless steel.
The Glasses inhibit combustion of whatever you're looking at when you wear them. One cruel Seeker tests them by putting a gun to a claimant's forehead and pulling the trigger a few times. Nothing happens.
On one episode of Misfits we see a guy who can control milk and dairy products telekinetically. No one takes him seriously until he realizes that he can force dairy food around people's bodies and more specifically, back up people's digestive tract to choke them with it. The episode ends with a Kill 'em All situation.
While hardly lacking for decent superpowers, Buffy herself also learns to use other skills in hunting vampires. Her keen fashion sense, for instance, comes in handy for spotting vampires, who frequently wear dated garb.
Kamen Rider Fourze, with 40 different pieces of equipment, was bound to run into this. In one episode Kengo is ready to write off the Hopping Switch as useless, but Gentaro insists that everything has its use; he demonstrates this by dodging the chameleon Monster of the Week's tongue (and again in The Movie, where he uses it to pinball-kick about a dozen Mooks). Later on, even Gentaro thinks the Pen Switch is a wash, but his friends discover that its ink hardens into a metallic substance, meaning it can make instant barriers (and in that specific case, block the monster's Medusa power).
One Pulp Sport sketch has a superhero whose power is to be able to find the start of a roll of sellotape in an instant. This proves incredibly useful.
Once Upon a Time: Emma is the product of True Love, so she seems to be magical by default, being a Living Lie Detector and apparently being able to jumpstart Regina's magic just by touch. In general, however, her "magic" seems to just be the fact that she is a physical form of The Power of Love. This suddenly becomes useful when it's revealed that love allows her to No Sell Cora trying to rip her heart out. More generally, True Love's Kiss is capable of negating any curse, no matter how powerful.
The main characters are all mostly depowered gods. Some of the powers like Anders' Compelling Voice or Olaf's eternal youth are obviously awesome, but then you've got people like Michele's mother—as the goddess of parties, she decides to throw a party, and everyone instantly knows there is a party (including whatever theme she feels like including), and they will be there.
Mike is Ullr, god of games, and doesn't lose games under any circumstances. Pretty cool on its own, but absolutely awesome when you keep in mind that a game is anything two people agree is a game. He's invincible in a fight if his opponent makes the mistake of betting on it, and he can even read minds if someone asks him "Guess what?" After all, it's a guessing game.
Johann, father of the Johnson brothers, is Njord, god of the sea. His power is to "spread oil on troubled waters," to calm arguments. Sounds like a weaker version of Anders' Compelling Voice? Maybe, but it means he never has to face any consequences for all the shit he pulls, since no one can stay mad at him.
Olaf: And yes, he also calms the actual sea. Great if you're a sailor, not so great if you're trying to take your twenty-one year-old out surfing.
The player characters are Physical Gods. Mortal NPCs often have enormous magical, supertech, and quasi-miraculous powers - and remain completely unable to challenge them, except extremely indirectly. What is the one class of mortals these Sovereigns of Creation must be cautious of? Botanists. It turns out the Angels used a language of flowers to define reality. Clever botanists can write their own addenda.
The Nobles (player characters) themselves can be like this, if the player wants them to be. There's an example of a Noble who is the Power of... Blankets. All blankets. Everywhere. And he can see through them from any distance. And he can make them do anything he wants, also from any distance. Including suffocating you while you sleep in them.He is one of the most feared Nobles in the setting.
Power of various emotional Estates are ridiculously powerful when combined with the animistic nature of Mythic Reality, especially in 1st & 2nd edition. Suddenly, gravity doesn't like you anymore.
In 3rd edition, Persona miracles can let you affect the "borders" or your Estate, making things more or less like the important traits of it. For example, if the Power of Hope states that one of the properties of Hope is that "Hope springs eternal," then Persona miracles of Hope can make things immortal.
When compared to its siblings, the Powers of Chaos and Strife, being the Power of Borders seems underpowered. Then the Estate is explained as having the power over all boundaries, including metaphysical ones (like class boundaries), and tells of how the current Power plunged Cincinnati (whose Mayor offended it) into anarchy for forty days by erasing the distinction between citizen and criminal.
Realms also has Gayrlana "Lady Bloodsword". A female human fighter with topped out Charisma (traditional Dump Stat) and Mindlink (ability to talk directly by telepathy) as a psionic wild talent. The result: a great Lethal Joke Character, because she used these advantages, and what started as a simple dungeon crawl ended with her as the head of Mindulgulph Mercenary Company, a mercenary company comprised predominantly of Monster Adventurers,note it's also in available for download "Gold & Glory" sourcebook, and continued along this line. Whatever the task is, they probably have creatures well-suited to it and humans or humanoids who can play intermediary.
Also, one article in Dragon Magazine told the story of a heroic and legendary wizard who, after his death, was revealed to have been a diviner. The article also included his spellbook, which included new Divination spells that were very useful.
Illusion, similarly, is often perceived as being far weaker than it really is. Even a low level illusionist has the ability to create voices in crowds, pass unreadable messages, and make documents that appear to be whatever paperwork is required, which while not spectacular in straight combat are ideal for running a rebellion. At higher levels, incredibly complex illusions can be created with a nigh-impossible saving throw, or can manipulate foes by substituting their reality for another or causing them to die of a fright-induced heart attack. (Your Mind Makes It Real applies in all these situations.) The problem here that something like 2/3rds of the creatures in the game are flatly immune to 90% of the spells in the illusion school, which forces it into What Kind of Lame Power Is Heart, Anyway? by way of balance-intended neutering.
Enchantment has the same issue exact same issue as Illusion; it is perceived as weak because the writers, recognizing the Game Breaker potential of the school, have gone out of their way to neuter its effectiveness, thus making it almost worthless.
In the Elfslayer Chronicles, a game of D&D played over an IRC chat, an illusion-mage PC decided to go Off the Railsin response to the DM's blatant elf fangirlism (with her being a Yaoi Fangirl adding fuel to the flames) and start strategically murdering elves, using his illusion magic to give himself alibis and incriminate those who he wanted to.
In 4E, there's Warlords (and to a lesser extent, Bards) with the "grant free melee/ranged basic attack" gimmick. Hey, cool, a free attack that usually has a lower to-hit chance than most abilities, and one roll of weapon damage instead of potentially between 2-5 rolls plus all sorts of bonuses. Nice, not great. And then Essentials came out, whose character classes usually base around turning someone who only has melee/ranged basic attacks into a Person of Mass Destruction suddenly lashing out three times their equivalents' damage per attack, and a Warlord that could grant an extra attack or three just cut the average encounter time in half. Some classes also have powers that can be used as a basic attack.
The (very) old Marvel Comics Superhero RPG had numerous weak powers that could be awesome in the hands of a creative player. Best example is Coloration, the ability to change the color, transparency and refractive index of matter. Seems very lame, until you're fighting vampires in the sewers and you turn the ground above, not just transparent, but into a giant magnifying glass.
Beginner characters, being limited to a couple Spheres at rank 1 or 2, seem sucky. Until you realize that, for instance, the first rank in the Mind Sphere already gives you the ability to boost your cognitive abilities to superhuman levels (total eidetic memory, information processing, etc) and psychic Aura Vision. Being that the whole game is pretty much about thinking outside the box, players and Storytellers are encouraged for take lateral thinking.
As a warning for potential game masters out there: Don't let someone who understands basic chemistry or physics to take Matter or Forces unless you are prepared to deal with the consequences of, say, mud in a swamp being transmuted into sodium metal under your bad guy. If you don't know the consequences of that statement... just don't let your chemistry geek friend take Matter.
Any sphere can become this with enough imagination. Spirit becomes a taxi service to the Umbra, Life allows for crazy healing, helpful player mutations and terrifying Body Horror on their enemies, Entropy brings the failing of every mechanism not working in the player's favor... the list goes on.
Entropy Level 1: Sense Fate And Fortune. Go to casino. Play slot machines. Wipe your ass with $100 bills.
The truly nasty spells come from two-sphere combinations, since you can do those with a starting character and the addition of the second influence can rapidly strip essentially every limit from the core spell. Consider: low-level spirit allows you to talk to spirits and convert some of your power into spirit-compatible bribes. Nice enough, but even though you can talk to basically everything that you can imagine having a personality (rocks, computers, your schedule) the power levels of the spirits rapidly outpace your ability to bargain meaningfully, much less bribe them. Add in prime, which allows you to juggle your power reserve and extract power from other things, and you're bribing the spirit of the entire city to assassinate a rival you don't like, and things of similar scale. With about three levels in each sphere.
In both Shadowrun and Cyberpunk, one of the weapons characters can have access to is basically high-tech squirt guns. Yes, squirt guns. Think this is super lame? Well, fill them with high-potency acid or lethal skin-absorbed poison and shoot it at someone's face, see how lame that is. Noah "Spoony" Antwilermade a whole video on them.
Reimu has the power to fly, something which nearly everyone else in Gensoukyou also possesses along with their other powers. Except Reimu can also fly away from reality, making her completely untouchable for as long as she wishes.
Kogasa is an umbrella youkai with the power of "surprising humans", and she's not even very good at it. She appears as a Stage 2 midboss and boss, and that appears to be the height of her power. Until she suddenly appears as the mid-boss of the EX-Stage, complete with the massive power boost that suggests! What a surprise for us!
Besides being an ancient wizard-messiah with all the power that entails, Toyosatomimi no Miko has the power to listen to ten voices at the same time... which includes the "ten desires" that make up the human psyche, meaning she can effectively read minds (or at least, personalities). This ability also causes divine spirits (physical manifestions of human desire) to be drawn to her, which she can absorb for power in a small-scale version of Gods Need Prayer Badly.
Raiko Horikawa can ride any rhythm, which sounds really abstract and useless... until she starts shooting lightning everywhere. Then you realize that in classic Touhou fashion she found her way into the repertoire of another rather notable drummer and suddenly her Ex-boss status makes rather more sense.
The Cobras in Metal Gear Solid 3: Snake Eater. The Pain, controls bees and is effectively a living bee hive. This makes him pretty much unstoppable on an open battlefield, stinging to death any soldiers without having to even be present himself. The Sorrow, is kind of a spirit Aquaman. He didn't just talk to spirits, he could pull battle plans, orders and other assorted information regarding the opposition from ANY dead soldier (which, you know, there can be a lot of in a war).
Knights of the Old Republic II turns the leadership "power" up to eleven. While the two main characters were still badasses in their own right, it's explained through backstory that Revan converted his entire legion of doom with this ability. The player character of the second game is an extreme example, by using the same ability to grow stronger with everything he or she kills without even knowing it, and at higher levels could devour all life in the universe, in a bizarre deconstruction of Experience Points.
Tsukihime: Tohno Akiha's power is called Plunder, which gives her the ability to "steal body heat." ...Well, okay, but we have people that can kill absolutely anything and others that can drop the moon on you, so what? How about being able to steal all of it from a person at any range, with such sudden violence that the target will instantly and functionally Self Combust? note They're not actually catching on fire or anything, it just feels like it to them because they can't understand what she's doing.
Shirou in Fate/stay night is so limited in magic that he can only use two kinds; Reinforcement and Projection. Neither seems very useful because Reinforcement just strengthens existing objects and Projection can only create an object for a very limited amount of time. In Shirou's case however he can use Projection to recreate any weapon he's seen before as a slightly weaker copy. Shirou is surrounded by heroes and demigods who use absurdly powerful weapons. By the end of the three routes Shirou is... rather dangerous for a human.
In City of Heroes, one of the least-played Controller powersets is Mind Control; it has a bad rep due to not having a summonable combat pet as a tier 9 ability (among other issues). This would seem to give it "What Kind of Lame Power Is Heart, Anyway?" status, except for the minority of players who use this set, who can attest to just how powerful it is in capable hands. As an aside, Mind Control is actually one of the more popular sets for Dominators (who have control and ranged damage abilities, as opposed to buffing/debuffing abilities like Controllers). M.C.'s ability to hold, sleep, and confuse multiple targets while your character is in "Domination" mode makes the immobilization powers found in other sets seem obsolete.
Monkey Island's Guybrush Threepwood can famously hold his breath for 10 minutes. The Pirate Elders are, to say the least, less than impressed when Guybrush mentions this as his special skill. As it turns out, being able to hold your breath for 10 minutes is a DAMN useful skill for, y'know, A PIRATE.
The Realm has 5 schools of magic, magic costs mana crystals which cost gold. One of the circles is Enchantment, which lets you permanently enchant items. But enchantment has no innate spells so its entire circle requires you to have learned other circles, but with enough ability, you can enchant any spell to any item.
Epic Mickey uses the thinner and paint mechanic to mean destroy/kill or repair/love. There is not actually any change in battle style, except the first will remove the tank from the fight, and the second will make him fight for you. Many have also pointed out the Fridge Horror in that mind control is the far darker option.
In the Super Smash Bros. series, a number of characters have moves fitting the description of this trope. However, an extreme example of this is seen in the case of Jigglypuff, whose "Down-B" special attack is "Rest". This doesn't even heal the character, as it does within Jigglypuff's origin franchise. However, if used with perfect timing, it can cause a two-hit KO; KO's at 30 damage, and it DOES 30 damage. Until they Nerfed it in Brawl. Even then, it can KO in 3-4 hits against an opponent with full health, as well as inflicting a health-draining status (which helps reach the KO-mark).
In World of Warcraft, engineering was largely considered a joke profession, most of its unreliable gadgets being watered down versions of better spells. Then the Burning Crusade expansion was released, and some smartass figured out that you could use the otherwise pointless Gnomish Remote Control to take command of the unstoppable Fel Reaver. Hilarity Ensued.
Gau has a lot of powers, but most players don't know how to use themnote especially since he's uncontrollable, but all he does is attack or do one technique, and lament that he is uncontrollable. He also gets to attack for quadruple damage by using the rage of a lost housecat. Or he can use flowers to turn your enemies against each other with his charm ability. And he can use a jellyfish for the one time in the first half of the game that pussycats can't kick ass. Even without the well known over powered strategies like "Wind God Gau", any observant player can see Gau use magic for free and earlier than normal.
The same game also has Relm. She can draw pictures. Completely useless, right? Except that with it, she can single-handedly defeat the most powerful cephalopod in the game.
The Top Spin in Mega Man 3 is frequently regarded as the absolute worst weapon in the series. Once you figure out how to use it, however, it is one of the most powerful weapons in the game. It will one-shot any enemy who's not outright immune to it, with the exception of most bosses. Even some bosses are destroyed in one shot by it, including the final boss! The only thing you have to keep in mind is which enemies are immune to it, and the fact that the weapon drains energy for as long as you're in contact with the enemy, so it can empty out its energy very quickly if you're not careful.
Various ghosts get various powers in Ghost Trick, but the one who takes the cake for this trope is Missile. He used "swap the location of two objects" to stop someone from being shot after the gun has fired.
In EarthBound, Paula's Pray ability produces unpredictable results throughout the game. However, it's absolutely essential to defeating the final boss, Giygas. When Paula prays during the final battle, it deals five digit damage to Giygas and eventually kills him.
In this one, a villain called the Existentialist, whose powers aren't exactly defined but are sort of implied to consist of knowing existential philosophy, comes up with a plan to beat the invulnerable Regenerating Man by causing him existential angst and confusion.
Junpei claims that Magical Girls follow a "code of love", which is much more dangerous than the ninjas' code of honor, and results in "much destruction of urban area". Magical girls have been shown to possess super speed, super strength, super athleticism (balance and so forth), teleportation, and shrinking, and the closest thing this setting has to a Big Bad is an immortal Really 700 Years OldDark Magical Girl who controls hordes of zombies and destroyed one of the most complicated MMORPGs in existence when she was bored.
Three so-far-unnamed Magical Girls are "potentially catastrophic grade." The zombie master mentioned above is not one of them.
While we are on the subject of Magical Girls, let's talk abut Yuki Sonoda. Her power is stealing things. Even she doesn't think her power is any good, but at one point she steals a Rent-a-Zilla. And it seems to obey her perfectly.
Tavros' seemingly-useless ability to commune with Alternian fauna? It works on the hordes of imps, ogres, and other such beasties that appear in Sburb, allowing him to amass an entire army of imps, ogres, and basilisks before even hitting his second gate. Suddenly, the guy who was mercilessly taunted for being a useless cripple before, is now probably the most powerful player in the session. Said powers of communion extend to Becquerel, Earth's First Guardian and Reality Warper extraordinaire.
John's friends eventually have the power to teleport, jump around in time, and manipulate luck. John just has wind powers. And he ends up stronger than all of them put together simply because he was tricked by Vriska into a permanent Super Mode.
Vriska's mind control has kind of a weird power curve: it starts off very useful but becomes somewhat pointless when the game starts (there are 12 trolls around, some with resistance/immunity to boot, and like 50 million enemies? Awesome.). By the end of the session these 12 trolls have become absurdly powerful. And that's nothing compared to all the ghosts of every dead Beta version.
So it turns out that Gamzee can amplify people's fears and give them terrible nightmares. He uses this to doom the kids' universe.note Because of said nightmares, John draws harlequins all over his bedroom, leading Dad to collect them because he thinks it will bring him closer to John, so John ends up prototyping one that he got for his birthday, so all the Dersites wear harlequin clothing, which makes Jack Noir kill the Black Queen, become absurdly powerful, and set out to destroy everything in his path.
The fanbase assumed that Heroes of Heart were simply good at managing relationships. Eventually, we discover that in Sburb mechanics, Heart is roughly synonymous with Soul. To hammer it in even further, this is revealed to a character known as the "Prince of Heart", along with the fact that "Prince" is a "destroyer" class. In other words, he's the "Destroyer of Souls. He later proceeds to demonstrate his abilities by ripping Aranea's soul out of her body.
Jade, with her friends flying around, creating super tornados, time traveling and using other amazing magic powers, was unsure of how useful her potential "space" powers were going to be when she would inherit them. Later she is shrinking planets to the size of baseballs, teleporting meteors, and accelerating battleships to the speed of light. (Though while changing the size of objects is certainly a power of the Space aspect, it is unclear whether teleportation and telekinesis are part of her Space powers or due to her merging with Becquerel and gaining his dog-like powers.)
Biscuit of the Felt's "power" is to hide in a broken oven, mistakenly believing it's a Time Machine. Much later, it's revealed that said oven's true power is that it's Bigger on the Inside. The entire Felt gang can be transported inside it.
In Sidekick Girl, Illumina's powers are floating a few feet off the ground and creating light, powers that she has no idea how to use practically and uses them just to show off. Then she and Sidekick Girl have a "Freaky Friday" Flip, and the much smarter Sidekick Girl shows what these powers can really do, discovering that Illumina can fall safely from any height — she slows into a hover rather than suddenly stopping by hitting the ground. As for light? Well, extremely high-intensity but otherwise normal light is the "flash" part of flash-bang grenades. Intense enough light can stun, as well as temporarily or even permanently blinding.
This tends to be the case on this forum. One person got the power of being Arthur Wellsley, the first Duke of Wellington. Another got heat resistance and super reflexes, allowing him to be an awesome fire fighter. One even got Circadian control (the ability to manipulate daylight hours by rotating the planet faster or slower), money ray (the power to create any currency), and water generation (the ability to secrete purified water from any part of the body) How do you do anything with these useless powers? One person saidnote By being able to effect the earth's rotation you can manipulate sea level across the globe (since the earth's rotation causes the earth to currently bulge out a little more at the Equator). Slowing this would cause planet-wide floods. You could level civilizations at a whim. You'd be unaffected yourself by groundwater tainting due to your water generation powers and could provide any area you wished with relief and/or immunity to the world-wide sea level crisis. Your ability to create currency means that while all other nations take a huge financial dip from dealing with rebuilding their ruined cities you can further undermine their currency through generating lots of extra cash in their denominations. Establish your own nation in the wreckage of civilization, declare your currency to be solid gold coins and produce the heck out of them making wherever you choose to rule the world's newest financial superpower.
Ashlie Jackson of Survival of the Fittest: Evolution. Her power was that if she spoke, her speech would come off as Sensory Abuse rather than anything intelligible, causing the listener to feel ill. How does she use this? She uses it as a distraction so that she can get a bullet into someone while they're down.
El Goonish Shive: Elliot gains the ability to turn into a girl by messing around with experimental alien technology and an ancient magical artifact (in that order). Elliot's a fairly well built martial artist, and his female form is a more idealized form, so the ability is really only useful for slipping out of ropes at first. It acts as the catalyst for turning him into an actual mage, and later allows him to shapeshift into any female body he imagines... around the time this new magical power forces him to transform on a regular basis, because of all the previous interference. He quickly grows to find the transformations inconvenient, creepy, and awkward. Then he gets a spell which, while it still keeps him female, turns him into a superhero with some Stock Superpowers, and three "Secret Identity" forms which gently alter his behavior so as to keep a low profile. It's kind of a mix between this and Magikarp Power.
In the Spanish-language A Friki's Life, an elf's gun of choice when surrounded by giant, carnivore ants: Invoking the flower fairies in their circulatory systems.
Done with a magical object rather than a person in Goblins: Life Through Their Eyes. Forgath has the Anymug, an enchanted mug capable of creating any non-magical drink the owner desires. Emphasis on drink — although it can produce disgusting things like urine that no person would willingly drink, it can't create anything that's outright too toxic or volatile to drink at all. When in a fight, he uses it to create an indefinite supply of Dragon Lung, an ultra-flammable liquid.
In Ow, my sanity, Dave has such good luck with Eldritch Abominations due to his inhumanly intense empathy. This tends to confuse and intrigue them enough for them not to eat his head and allows him to just roll with things that would cause others in the Cthulhu Mythos to go stark raving mad. Unfortunately, it's useless in interacting with other humans, as they use it as an excuse to treat him as a doormat.
Last Res0rt has a surprising number of these, justified by the Fantastic Racism of the Celeste (the Celeste maintain schools where they learn to use the full cadre of their abilities, whereas the rest — including Djinn and Light Children — only learn of their powers once they gain access to one or two abilities, and rarely advance beyond that.) The end result means that "progress" from one ability to the next implies a new use of the old power:
Jigsaw's abilities have slowly grown from merely being able to read thoughts (and communicate in kind) to the ability to generate illusions and turn invisible
Daisy's primary power appears to be her ability to 'bounce' (read: teleport), which she originally used to try and escape, but also works as a way to surprise foes from behind and above!
In Worm, the main character is a fifteen-year-old wannabe superhero in a world full of people with powers. While most famous heroes and villains have standard abilities like flight, strength, healing, speed, etc, she only has the power to control insects, spiders, and other invertebrates, but she's smart enough to come up with all sorts of creative ways to apply her powers. These include attacking with hundreds of black widow spiders and other venomous creatures, spying on people from two blocks away by looking through the eyes of insects, devising a bullet-resistant costume by commanding spiders to spin one from their silk, improvising a costume with carefully directed insects, which not only conceals her identity but intimidates her opponents with a giant, creepy-looking human-shaped swarm, arming even the nonstinging, nonvenomous insects (imagine being divebombed by bugs whose undersides are coated in capsaicin, who are guided to the mouths, nostrils, and eyes of an opponent), have her bugs vibrate to mimic speech, pull the pins on grenades, used spider threads as triplines or to cocoon people, and even kill a Nigh Invulnerable character by cramming insects down their throat so that they suffocate.
In We Are Our Avatars, Sawbuck's ability to teleport him and his attacker to any place in the space-time continuum is used by Snowman to kick Drakina out of her building.
Jack from Zebra Girl has power over all things plaid. That sounds ridiculous and useless until Jack points out that at a near-microscopic level, any fabric's weave is a form of plaid. He used this logic to trap Harold Duvase (an evil Harry PotterExpy) inside his own scarf and then banish him into another universe.
Gateway can only open 'gates' to other places. Then she finds out she can summon things from other dimensions through those gates. Starting with Rythax, an intelligent winged panther-like thing bigger than a tiger.
Verdant can secrete stuff. But she can secrete anything she can think of, through any of her glands. She can bite you and inject the deadliest poison known to exist.
In the Global Guardians PBEM Universe, Madras had telekinetic control over fabrics, and only fabrics. That sounds like an amazingly lame power, until you look around and realize just how much fabric surrounds us every day of our lives (and the fact that most people go around covered in the stuff). Combine telekinetic control of fabric with a ten-foot-long canvas cape, and you've got one Badass crimefighter.
Aquaman receives this trope in the animated medium as well, Justice League Unlimited and Batman: The Brave and the Bold in particular use it to great effect and make him one of the most powerful characters in each series, while in one episode of Super Friends he uses microscopic aquatic organisms to control water itself.
The Brave and the Bold Aquaman deserves special mention, because he's weak primarily because he's by far the most relentlessly positive do-gooder Gentleman Adventurer in even that universe and has very definite ideas about how a hero should behave, and most of those ideas involve faces and the punching thereof (not really where he shines, though he's reasonably strong). This leads to some rather hilarious sequences where he casually turns aside from a hopeless fist-fight to casually stop a large-scale natural disaster or weapon of mass destruction or other "non-heroic" issue in a matter of seconds, then returns to losing the fist-fight against the villain.
Don't forget his Rousing Song of Heroism from episode ten of Season Three, which he started by claiming that "...heart is the only superpower you need."
In the series premiere, Twilight Sparkle represents Magic in the Elements of Harmony, but she and her friends can't access the Elements' full potential until she learns the value of friendship.
Princess Celestia: I saw the signs of Nightmare Moon's return, and I knew it was you who had the magic inside to defeat her, but you could not unleash it until you let true friendship into your heart.
Amongst the unique abilities ponies possess, Fluttershy being the Friend to All Living Things is highly useful when nature needs to managed, but doesn't seem that impressive. Except her ability really does include all living things, including a manticore, a gigantic adult dragon, a cockatrice, Cerberus, and even the spirit of chaos himself, all of whom she managed to pacify with nothing but a firm tone and a kind touch (with occasional help from The Stare). Good thing she's the nice, shy little pegasus she is...
Earth ponies don't have any obvious magic, unlike the unicorns, who cast spells from their horns, and the pegasi, who walk on clouds, control weather, and have tactile telekinesis that allows them to tow things while in flight. According to Word of God, they're stronger and have more endurance than a unicorn or pegasus of comparable physique, and have a deeper connection with naturenote Making Fluttershy a bit of an odd duck, since she's a pegasus whose specialty lies within the earth pony domain and the earth. The latter means that they are inherently better at growing and tending plants of any kind, to the point where in ancient times earth ponies were the only pony tribe capable of producing food, and even in modern times only earth ponies have ever been seen growing anything. The greater strength of earth ponies is seen most notably in "Hearts and Hooves Day", where an earth pony schoolteacher is strong enough to break through two walls when properly motivated (and without Impact Silhouette, she just demolishes it), while one who is notably stronger than average is shown effortlessly towing a house. While skipping.
Pinkie's "party cannon" helps her set up parties super fast. As the changeling fight showed, it can also be used as an actual cannon.
A literal example with Princess Cadence, the third Alicorn in the series. She's a Love Goddess who has the power to repair the bonds between ponies and spread love wherever she goes. Compared to the Physical Goddess level abilities her aunts show, this doesn't seem as impressive. Then you remember that The Power of Friendship is the most powerful magic in the world, and The Power of Love is it's close cousin. Cue her and her husband-to-be using The Power of Loveto deliver a Heart Beat-Down to Queen Chrysalis that sends her and her minions hurtling over the horizon. We see in the Season 3 opener that her love magic is capable of repelling King Sombra, an ancient and extremely powerful unicorn king who took both her super powered aunts to defeat a thousand years ago.
The aforementioned Stinkor in He-Man and the Masters of the Universe (2002). In this cartoon, the character got the chance to show just how lame of a power stink really was, and He-Man was almost defeated. In later episodes, Skeletor treated Stinkor much better than his other minions, just because of how powerful he was. Stinkor's stench is so awful that Skeletor has to hold his nose around him. Skeletor doesn't have a nose. Another episode showed that his stench is strong enough to send dragons running for the hills, no easy feat. He proceeded to clear out basically every dragon in the country so Skeletor's plan could move forwards (the dragons were the natural predators of his latest weapons).
Some of Ben's alien forms. Most notably, Grey Matter's power is to be small and have high intelligence. Seems pretty weak, until you realize he can build a lasergun out of pieces of crap, destroy Killer Robots from the inside, hide in smaller spaces and outsmart most opponents. The Omnitrix, which give Ben his ability to turn into aliens, was created by a guy from Grey Matter's species (and he is considered to be the smartest sentient being in multiple GALAXIES).
In a now non-canon future episode, Ben's son Kenny used Grey Matter to hack the Omnitrix (being given his own as a birthday present) and turn off the power limiter, therefore giving him access to all alien forms currently available with no time limit between them.
We also see Grey Matter's ability proven in the Showdown two-parter of Omniverse. After trying to ever since getting the new Omnitrix, Ben finally re-unlocks Humungousaur, takes on the shapeshifting monster... and is quickly cocooned. He tries to switch to Way Big, gets Grey Matter instead... and realizes how to immediately take down the monstrosity with a precise nerve pinch.
Cannonbolt is an in-universe example. The first new alien Ben unlocks after the original ten, Ben laments that all the form can do is turn into a ball and roll around. It's only later in the episode that he finds out that when Cannonbolt picks up speed, his tough shell makes him more or less a free-rolling wrecking ball. Not only does Cannonbolt rip the planet-destroying Monster of the Week apart from the inside out, but he goes on to become one of Ben's more prominent alien forms. As seen in "Be Afraid of the Dark", Cannonbolt can survive atmospheric re-entry and the resulting crash into the earth while encircling two completely normal people (who are likewise unharmed).
In Sabrina: The Animated Series when everyone ends up inside Harvey's comic book world, Sabrina's power is to make squids shoot out of her hands. It seems pretty useless until Harvey's draw-it-and-it-comes-to-life pen gun runs out of ink and Sabrina remembers what squids are good for...
Schoolhouse Rock: Zero the Hero's power seems useless on surface, but at one section he alters any number by the power of 10 through adding (and presumably removing) zeroes. He also altered weights by adding zeroes, making it literally heavier. He created money by altering the $4 written on a piggy bank to a $4000, and there doesn't appear to be any limit to this. So he theoretically could change reality, simply by altering numbers. If there was box of "10 guns" he could grow it to a box of "1,000,000 guns" and supply an army. This also implies he could reduce anything to zero as well, causing it to vanish! He didn't do much in the video besides making some small-time money and making weights heavier, but he is still young yet... The video.
When Barack Obama was asked in an interview what superpower he'd like to have, he picked the ability to speak any language. This is a power from the Bible. In the New Testament, the 12 apostles of Christ were briefly given the ability to speak in any language, which led them to gain large numbers of followers. Have a translator pick between Flying Brick and omnilingual.