Well, Kal-El's
Double Your Pleasure mod is an absolute must. It really enriches the game drastically



It is finished, and it's present on PtW's CD. However, you may want to download it, since it has been updated, and because there are tons of new units there that aren't on the CD.
Some outstanding job is being done by the modding community, but is not completely finished yet.
BeBro is on the verge of finishing his
X-Com Mod
NeOmega has published a beta version of his
Pentagenesis mod.
And it seems the team working on a
Lord of the Rings mod is advancing well as well (I don't think they published anything for now).
Procifica has also done an ambitious
American Civil War scenario, which will likely be included in Conquests. I didn't try it yet, but it sounds like a great work and great quality.
But check out for BeBro's mod. The guys at CGN are betatesing it currently, so it should be out shortly
Edit: oh, and how could I forget
my mod. Obviously, it
has to be the best of all

(seriously, if you mod the game yourself, there are quite a few good ideas in there)