Civilization add-on packs have for the most part, always been good. Beyond the Sword is by far the best yet. It provides some excellent scenarios, and makes some fundamental changes to the to the main game as well.
I am usually not a great fan of scenarios, and as such don't play them that much. There are however, 11 new scenarios included with BTS, and some are fantastic. They include fictional scenarios, as well as historical ones. I play mostly the latter, and have spent many hours on the WW2 scenario.
It's the main game though, that I really feel has been improved though. As well as 'cosmetic' changes such as new units and new buildings, some fundamental game mechanics have been altered. These introduction of espionage from nearly the beginning of the game. This can actually be invested in in the same way as research and culture, and the counter sits beneath the two. Another example of this is the space-race victory. The game has gone back to its roots with this- like civ 1, you can pick and choose how to build your ship. If your beaten in launching your ship, you have the option of making yours faster and you may still have a chance.
On top of this, there are other smaller changes which none-the-less add to the game. One example is random events (like earthquakes, plane crashes, weddings between civilization), which can have either a negative or positive effect on things.
If you have Civ4, Beyond the Sword is an absolute must-have expansion.