Paralyze/Mass Parlysis and Calm/Pacify/Harmony are such an awesome spells. It took me too many characters before I appreciate them.
Fear/Rout/Hysteria--useful if you can stay calm in a battle yourself--although admittedly I usually let Marcurio do the Fear casting.
Clairvoyance--helped me my way out of a Dwemer ruin more than few times
Muffle--I abuse that spell so much I had to make a no-magic character to force myself to not rely on it.
Magelight--damn, this vampire den is dark--would hate to miss any good loot. Lemme cast a few Magelight orbs and...ew, are those...eyeballs?!?
Transmute--GOOOOOOOOOOOOOLD!!!!!!!!! Get yerself a ton of iron ore, some high level Robes of Alternation and a Circlet of Major/Eminent Magicka and make yerself some GOOOOOOOOOOOOOOOLD!!!!!!!!!
Waterbreathing--if you're not an Argonian, this beats all other underwater alternatives. Unless you like carrying around potions or a piece of armor with an waterbreathing enchantment.
Detect Life--I see you, bitches. I'm coming to kill you. (granted Level 3 Aura Whisper is better than the spell, but more work to obtain)
Detect Dead--I see you, corpse bitches. I'm coming to loot you.
Telekinesis--fun spell, handy in creative ways. Pity you don't get to use it much in quests. You really should.