Completing several quests will trigger some pretty awesome scripted events. Notable examples are the Den of Evil (the red light goes out and the sun shines in through the cracks), Sisters' Burial Ground (Blood Raven's soul getting released and the ensuing electrical discharges), Radament's Lair (the Fist of the Heavens destroying Radament's servants when he dies), The Tainted Sun (the sunlight released from the altar), The Hellforge (Mephisto's dying screams and the souls released from the stone), and Betrayal on Harrogath (Nihlatak dragged into Hell).
The battle against the Summoner, especially when playing with the Sorceress. Epic magical duel in space.
As a matter of fact, any character fighting their enemy counterpart (Sorceress vs. Summoner, Amazon vs. Blood Raven/the Countess, Necromancer vs. Nihlatak, Barbarian vs. the Ancients) can qualify as awesome.
Druids against the Ancients doubly so as they're basically outcast barbarians.
The beginning of Act V, especially for the Barbarian. You're not alone in this one since you fight alongside an entire army of your compatriots against Overseer Shenk and his minions.
Act V in general. You start off generally distrusted and an interloper in the midst of the war. As you progress, you eventually become their greatest champion.
The duel against the Ancients.
Really, the entire progression of your character throughout the story. You go from a plucky wannabe to a such a badass that you launch a one man invasion of Hell so that you can kill the devil.