Fall from Heaven II
Fall from Heaven II turns your Civilization IV game into an entirely new fantasy-themed game. You get powerful spells, roaming undead, fire-flinging evil priests and many other trappings of the genre--all for free.
Gameplay varies greatly depending on which civilization you choose. Playing as the tree-tending Ljosalfar elves requires an entirely different civilization-building strategy from that of the nihilistic Sheaim, for example, and there are plenty of new civs to choose from. What's more, Fall from Heaven II sports a well-developed line of scenarios that both tells a story and helps introduce new players to game elements.
While great fun, this stellar mod is also complex, and there's a very good chance that rampaging barbarians or a wayward wraith will cut your first few games short. A player-created downloadable manual is a bit outdated, but its New Player's Guide in particular will go a long way towards helping you ascend from punching bag to global overlord. You'll also find plenty of helpful info at the mod's forums.
The latest FfH II version requires the Civ IV: Beyond the Sword expansion, but there's also an earlier version available for the 'vanilla' Civ IV without the expansion. If you've ever said "One more turn!"--and have a soft spot for elves, demons and magic--you'll love Fall from Heaven II.
Note: This link takes you to the vendor's site, where you can download the latest version of the software.