Probably Yogsloth. Took some serious work though.

Lazarus - A Ultima 5 recreation using Dungeon Siege.

Far too many to count. Some from the pile of could not play the base game again:
Baldurs Gate - Big World, just adds so much.
Morrowind - Overhaul and enhanced, two big mods which really boost the graphics and feel.
Oblivion - Oscuro's overhaul, plus about 50 more mods.
M&B - Prophesy of Pendor - still the best.

Plus all the great ones mentioned above by other people. If you look on moddb they have mod of the years for the lastt few years, all those should be on your backup drive!

That's what I meant to say as well! The only time I ever managed to play through a 100+ hours Bethesda RPG without losing interest after a while, and it wasn't actually a Bethesda RPG but 'just' a fan-made mod. The story was so-so, but the world building, the immersion and exploration aspects were fantastic.

Neverwinter Nights and Unlimited Adventures have tons of great mods; one of my favorites back then was The Prophet series for NWN.

Morrowind - Overhaul and Companion Julan. Overhaul makes the game gorgeous and less tedious, for example by making it easier/faster to pick herbs. Julan is a companion that actually feels like a living-breathing companion, who follows you around for an actual reason. His story is built into the main quest, expanding it greatly, while leaving the quest itself untouched.

Neverwinter Nights - A Dance With Rogues. The best module I've ever played. Also, the first module I've played. Set the bar quite high for the other modules I've tried :)

Oblivion/Skyrim - Vilja. I have lots of great mods installed for both games, but I go with the companion who actually felt like a companion/friend. I hated that the npc-followers in Skyrim are just 2-dimensional henchmen, who follow you around for no reason other than coin, and have absolutely nothing interesting to tell about themselves. I wanted to go adventureing with a person, not a pack mule -.-

Dragon Age Origins - Skip the Fade. Again, I have a lot of great mods that expand the world, change NPC behaviours, make the game feel more alive etc, but I go with the mod that got rid of the most annoying and tedious part ever to be created in an RPG: the goddamned Fade :@

Minecraft - Tough choice between Thaumcraft and theTwilight Forest. Twilight Forest is a new dimension with gigantic trees and dungeons. Thamucraft adds magic and alchemy. And a lot of other things.

• OOO & Nehrim (Oblivion)
• Third Age (Medieval II)
• Power and Glory (GTR2)
• Counterstrike (Half-Life)
• Black Mesa (Half-Life 2)
• Complex (Homeworld 2)

Being a big fan of the Penumbra games I really loved Penumbra: Necrologue and Penumbra: Twilight Of The Archaic.
These are mods for Amnesia: The Dark Descent which continue and conclude (?) the story of the original Penumbra trilogy.
Absolutely amazing!

I also liked Silent Hill: Alchemilla. (video 1) (video 2)

Another vote for Nehrim. A massive total conversion for Oblivion that IMO greatly surpasses the original game itself.

Yeah, it's easily the Stanley Parable for me.

Not sure what is always considered as a mod. For instance, is the Fallout 2 restoration project a mod? In that case, probably that one. If not, I'd say the one in Evil Genius that allows 200 minions.
Oh wait, I've got it, the 1.13 mod for Jagged Alliance 2. That's a brilliant one! Bit buggy though when it comes to the ridiculously complex inventory overhaul.

Cube Experimental for FO3
https://www.youtube.com/watch?v=wVeBPPPBiZI

Heavy inspired by the Cube movie series. Quite an intense, unusual FO3, experience.

PS: Made by the ppl who made Oblivions Nehrim mentioned above ;)

MISERY mod for S.T.A.L.K.E.R.: Call of Pripyat

Fallout 2 Restoration Project

Best mod? Either Vitek, JMich or Yogsloth. Forum Mafia. Everyone is unique, hard to choose.

That of course, treating 'mod' as 'moderator' and not an actual modification.

Baldur's Gate Tutu, so we get the improvements of the BGII engine in the first game.
Third Age Total War and Stainless Steel for Medieval II Total War.
The Last Days for Mount and Blade.