It is interesting that the units that are being discussed most - crossbows, ironclads, explorers - all have their particular niche.
I don't build a lot of crossbows, and in fact don't build any in many games. But...if I am next to Japan...you can bet that I will build several of them to include in my defensive forces.
Someone mentioned marines...I tend to like infantry a bit better, too...but...if you're dealing with Germany, Marines with Combat II/Ambush are Panzer killers...catch the Panzers in the open field, and smash 'em (you don't want to use tanks against Panzers, of course). Though Infantry with Combat II/Ambush can be effective against Panzers, as well.
I would probably vote for Explorers or Ironclads as the most useless...but again, several people have shown where Explorers can be useful (certain maps, for example). And even I have been known - in certain situations - to have a few Ironclads guarding my seafood resources.
I suppose the ultimate point is that every unit has it's place...even the most useless unit can be very useful in certain circumstances, which in turn gives you good options for different playstyles. Which is probably a credit to the balance put into the game by the game designers.