Throwing fireballs and lightning bolts from your hands in Skyrim is always good, but even the world's greatest magic show gets old after you've seen it a thousand times or so. It's time for gaming's greatest magicians, modders, to alter physics, conjure items out of thin air, and basically just screw with every system they can get their hands on to make Skyrim's mages the mystical powerhouses we all want to play. Once you've expanded both the repertoire of spells available and the power of the spells that already exist, magic is a much more viable and impressive option.
Imagine if this power fell into Snoop Dog's hands...
The most extensive magic mod out there right now is Midas Magic, which livens up the somewhat dull set of spells and scrolls without breaking the balance and turning you into an unkillable magic god. Fireballs and lightning are all well and good, but meteors, bug swarms, spectral dragons, giant magical laser beams? They're a little more exciting.
The beauty of Midas Magic is that these aren't just thrown into a chest somewhere for you to pick up and suddenly have an arsenal of sorcery at your hands. Instead, there are three Aurums spread around the world (at Riverwood, The Winterhold College, and Hillgrund's Tomb) which are essentially magic factories. Each comes with a unique recipe book, and to get your hands on the spells you need to scavenge the materials that each requires. Summoning meteors to fling from your fists, for instance, calls for a gold ingot, fire salts and a giant's toe. Roots rising out of the ground to entangle your enemies? That's a gold ingot, a Briar's heart and canis root.
Time to go all Nightcrawler on this thing.
And the materials can get rather tough to come by: to conjure a phantom sabre cat, for instance, you need the requisite gold ingot, but also a sabre cat pelt and a Staff of Fear. It creates a whole new subset of quests that you need to go after -- some of these spells require unique items that are quest rewards, which forces you to make the difficult decision of whether to keep that amazing staff, or swap it for a giant death beam. And just getting some of these items is an adventure in itself, requiring you to either consult a wiki or use your nous to track them down. For those of us who were a tad underwhelmed by the Mages' College quests, a new reason for the magicka-inclined to set out on adventures is more than welcome.
As a companion to Midas, I recommend the Truly Balanced Destruction Magic, which invigorates the Destruction skill tree's increasingly feeble attempts at keeping up with your enemies' growing health bars as you level up. It slaps some damage modifiers on each spell, scaling both with your general level and the level of your Destruction skill. It seems like a minor change, but it makes a huge difference in your viability as a straight mage.
You're like Rudolph, except it's your hand and you can't fly.
And to just make the entire game more exciting and fun, there's the Blink/Teleportation Spell, which I have used far more than anything in the Midas Mod because of how damned useful it is. You fire a projectile spell from your hand, and, providing it isn't out of range, you teleport to wherever it hits, instantly. It makes traversing mountains wonderfully liberating, and is hugely useful in fights when the melee guys with big, mage-cleaving axes get too close.
Finally, have you ever noticed how those flames and sparks that surround your hands when you've got a spell equipped don't shine like they should? The Spells Give Off Light mod fixes that, and now no mage will ever have to wander the halls of an ancient Nord's tomb in the dark again.