The Law of Conservation of Excitement
 |
(The number of exclamation points in a story) times (how much impact each one has) equals a constant. |
 |
In other words, the more exclamation points a writer uses in his text, the less each one means to the reader. Use too many, and they all mean nothing. The reader will then ignore any excitement or tension that the author wants the points to convey.
As a result, a writer should use them sparingly, and then only in dialogue. One, and only one, exclamation point should end any excited speech—more than one looks amateurish. (Similarly, try to avoid compound punctuation like "?!", aka the "interrobang". It's nonstandard and isn't used in proper writing, the title of the second Negima anime notwithstanding.)
Further, the classic third-person narrative voice should not use exclamation points at all. An obvious exception can be made if the Narrator is an actual character within the story who is relating the events thereof, but even then the author should avoid them whenever possible.
Finally, the author should never end every sentence in an exclamation point—not only will it dilute the impact of all the exclamation points in the story, nobody's ever that excited, and the dialogue will end up looking like it came out of a Silver Age comic book. (Of course, if the author is deliberately crafting a character whose schtick is to sound like he's from a Silver Age comic book, then go for it.)
(Text adapted from A Fanfic Writer's Guide To Writing by Robert M. Schroeck, with the permission of the author.)