There are quite a few honestly. Team Fortress and DayZ are obvious examples, I also quite liked Natural Selection.

A Link to the Past/Super Metroid combo randomizer. Both games smooshed into a single ROM, with certain doors taking you from one to the other. Item pools are then shuffled together, sending you on a big scavenger hunt to find Metroid items in Hyrule so that you can explore Zebes to find Zelda items so that you can explore Hyrule.

Unfortunately it's somewhat lopsided due to how much bigger ALttP is than SM, but the technical novelty of it is amazing and worth a playthrough.

Elden ring seamless coop. I can't believe that such a small file could create such a better experience in the game.

Excessive Plus, a Quake 3 Arena mod

It's a mod where the weapons are extra powerful. Making for some interesting physics like using the weapons for jumping and one shot kills. Even with all the madness of super weapons, it had great gameplay and competiveness.

The community lost the steam and now there aren't many players last time I checked which is too bad because it was a great mod.

A movie with the mod: https://youtu.be/-qIvgdDznb8

Allowing you to collect all three triforce pieces in Ocarina of Time using arbitrary code execution.

Princes of Darkness for Crusader Kings 3

Midian XL for Diablo 2

In terms of actual impact, a Fallout: New Vegas mod named Tale of Two Wastelands that takes a Fallout 3 copy and brings all of it into the Fallout: New Vegas world.

The mod makers didn't create the great bulk of the content, made a converter, but it amounts to a new experience with an AAA-game level of content addition.

Does make me wonder about other mods that can leverage pre-existing work. Cataclysm: Dark Days Ahead had a mod where someone imported a real life map of Massachusetts, for example. Some flight sims can do similar stuff.

Does the flight sim in Excel count? If yes, then that.

Otherwise, Day of Defeat (Steam mod). Still somewhat peeved that it never got the same recognition as CS.

X-Com and X-Com 2 Long War mods. Essentially the games at their full potential.

That reminds me, It's almost time for another playthrough i think.

Mekanism, an extensive tech mod for Minecraft that could quite happily be its own game.

Expanded Stardew Valley! So much added stuff!

Can't recall the name but there was a Halo PC mod which was an entire fan made campaign.

Black Mesa

People who improve Bethesda's in game menus are awesome.

Half-Life: Firearms was sick back in the day.

Doom: Infinite is pretty cool. It turns it into a roguelike. It’s amazing as is, and is still in beta.

Space Exploration for Factorio. My dude built a huge mod for free, then was hired by the devs to desogn the paid sequel, Factorio 2, and although large and great its still smaller and less complex then the mod.

Fall from Heaven 2 (overhaul mod for Civ4) would've been top pick until this year, now it's Enderal (even calling it an overhaul is selling it short, it's a whole new game built on the Skyrim engine).

Pokemon unbound. Its a completely new game.

Doom 3 OpenCoop. There's a few mods that convert the single player campaign into online co-op. At the time the competition was Last Man Standing, but I liked how vanilla OpenCoop was. There's better mods now, and OpenCoop never left Alpha versions. Although the only game breaking bug is with the first teleporter sequence which kicked all clients from the server. Easy enough to skip that segment.

It's a bit of a scripting nightmare since the game uses so many floor triggers for events never intended for multiple players. That's what impresses me about the mods.

That or The Dark Mod which is now a Standalone freeware title using idTech 4 engine to make a fan sequel to the classic Looking Glass Thief games. There's a TON of fan made high effort maps to play on.

I remember playing zombie master, a half life 2 mod on a lan party and being blown away by the mechanics. Also ,,the hidden,, was a new experience for me.

In zombiemaster one player plays against the rest and has a well... Real time strategy layout and spawns and controls different kind of zombies . The other players have to complete tasks and survive.

In ,,the hidden,, one player controls a almost invisible super strong rouge experimental super soldier against the special forces of iris .

Immersive cursedness

A minecraft server side mod that lets unmodded clients see through nether portals somehow

the multiplayer mod for BOTW

Shrek mod for Left 4 dead 2😂

Cataclysm: Darker Days Ahead.

Daily pull requests for over two decades on an open source project

The Vanilla Expanded mod set for Rimworld

I came across the Star Wars Helldivers mod. It’s basically Battlefront 3 but man it looks cool

GregTech. You need to be special to make the game this complex, and I am more for playing it.

Team Fortress and Team Fortress Classic.

The entire OAR for Fallout 4:

Outcasts and Remnants

Fusion City Rising

Depravity

Project Valkyrie

Entropy: Zero 2 For HL2 was awesome

RogueTech for battle tech is amazing. Hundreds of hours into it.