Yeah this is hard. Hard to remember what expansions really were fun and brought new life to the game. I'll try, some of my favorites have already been mentioned though.
1.Red Alert 2: Yuri's Revenge(2001)
2.Far Cry 3: Blood Dragon(2013)
3.Oblivion: The Shivering Isles(2007)
4.Blue Shift(2001)
5.Finding a final one was hard, but if unofficial content counts as Add-ons, then I'd like to mention Brutal Doom. Otherwise it's a tie between Fallout New Vegas:Old World Blues and Skyrim's Dragonborn DLC.
And I suppose I'll explain each like everyone else tried to.
So first up Red Alert 2:Yuri's Revenge. I love this game. I love Command and Conquer, but this will forever be my favorite CnC title/expansion. It brought new campaign branches and a whole slew of improvements from the base title. It was amazing. I still have my original discs of RA2 and YR. They will always have a place in my heart.
Far Cry 3: Blood Dragon. Standalone DLC(The best kind!), this was a kick ass twist on FC3's mechanics and just shows how awesome games can get. It was advertised as a retro 80's action flick styled game, and did it deliver. From the artistic styling to the music, it just reeks of old action sci-fi movies, somewhere between Rambo and Tron. One indestructible badass american versus and army of evil robots. And that theme song, THAT FREAKING THEME SONG. Just awesome.
The Shivering Isles, this one was interesting. A friend had shown me Oblivion just a few days prior, and I was in love, it was amazing, I immediately begged my mother to buy me the GOTY edition for my computer, it took a week or so but finally she said fine and I played that game day and night(thank god for summer). Finally coming upon the portal to the Shivering Isles, going through the portal just made the game feel different, gone were the forests of Cyrodiil, instead replaced by a bizarre landscape, with two different and unique sides. The creatures were odd and the main character of the expansion was hilarious. It was a really fun experience. I'm tempted to pick up the game on Steam if I can muster up enough funds to afford it. Although I fear it won't hold up to Skyrim and such. I still have the GOTY disc I got all those years ago.
Half-Life:Blue Shift. Yes! I loved this expansion, I, like the other games, still the original HL and BS discs. Opposing Force was fun, but Blue Shift really brought me in, being able to play as a security guard was just fun, it went from "Oh, fix an elevator? Well this is boring." to "HOLY FUCK ALIENS FUCK YOU I KNEW THIS WAS COMING WHY AM I SURPRISED?!?". Oh and Barney went to Xen, come on. the HD model pack also improved the experience a little I think. Was just a really fun expansion to the story of Half-Life.
I have 3 here in 5th, and one might not count, so this will be short. First off, if it counts, my number 5 is Brutal Doom, an add-on for Doom I and II. Basically makes the game a hell of a lot more fun and has way more gore than any game you've seen in the last few years. If that doesn't count. Old World Blues for FO:NV or Skyrim's Dragonborn. Old World Blues takes you to a cool research center and has you fighting robo-scorpions, fuuuun. And Dragonborn takes you to a cool new(or old I guess, if you've played Morrowind I think(I never played :p))island, and has new creatures to encounter, and new environments to discover. Seriously, that realm with all the books (Forgot it's name) was really cool at parts.
Something of note, Halo 3:ODST is a standalone game, but was planned as an expansion. ODST is my favorite Halo title personally, and would have definitely made it to the top 2nd/3rd place if it counted.