"Holy shit" is quite a unique term. It is one of the most satisfying exclamations of surprise that can be uttered, as long as it isn't overused on trivial events. It's a way to come to grips with the fact that something surprising or extremely fucked up just happened. It is a vulgarity which may be inserted into an unusual situation which allows the utterer to express just how surprised he is at whatever is going on. It's quite a curse, and will probably conjure up various blasphemous images if it's meaning is contemplated. (God shitting? A priest blessing a pile of feces? What kind of heathen bastard would think up such a phrase?! Christ on a crapper, I hope he was ashamed of himself.)
While "Holy shit!" is not as versatile as "fuck" or the standalone version of "shit", the presence of the word "Holy" implies that it isn't meant to be. According to Webster_1913, one of the meanings of "holy" is "reserved from common or profane use". "Holy shit" is not like other swear words; it is not meant to be worn out by constant use. We can insert all the fucks and shits into our conversation that we want, but "holy shit" is set apart, goddammit! It is meant to be reserved for an event which demands its usage. It should be savored like a fine wine in the moment after a strange event has occured. Depending on the situation, it can be used in different ways.
In the case that something which poses immediate danger to oneself or those nearby has occured:
(A family is having a picnic in a park, when a pride of lions comes rushing at them)
Mother: These are mighty good sandwiches, if I do say so myself!
Father: Yep yep yep! HOLY SHIT! LIONS! RUN!
In the case that something really bad has happened:
Person 1: Did you hear? California was just eaten by giant worms!
Person 2: Holy shit! That's horrible!
In the case that someone has said something incredibly stupid:
Person 1: Look, after what Saddam Hussein did to the World Trade Center, I don't see how we COULDN'T invade Iraq.
Person 2: Wow. Holy shit. Just......wow. Holy fucking shit.
When something really disgusting happens:
(Dick Cheney is delivering a speech on national TV, when he suddenly strips naked in a frenzy, revealing that he's a hermaphrodite)
TV Watcher: Yeah, so I was telling my wife (glances at TV) HOLY SHIT! WHAT THE FUCK IS THAT? ARGH! EW...! NO!
There are situations where you just can't complete the utterance of "Holy shit". These situations often involve your death, and should be avoided.