Great units in no particular order:
Longbowmen - 3 range unit in the medieval era? Oh yeah! Sure, you can argue that you could just as well have a Crossbow with the range promotion, but since Longbowmen start with it they are very easy to keep alive and crank them up in levels. With ca 4 Longbowmen and a Horseman or Swordsman you can conquer that whole world during the Medieval era (at least on Emperor

)
Keshik - Probably the best unit in the game. Again, with a small army of Keshiks and a Horseman or two you can wreck the entire world in record time.
Janissary - Full heal when killing a unit is just amazing. If you upgrade an army of Janissaries to rifles/infantry/mech infantry you have a nearly unstoppable army.
Tercio & Musketeers - Two very underrated units in my opinion. While the Musketeer only get the increased combat strength and no special promotion they can really be game changing when they appear. With the recent nerf to Longswordsmen there are really no units in that time period that can stand up to them, except for Knights. Which brings us to the Tercio with its 100% bonus VS mounted units. It basically means that until Rifles starts appearing you can dominate the Renessaince battlefield.
Mandekalu Cavalry & Conquistador - With the nerf to Longswords Knights have become much better units recently, and both the MC and the Conquistador are good at capturing cities, removing one of the problems with the regular knight.
Immortals - Double healing means you can crush cities in the Ancient era. Just park your Immortals outside the city with a few archers for support and you can wreck a lot of havoc.
Other great ones are Companion Cavalry, Chu-Ko-Nus, Hwa'Chas and Berserkers to name a few.
Bad/underwhelming units in no particular order:
Lancer - Defense penalty in addition to the regular lack of defensive bonuses for mounted units? Ouch. Hard to keep alive which makes them even worse than Knights in my opinion. The Sipahi is better, but still pretty terrible.
Fighters/Zero - Rarely needed against the AI. Probably a lot more tactical usage in multiplayer though.
Slingers - The only good thing about these in my opinion is that they don't get a penalty attacking cities. The "retreat" promotion is wasted as if your ranged units do get melee attacked you are doing it wrong.
Indian War Elephant - Not really a bad unit, just underwhelming. I tend to see it as a more expensive version of the regular archer as it has the same movement but more strength. As its promotions are wasted when you upgrade though I don't really see the point in building a lot of these. One or two are good for defense, but apart from that I don't really like them.