I'll also point out a couple of utilities that will help you a lot in organizing these things. And, there are a few mods I label as "INSTALL LAST" because they actually work better if you install them after other mods. (It doesn't matter which of these "LAST' ones actually goes last -- the point is that all those go after all the others.)
If you REALLY want to go wild, using the Morrowind Script Editor will allow mods that are otherwise impossible. Go here for more information.
Also, one last obvious but still necessary-to-point-out thing: These are some of my favorites. I don't use a large number of mods that some people consider absolutely essential. I haven't listed any house mods. And I leave out a bunch of realism and landscape retexturing that some people love. The best way to test 'em out is to try 'em out, if at all possible with a clean install of the game and, bluntly, a character you don't much care about. Go through a few levels, cull your list, then do a clean install and only use what you want.
(Some of the most prominent mods I don't use include Morrowind Comes Alive [which adds a heck of a lot to your NPC experience -- just not things I want or need... a lot of people love it, though, and have added onto it substantially; just search for mods with MCA], CM Partners [same thing], Morrowind Complete [ditto], Morrowind Enhanced [ah-yup], Qarl's Gothic Attire mods [which somehow conflict badly with all my dialogue], and a lot of the Vampire mods [especially Vampire Embrace]. If you want 'em, go wild.)
-----
The very first thing to do is to make sure your game is patched. This page has what you need, but you only need to install one patch. If you have only Morrowind, get only the patch for that; if you have either of the expansions, Tribunal or Bloodmoon, get the patch for that; if you have both expansions, get the Bloodmoon patch. Install whatever of the official disks you have, in the order Morrowind - Tribunal - Bloodmoon, then run the patch.
For mods, the first place to look is the mods list at Planet Elder Scrolls. To be more specific, the Mods Hall of Fame. Telesphoros' List is pretty damn comprehensive, and Finding and Using Mods is an excellent guide that will save you a lot of grief.
On that latter page, under "Tools for Mod Users / Mod Editors", you will find links to TESTool 1.3 and Leveled List Resequencer v1.0 for TESTool. Morrowind uses something called "leveled lists", which are basically lists of the kinds of critters and items you find based on your character's level. These can conflict with each other, unless you use a simple tool to organize them. Other mods conflict with the main game or with each other, and occasionally you have a truly badly written one that breaks something. I try to avoid those on general principle.
-----
Now then.
I use lots of different mods, and many of them are for altering the appearance and atmosphere of the world. Good examples are:
- 20 books (additional texts, including some familiar ones)
- A Portcullis For... (these mods add gates to some of the Imperial forts)
- Authentic Signs: Inns and Taverns 1.1 (install AFTER "Unique Signs & Banners")
- Balmora Un-Mod (removes details which don't affect the game but DO affect the frame-rate)
- Balmora Waterfall (nice waterfall just north of town)
- Better Books (replaces the textures -- drastically better looking)
- Better Caldera (complete overhaul of the town, so be careful you don't use conflicting mods)
- Better Pelagiad Transport (adds a cart you can hire between Pelagiad, Seyda Neen, and Balmora)
- Book Jackets (ALTERNATIVE to Better Books -- a series of mods which puts unique covers on every book in Morrowind)
- Book Rotate (makes it easier to put books on bookshelves)
- Correct Spell Sounds
- Enchantment Colors De-Enhancer (magical items have a pronounced "glow" which some people hate, and there are numerous mods to eliminate it completely... but I like this mod, which just tones it down)
- Fashionable Merchants (when you sell armor or clothing to merchants, if it's better than what they're wearing they will put it on -- which, besides altering their appearance, means you can't buy it back if you want. This mod fixes that)
- Flask Replacer (much, much better glassware)
- Flora Glow (some of the plant life is phosphorescent -- install AFTER other mods affecting plant life)
- Illuminated Windows (building windows light up realistically at night -- truly cool looking)
- Ivy & Stone Cottage Texture Replacer
- Jarrod's Bloodmoon and Imperial Texture Packs (I'm not fond at all of his New Texture Pack, though)
- Kel's Hi-Res Hlaalu Retex pack
- Lantern Replacer (install AFTER "Illuminated Windows")
- Lights 300 w/icons (INSTALL LAST -- affects the entire game, especially dungeons, which become MUCH darker -- very cool if you do install it, but give it some serious thought first... you will be much more dependent on torches, Light and Night Eye spells, and it'll make everything a tad creepier)
- Magick Books (adds some Earth occult texts to the Caldera Mage Guild)
- Mixed Signposts (road signs in English and Daedric -- of all the "signs" mods, I think these look coolest, but Real Signposts, Textured Signs, and Weathered New Foliage Textures add-on)
- Nemon's Balmora Interiorator and Vivec Interiorator (excellent mods adding all kinds of cool interior details to the two largest cities)
- Propylon Redux 1.1
- The Regulars (taverns have NPCs actually sitting at tables -- a small thing, but it matters)
- Ring Texture Fix
- Sexy_Almalexy (retexturing of Almalexia from Tribunal)
- Spring! and Spring! Creature Pack
- Tavern Ambience (adds sound effects to taverns)
- Traveling Merchants (exactly what it says -- you can find them on the road)
- Unique Signs & Banners (improved signs for about twenty different shops and inns)
- Wilderness Sounds Mod (a vast expansion of ingame sound effects, which adds incalculably to the atmosphere)
- Artifact Level Requirements (you can't use some really powerful magic items until you're experienced enough)
- Blight Infections (your character can catch Blight disease if you're out during an ash storm)
- Bloodmoon Extras (things added by the expansion that don't actually show up in the game)
- Daedric Invocation (makes getting divine quests easier)
- Darker Red Mountain (ALTERNATIVE to "Tougher Dagoth Ur" -- makes the last part of the main quest tougher)
- Fair Magicka Regen (the one by Glassboy) (in the unmodded game, your magical energy regenerates s-l-o-w-l-y; this lets you regain it more quickly, you determine the rate)
- Healers (the game mentions getting healed at temples, but it's not actually an option [except for buying healing potions]. This mod puts it into the game)
- Marksman (if you like missile weapons, you absolutely need this)
- Passive Healthy Wildlife (INSTALL LAST -- makes many critters not nearly so aggressive)
- Persuasion Response Expansion (many more and better responses when you try to admire/bribe/intimidate/taunt)
- Potion Sorter (INSTALL LAST -- sorts out your potions icons alphabetically and renames them more logically)
- Quest Giver MB (a major cheat which can also bail you out of a tight spot -- it's a chest, in one of the storage rooms of th Ald-Ruhn Mages' Guild, that has one of every quest item in the game)
- Regionally Known Criminals
- Regionally Known Werewolves
- Richer Traders (INSTALL LAST -- and, honestly, there's an older mod called "Rich Traders" that seems to be more stable, if you can find it [again, INSTALL LAST])
- Sorefoot Enterprises (extra silt strider stations, several adventures, and just cooler stuff)
- Taddeus' Balanced Weapons and Armor (INSTALL LAST -- two separate mods that fix bugs, incorporate several excellent mods such as Area Effect Arrows and LaFemm Armor, and balance the weapon and armor types more realistically than the original game -- note that there is also a "Balanced Objects" mod, which I find makes the game much less fun; your mileage may vary)
- Tougher Dagoth Ur (ALTERNATIVE to "Darker Red Mountain" -- makes the last part of the main quest tougher)
- Tribunal Integration (things in the Expansion are added to the main game)
- Unboarable Rieklings (Bloodmoon has some gobliny things riding boars. In the unmodded game, they die simultaneously; with this mod, you can unseat the riders)
- VGreetings (enables greetings which shipped with the game but were not activated)
- A Call to Issilar (a huge adventure you can only get to AFTER you beat the main quest)
- Abandoned Well at Dagon Fel (now you can go down it, and there are lotsa bad things down there)
- Annastia 3.3 (several integrated quests)
- Apostates to Oblivion: Beyond the Realm of Death
- Ebonheart Gentleman's Club
- Ghostpath (oooooh scary -- but, warning, kinda buggy)
- Havish
- Illuminated Order
- Inferno's Island Revisited
- Inn of the Whispering Wood
- Island of Tusar
- Mebridith
- Reign of Fire: The Attack of the Dragons
- Suran Underworld
- Tales of the Bitter Coast
- Tales of Tel Branora
- The Haunted Bridge
- The Imperial City of Cyrodil
- The Old Unused Well of Pelagiad (much more a beginning adventure than the others, but HINT HINT have a Levitation [Rising Force] or Slowfall ready when you go down the well)
- Tombs Expanded (like bashing undead? Well, this adds lots and lots and lots and lots....)
- Tos Caverns
- Classical Monsters Replacer (much better meshes for a dozen critters)
- Giants Ultimate 2.7 (possibly the greatest monster-adding mod of all. You might also want the "Giants Ultimate [No Monsters In Cities]" mod, which removes the spawn points from cities -- install it AFTER GU)
- Silt Striders Are In Vvardenfell
- The Wilderness Mod 2.0 (many, many, many new critters)
- Advanced Guards 4.0 (improves the city guards quite a lot)
- Astarsis Basic Replace
- Astarsis Bearded Nords
- Astarsis Dark Elves Female Beauty
- Astarsis Dark Elves Heroes
- Astarsis High Elves Female Beauty
- Astarsis High Elves Heroes
- Astarsis Half-Human Khajit
(The original faces that load with Morrowind are -- how shall we put this -- ugly as fuck. All the Astarsis mods dramatically improve character faces, including giving you some much, much better looking options. And lots more hairstyles.) - Better Bodies (Another dramatic improvement -- make sure you've got at least version 2.2)
- Elders of Morrowind (adds NPCs that shipped but were not in the game)
- Less Generic NPC Project (a series of mods adding lots of new dialogue to each town)
- Black Heart Armor (just sittin' there in a crate in Molag Mar)
- Blue Glass (new armor shop in Balmora)
- Korana's mods (http://korana.elricm.com/Clothing.htm)
- Moon and Star Ring (makes one of the main quest items more powerful, possibly unbalanced)
- Metal Queen Boutique (possibly over the top)
- Sexy Daedric Armor (hubba hubba, but pricey)
- Silk Dragon Imports (Asian theme -- also has some object retexturing)
- Slofs Goth Shop for Men (good lookin' stuff for the guys)
- Violet Femme Boutique (new shop in Gnisis with nifty purple armor sets for the ladies)