I'm just scanning through all the spells and here are some I found very frequently asked and atleast very useful.
1. Spell Template (in JASS) if you want to make spells much easier -
http://www.wc3sear.ch/index.php?p=Spells&ID=109&sid=210fed1e34d49288b449008d5885f75b
2. Advanced Building, allows you to build advanced buildings like in wc2 -
http://www.wc3sear.ch/index.php?p=Spells&ID=76&sid=210fed1e34d49288b449008d5885f75b
3. Enrage, makes all units into an area attack random units -
http://www.wc3sear.ch/index.php?p=Spells&ID=224&sid=210fed1e34d49288b449008d5885f75b
4. Charge, requested multiple times, it's the closest to Diablo version -
http://www.wc3sear.ch/index.php?p=Spells&ID=281&sid=210fed1e34d49288b449008d5885f75b
5. Pushback Nova, a nova spell which pushes all the units around the caster back -
http://www.wc3sear.ch/index.php?p=Spells&ID=528&sid=210fed1e34d49288b449008d5885f75b
6. Tower Sell, very useful for most Defense maps -http://www.wc3sear.ch/index.php?p=Spells&ID=531&sid=210fed1e34d49288b449008d5885f75b
7. Temple Guardian, contains the Terrorifying skill which makes all the units around the caster flee in panic -
http://www.wc3sear.ch/index.php?p=Spells&ID=548&sid=210fed1e34d49288b449008d5885f75b
8. Reflect, this spell was also requested a lot of times. It reflects spells back to the original caster -
http://www.wc3sear.ch/index.php?p=Spells&ID=586&sid=210fed1e34d49288b449008d5885f75b
9. Drag, this spell drags an unit to the caster -
http://www.wc3sear.ch/index.php?p=Spells&ID=575&sid=210fed1e34d49288b449008d5885f75b