It's basically a collection of all the stuff modders have made for the game over the years: new creatures, new clothing and armour appearances, new placeables, new player character appearances, etc.
You need it to play some user-made modules that make use of all the new stuff. The idea is that by putting all this stuff in one big download, you don't need to download as many separate hak-files for each new module you play.
You -can- add the CEP to modules that do not use it, but this a] requires some technical know-how and b] doesn't really add much since those modules don't make use of the creatures, placebles, items, etc. All you'd get is the ability to craft your armour in more ways and choose some new heads for your character. Generally not worth it.
If you just want to play the official campaigns, you needn't bother with the CEP. Only a minority of modules (but quite a few persistent online worlds) use it.