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).