Civ2 had some of the best scenarios, many of which fundamentally changed how you played the game.
*Vanilla had Rise of Rome and WW2.
*XP1 (Conflicts in Civilization) had Alexander the Great, Jihad (AD 600-700), Crusades, Mongol Hordes, Feudal Japan, American Revolution, American Civil War and WW1, as well as a fictional WW3 in 1979, and alien invasion and post-apocalyptic scenarios.
*XP2 (Fantastic Worlds) had Midgard (Tolkien-esque), Mars colonization, Jules Verne (Steampunk), Native America (no Europeans), Atlantis, Dinosaurs (yes, Dinosaurs!), Ice Planet, X-Com, Masters of Orion and Masters of Magic.
We haven’t really seen it’s like since then. There have been scenarios, but none quite as original or ground-breaking.