So this is a surprisingly complicated question.

There are a few major reasons why being excommunicated was an "effective" punishment. First off, keep in mind that the "average" person would not have been excommunicated. Generally it was reserved for figures who strayed from the "path" in a fairly visible or outrageous way (usually either politically or due to heretical beliefs).

As Juventus points out, being excommunicated meant that you were not allowed to partake in the sacraments. He (or she) way overstates the issue of "perfection" however. No one (least of all a political figures like kings or aristocrats) would have expected to be perfect, sin was a part of daily life and being an aristocrat perforce led to actions (warfare, being wealthy, etc.) that would be considered "sinful". Of course some of the sacraments were designed to directly deal with these problems, so being cut off from them would be a rather difficult place to find oneself. If you were to die while excommunicated, trouble indeed. In a world where death was a very real possibility, especially for an aristocrat, this could be a serious threat. Moreover, your excommunication also threatened the spiritual welll being of your family. You could not offer prayers for the souls of the departed or for the well being of your family.

Beyond this problem there are several others. In the medieval world the Catholic Church and Catholic practice was an integral part of everyday life. Being cut off from the church meant you were cut off from society. You could not spend time with your community in church, you could not participate in marriages, could not be buried (or have other family members buried) in the graveyard, you could not participate in the numerous Christian feasts and festivals scattered throughout the year, etc. and so forth. To be excommunicated was to be a pariah. This was probably as dire (and from our modern perspective perhaps a more "practical") threat as any spiritual ones.

For a figure in power, like Henry IV the Holy Roman Emperor who was excommunicated by Gregory VII or Philip I of France, excommunicated by Urban II, excommunication carried heavy political repercussions as well. In a world where oaths and bonds were an inherent part of political power, excommunication effectively annulled any oaths one might have to you and disrupted the political clout and abilities of a ruler. This might not be a big deal if you were firmly in control of your realm and your vassals, but in a situation where your vassals were already looking for ways to buck your authority, excommunication could and did result in rebellions.

Of course being an excommunicate also opened you up to legal prosecution by the church (and the state) and removed any sort of protection from oppression, banditry etc. Attacking an excommunicate would carry far less weight than attacking a fellow community member. An excommunicated heretic's land and property could be seized.

So, lots of reasons why being excommunicated was a serious punishment and not something one would take lightly. With all that being said, however, excommunication was not the end of the world.

Those with power could flout excommunication and regularly did for great lengths of time. Kings and lords often exercised a heavy amount of control over the church in their area and could safely ignore distant excommunication orders for some time.

The other major caveat with excommunication is that, should the person repent, excommunication was to be lifted. Obviously repentance was to be sincere, but how does one judge this sincerity. There are numerous accounts of excommunication being lifted only for the newly reformed person to return to their old practices as soon as they had reestablished their power.

And excommunication was not a very effective tool when used multiple times on the same person. The severity of the punishment was mitigated when a person could see that there were ways out and that fire from heaven didn't immediately rain down upon their head.

Excommunication thus was an important tool in the ecclesiastical tool box but we have to be careful about overstating its effectiveness. Henry IV begged for forgiveness from Gregory VII in the snow as much because his enemies took advantage of his excommunication as because he feared for his soul. Later when he was excommunicated again but more firmly in control of his realm the punishment doesn't seem to have phased him at all.

An average peasant who managed to get himself excommunicated because he decided to follow a heretical preacher would probably have had less ability to flout it, and yet heresy was an endemic problem and people clearly continued to risk punishment in pursuit of what they believed to be true salvation. And if your entire community was sympathetic to that preacher you might not have a problem at all.

Did I mention it was complicated :)

As a follow up question, when did excommunication lose its efficacy as a political threat wielded by the pope?