So you finally got tired of the vanilla experience? (shame on you)
Don't worry, there's dozens of fan-mods out there to keep things interesting, including custom maps, weapons, units and more. If you're interested in making your own, check Customizing.
Downloading & Installing
To install them, check the individual instructions included by the mod author. Most mods just need to be extracted into your Data Folder, but some require special steps and instructions.
If you did everything correctly, you should now find your mod listed in-game in the Options > Mods section. If it's not showing up, recheck your installation. Common mistakes include extracting it to the wrong folder or into a subfolder in the Data directory. Note that you can only change mods from the Main Menu.
After you enable any mods, the game will restart to load them and the mod changes will now be in effect. These apply to any saved game, new or loaded. Be careful when changing and swapping mods of existing saved games, as removing mods used by a game may cause serious bugs and crashes. Total conversions in particular are best used on new games.
If you experience problems or bugs while playing with mods, be sure to isolate them to the problematic mod first (to see if the bug comes from a specific mod or combination of them) and report them to the respective mod author.