It's a funny thing. Despite being a fixture on several BG-related boards (Still am), and knowing a handful of the people who've worked on the various mods out there, I've never really gotten into using all that many of them, myself.
I mean, granted, some of them really are quite good. Most of the interface improvement ones people do swear by. Things like allowing people to play through BG1 using the BG2 interface are pretty much mandatory these days, seeing as how the original BG is like, what? 11 years old? I mean, it's still a great game, but it does show its age.
There's also tons of stuff out there to "fix" things like broken quests or simply unfinished quests that the devs never managed to get done before the games actually shipped, so that's good, too. Plus, for the really hardcore players, there are mods to make certain key battles in the games a lot harder. (Something I've never actually looked into, but that, again, other players swear by.)
As for the various other things like the banter packs, flirt packs, extra NPCs, the other romantic subplots... well, some of them can be pretty hit or miss. But there's tons of material out there, and the links posted earlier in the thread are good places to start looking.
As for Tales of the Sword Coast which was mentioned earlier (i.e. the expansion pack to BG1. Eh, it's.... not so great. It's not bad, but I feel it goes under the category of "safely skippable." You're not missing much if you didn't go through it. Really, the original BG, BG2 and Throne of Bhaal are the only critical components. The series is pretty much a trilogy. as a result.
One pretty friggin' awesome trilogy. :D
There's a reason why I got hooked on the series and my obsession with it never really died.