I've been playing Skyrim again recently, and thanks to the wonders of modding, it is lookin' damned fine.
That's not to say it ever didn't look fine—when the game was released last November, it was a perfectly okay-looking game. A little rough around the edges in places, not the hottest textures in the world, but still. Considering its sheer scope and scale, it was amazing that it looked as good as it did.
But as anyone who's played past Bethesda games on PC can tell you, the incredibly active (and flat-out incredible) modding community ensures that the version of each game that's released is far from the "final" version. The mod-combination I've spent the last month or so tweaking combines graphical upgrades, audio enhancements, interface tweaks and new items and locations, yet keeps everything stable and smooth.
(Note: In case it isn't clear, we're talking PC here, not consoles. Sorry, console people. You're pretty much stuck with the original. Feel free, however, to read this post anyway, then go browse NewEgg and contemplate your next paycheck.)
I've found that when it comes to modding games like Skyrim, there's usually a grace period of a few months where it's wise to stay away from most user-created mods until their creators get the bugs sorted out. Shortly after Skyrim came out, there were already some easy ways to make it look better. But past that, I avoided most large mods.