I can't remember what it was called, but it was in Golden Sun. Basically, a huge sword just flew down from the sky and did mass damage, it was pretty badass.
That's the Gaia Blade's unleash. I forget the exact name, but it doesn't hold a candle to the Sol Blade's Meggido.
And, now that I think of it, the ultimate Golden Sun: The Lost Age summon, Iris, is a pretty awesome spell. You can't even get it until you've almost beaten the game, but it summons a goddess to throw your enemies into the sun. What could be better?
Captcha: Scentc Velocities - the speed at which Iris throws your foes into the sun.
I love the Gravity spell from DA2 that slowed everything down.
Love the Sparks spell in Skyrim, makes me feel like a Sith.
Love the Blademasters spells from WC3 he was a bad ass, same as the Mountain Kings.
Vague - It's kind of hard to nail down. Um, it- it's not so much what it does, effects wise, so much as ... um.. yeah, I have no idea.
Can't remember the exact name, I think it was flay, but in Legacy of Kain there was a spell that made enemies explode into a pile of blood and gore, very satisfying, I loved using it against the bartender who wouldn't serve me another pint at the very start of the game.
Another two are Knights of the round from FF7 and Eden from FF8 (I know these are summons, cheating a bit here) not only because they are visually impressive and inflict MASSIVE DAMAGE, but it's also a handy chance to damage the enemy and also go make a cuppa whilst the extremely long animations are playing out, win win.
Infernal Wrath from the original Fable. Watching a bunch of guards run up to an eight foot guy in full dark plate and hit him to no avail, then a moment later watching them suspended in mid air with a bolt of energy going through them and hundreds of 10 damages appearing in the bottom left of the screen, always gave me a warm feeling inside.
Fuck that Divine Fury or whatever it was, stupid bloody light side people...
I would say Fungus Amongus, from the Mongrel class of Gladius. Silliest animation for a spell ever, and it wasnt half bad.
Then again, theres always Glyph of warding and Sanctuary. Speaking in terms of 3.5 and lower.
Fog Breath in Shin Megami Tensei: Nocturne... it's the best debuff spell I've ever come across. All enemies' agility and accuracy is reduced tremendously. I have enemies with normally high stats missing every attack. The only problem is, it costs a lot of MP.
Fireball in Skyrim - the bestest destruction spell yet. It can hit multiple enemies to deadly effect. Frost Trolls - gone in a few hits! Soldiers - obliterated!
Wouldn't you know it. The time I actually get asked something like this I can't remember any of the spells from my favorite games. Well out of the ones I can remember:
Ultima from FFVI: So overpowered. Hits all enemies all the time and hits for max damage even at lower levels. Combine that with the celestriad (which reduces all mp usage to one), the soul of thamasa (which lets you duel cast), and quick (which lets you take an extra 2 turns), and a little girl could take out 7 boss dragons at once in pretty much one turn.
Extension from Tales of Phantasia: This was just fun to spam. Sure it burned through tp like nobody's business, but it hit all enemies for massive damage and looked pretty cool.
Ice barrage from Runescape: An evil spell. Hits all spaces around the target, freezes them for 20 seconds, and does pretty good damage to boot. Of course I always seemed to be the one on the receiving end of it, but, hey, it was still cool (no pun intended).
The black planet pins from The World Ends With You: Not sure if this one counts, but whatever. The ultimate offensive set. All the pins do immense damage, have nasty side effects, and their uses can be chained for some pretty brutal combos. If Black Saturn were a better recovery pin it would be perfect. Admittedly they were pretty tough to get and don't do shit if you don't have them all, but "When six darklit planets align..."
All the Rex spells from Fire Emblem: Path of Radiance: In this case its really just cause they look cool. Yeah they were powerful and all that but by the time you had them so was absolutely everything else so it wasn't that big of a deal.
Jihad from Shin Megami Tensei: Strange Journey: Does massive almighty (non elemental) damage
to all opponents and lowers all their stats by one (which was a big deal in this game).