My current list of mods:
Natural Habitat/Water/Vegetation/Weather (HDR) by Max Tael -- all awesome.
SpellIconReplace.esp -- assigns each spell a separate icon, surprisingly useful.
Quest Award Leveller -- this makes it so you can complete quests whenever, as the rewards will scale with your level.
Clocks of Cyrodill -- Adds hundreds of working clocks (and clocktowers) into the game, many of which are extremely intricate and awesome looking. Very well done!
Harvest [Flora] -- When you pick up flowers, the petals / mushrooms / etc actually alter their appearence to show that they've been picked.
The Lost Spires -- a fanmade expansion that is all kinds of awesome. If you try only one mod that adds new content, make it this one.
As far as the official DLC is concerned, I highly advise buying the Knights of the Nine DVD used if you can get it, as it comes with everything for cheap. Short of that, I really enjoy Frostcrag Spire, Spell Tomes, the Orrery (largely because the game feels incomplete without it, as you can see the room but not enter it), and Knights of the Nine.
Edit: I forgot BTMod, which preserves the look of the original menus, but presents them at a resolution that doesn't suck so you can see a ton of items instead of just 6. If you're playing on PC and you don't have some sort of UI mod (BTMod, DarkUI, DarnifiedUI) you're really not getting the full experience.