Now before you get all flushed over the install process, relax. It will not requrie you go to through manually digging around changing this and that. Some mods even come with an auto installing executable, double-clicking is all you need. Some however, will require you to backup one file in particular.
Back Up The Default File patch_d2.mpq!
This is the most important part of installing any mod. There is a file called patch_d2.mpq that is located in your Diablo 2 install path. For the sake of simplicity, make a new folder in the Diablo 2 install path called "BackupMPQ". Then, simply copy the "patch_d2.mpq" file and paste it into the new folder.
Now, should anything go wrong all you should have to do is copy and paste the file from the backup file you created into the Diablo 2 install path. This will save you from having to re-install Diablo 2 should something go wrong.
After you have backed up, the install process for each mod should be as simple as moving the downloaded mpq file into your Diablo 2 install directory and renaming it patch_d2.mpq (unless it's already named that). Just be sure to not overwrite your default mpq file.