Heroes of Might & Magic Wisdom for Civ 5 is interesting, so is the ME Civs for it. Best mods I’ve ever seen though would have to be Civ 4, recently I’ve been using more of Caveman 2 Cosmos which spans from the prehistoric era when you have next to nothing (really, no language resource usage or anything) all the way until you’re at an intergalatic level with armies of spaceships and you’re an army of superhuman cyborgs that don’t age get ill or have crime. Unfortunately it’s unfinished and most of the sci-fi stuff isn’t in there yet and civ 4 is 8 years old now so it doesn’t get much modding anymore, luckily this mod is still in very active development and is going strong with regular updates.
The other major mod I liked was Fall from Heaven 2, it turns civ into a fantasy game with diverse and interesting gameplay, one of their goals was to make every civ play different and they fairly well succeeded, you can play as anything from the pacifistic culturally acceptive elves (with armies of treants and zombies to capture and convert their enemies) to the law abiding light aligned demons that have to be summoned from hell in-game, as evil builds the apocalypse slowly approaches until Armageddon strikes. Play as the insane circus people that capture and enslave their enemies for freak shows while tormenting them with spell casting puppets and Loki himself? Atheistic humans that are waist deep in adventurers? Vampires that can kill your own population for power or golem using dwarves? (as opposed to the dwarves that don’t use golems).
That’s just two of the best mods for civ 4 but there is a lot more from things that turn into a tactical strategy game to ones that make it an rpg or a sci-fi space based 4x game. Custom victory conditions based on civs history? it’s all in there.