Dracula X - Rondo Of Blood is an obvious choice... it's like a tour de force of artistic and clever bg/sprite tricks that make it seem like the system can do things it's not supposed to.
I've always thought the multidirectional software parallax in lv1 and lv2 of Ninja Spirit was both technically and visually impressive. It's all done with tile animation, but it's so convincing that it's hardware-based parallax, somebody would have to point it out to you in detail to know that it wasn't.
All the games with tons of horizontal interrupt scrolling look great doing it (Coryoon, Air Zonk, Dead Moon, etc.) but it's no great technical feat. In that same vein, the games that use line scrolling (like SFII' CE's floors) or sine wave effects (like the lv2 boss background in Sinistron) are always crowd pleasers. All of these effects are essentially acheived the same way; by way of controlling the horizontal interrupt, sometimes on a per-scanline basis.
The "rolling barrell" effect in lv3 of Metamor Jupiter is pretty simple to do but also pretty nice eye candy. As is the "horizon" effect from Chris Covell's Axelay demo. They both use the same scanline trick, i believe.
Also, some of the tricks used in Soldier Blade are pretty impressive. The overpasses that "parallax" over the city, and the huge tank-like boss that is cleverly comprised of both tiles and sprites. Not to mention the vertical "metal canyon" that uses faked column-scrolling when the huge crack in the Earth opens up.
The transparency effect in Jackie Chan is also very neat.
The rotating "Gunstar-style" title screen of Chris Covell's Tongueman's Logic is also pretty nice looking. Makes the average person think, "Woa, the PCE can do THAT?!"
Not to blow my own horn, but I made some pretty neat effects for Xymati that are completely doable on the PCE...