Half-Life: Opposing Force comes to mind. It's not the biggest expansion ever, but I did enjoy it quite a bit back in the day.
Other than that, I didn't play too much expansions, I'm afraid. At least nothing got stuck in my memory too much. I got all expansions for ETS2, but those are basically just map extensions. I also remember enjoying expansions for The Sims and The Sims 2 a lot. Oh, and of course expaniosns for Civilization games.
Official member of VGC's Nintendo family, approved by the one and only RolStoppable. I feel honored.