The Total War community is particularly rich. Many mods, particularly for Medieval Total War 2(which must be easier to mod), expand the game to the point where it is almost an entirely new game.
Two mods which I recommend to my students are the Third Age Mod (Lord of the Rings) and Call of Warhammer (based upon the eponymous fantasy world.) While both are tremendous games in their own right, the lore and background to the worlds are fantastic. Previous attempts to recreate this world have fallen short (although the March of Chaos was admirable.)
Particularly pleasing is the ability to have 10,000 troops on the field at once: to set them up in minutes, and refight the battles in moments is still something I didn't think would be possible ten years ago. It's enough of an experience to play for 20 minutes and to feel like I've played for hours.
For those interested in playing, you need a copy of MTW2 (with the Kingdoms expansion) on Steam, or via here: http://www.amazon.co.uk/Medieval-II-Total-War-Gold/dp/B0012BFK0G/ref=dp_ob_title_vg
You can download the Call of Warhammer mod here: http://www.twcenter.net/forums/showthread.php?t=320935
You can download the Lord of the Rings mod here: http://www.twcenter.net/forums/forumdisplay.php?f=654
Read the instructions if you struggle; I needed to download a specialised launcher in the end to make the game stable. It's all in the forums, though: worth spending a few hours to get it working.