Warriors are great for cheap, quick marshal law and 10 shield riflemen. Far from useless IMO.
Explorers are one of my favorite units. Move 3 over any terrain, finds the best city locations(special resources) quick, finds tons of gold, units, settlers, NONES, and most importantly: reveals all the land way quicker than anything else. Knowing is half the battle, etc
Archers are great to find in goody huts along with chariots. Move them next to an enemy city, demand tribute, use them to catch barb leaders, etc
Then somebody said crusaders! I disagree hehe, they are offensive kings of their time, don't use them for defense silly

Fanatics are good because they're cheap, free support, just as good as a rifleman on defense - which makes up the bulk of my defenders at that time.
Now that I've got that out.. My vote is for cruise missles, nuclear missles, and helicoptors as the least important units.