Saw the steam sale and almost want to purchase Morrowind! I spent countless hours playing Oblivion years ago, and have spent a decent/okay-ish amount of time on Skyrim, but I never played Morrowind before, I just can't get passed the bad graphics!
1) How much of an improvement is there with the mods? I.E the Overhaul + other mods
It's night and day. If you get the Morrowind Overhaul, provided your computer can handle it, I'd say that the graphics are easily equal to or even superior to Oblivion. In some places, notably the night sky, some water effects, fire, you could even say that Morrowind is approaching Skyrim quality.
2) How many hours does it take to complete a single playthrough doing a mixture of side quests and main story?
That's such a hard question to answer. Suffice to say, it will take you many hours and I'd say even longer than Skyrim or Oblivion. There's no magic pointer telling you where to go, so sometimes there's a little bit of thinking involved with figuring out where to go next. The Main Quest itself isn't so much 'Oh my God! Hurry and save the World!' There are several points where the game really encourages you to go do other things, let events play out, and then come back and continue the main quest. All in all, the main quest is much more interesting, and better written than Oblivion and Skyrim's, and I think the pacing is much better as well.
3) How many guilds are there?
This is where Morrowind beats Oblivion and Skyrim with brass knuckles until they cry for mercy, receiving none. Morrowind has so many more guilds...off the top of my head: Fighter's Guild, Mage's Guild, Thieves Guild, Imperial Legion, Tribunal Temple, Imperial Cult, Morag Tong, plus you get your choice of joining either Great House Redoran, Telvanni, or Hlaalu. Each joinable faction is at least twice, or three times as long as a Skyrim guild. With better writing, more variety in quests, and a more believable progression. Granted, the cinematic values of Morrowind can't compete with the Skyrim Thieves' Guild, but that's to be expected.
Also, there are mods which add factions, some of which I hear are quite good. Plus, as said before, there is a new faction in Bloodmoon in which you get to make a decision about which portion of the faction's leadership to support, with the opportunity to help guide the construction of a village, and your own home.
4) Should I expect similarities from Skyrim, or Oblivion, in regards to the leveling and combat systems?
It's basically the same as Oblivion, but with more skills, and a much greater variety of spells. Also, the biggest difference is that a character's given skill is more determinant of a successful action...for example, in Oblivion to unlock a door you play a mini-game which depends on the player's skill. A skilled player can open a difficult lock, no matter what their character's lockpicking skill is. Conversely, Morrowind has no mini-games for lockpicking, and if you attempt to pick a lock there is a random chance that it unlocks, depending on your character's skill.
5) Do NPCs walk around in the cities and throughout the map, or do they just kinda chill? I always liked how in Oblivion and Skyrim, some NPCs would traverse the world and had various "routines."
For the most part NPC's 'chill'. Most NPC's will wander around their house, or the streets, but Morrowind is from an era before radiant/dynamic A.I.