Skyrim offers a lot of spells as it is, but few of them feature interesting effects. Apocalypse - Magic of Skyrim provides a great pack of new spellcaster ammo.
The mod adds 155 new spells to the game - most of them are simple offensive spells, but you can also find several gems. Among the more interesting spells in the set, you'll find the option to create temporary objects (such as bridges, which helps when exploring caves), shapeshift, or conjure a phantom of your enemy that will attack them. Additionally, the mod enables you to bury your enemies alive, or even create portals that can be used for long-distance travel.
All new additions can be found across Skyrim - bought from vendors, court mages, or found randomly in various locations. The spells are properly balanced and designed to match the lore of the game.
The mod can be installed through Nexus Mod Manager.