I tend to like different types of ice spells as they open up for good crowd control and for kiting single targets. In vanilla WoW I used to play as an AoE frost mage for CCing big groups. One of the most useful spells was the blizzard spell that rained ice shards on the enemies damaging and slowing them down. The funniest spell would have to be Scroll of Icarian Flight from Morrowind, which casts a spell that increases your acrobatics by 1000 for seven seconds, making you able to jump almost over the whole map.
My favorite is probably Celestial Host from Planescape Torment. A very spectacular spell. It's worth to note that most of the high level spells from this game are spectacular. Best visual effects from all D&D based games.
A second pick would probably be Time Stop from D&D games. Third one Puppet Master from Pillars of Eternity games. I can't imagine playing without it on higher difficulty levels.
The oldest of Skyrim traditions: Taking Lydia with you up to High Hrothgar and Fus Ro Dah-ing her ass off the mountain.
As for me, I've always preferred two types of spells: spells that messed with the senses and spells that conjure up minions to do my the work for me. Animating dead or summoning otherwoldly creatures and illusion effects that mess with sight and sound.
I really enjoy the spells in Spellbreak (upcoming mage-based battle royale) because you can combine them so enjoyable. So if you use a spell that makes a firewall, and use a spell that throws a boulder, you get a burning boulder. Lay down an ice trail and melt it with flame, then electrify the puddle. Summon a whirlwind, then use a poison spell and you've got a toxic tornado.
The effects are really lovely, too -- there are some gifs here and here. I just really enjoy when magic interacts with magic for double magic.
@DarkShine Clever use of game mechanics I admit to using it a couple of times, especially at the planar prison in BG2 and when I *cough* accidentally opened a sarcophagus only to be faced by some nasty, nasty demon.