Download your desired mod (or one you created yourself) from the library and place the mod within the mod folder in your GC2 install directory, typically in, C:\Program Files (x86)\Stardock Games\GalCiv2. Always be sure to checkout any .readme files.

GC2 allows for a great freedom of modding, as long as you have an .xml editor you can do a great many things your self. Download Notepad++ and open the desired xml's up in it, its a very user friendly app. Always be sure to back up the file your playing with tho.

Edit - Here is a useful modding guide that might get you started. It's an older guide, but has plenty of useful info in there for those new to modding.
A Brief Guide to Modding.