The general purpose shift macro
/cast [mod:shift] Inner Will; Inner Fire
This macro will cast Inner Will if you're holding down shift and it will cast Inner Fire if you aren't holding down shift. This is, by far, one of the most useful macros there is. Inner Will and Inner Fire could easily replaced with any other two spells to achieve the same effect. I also have one of these for Power Word: Fortitude and Shadow Protection. My shaman has one for Astral Recall and his hearthstone. This macro is really good for just cutting down on bar space and combining similar things that can't be used simultaneously. It can also be expanded, such as
/cast [mod:shift] spell1; [mod:ctrl] spell2; [mod:alt] spell3; spell4
This macro would cast each spell if its corresponding modifier key was held down, and would cast spell4 if no modifier key was held down. These are also fun to put different mounts and pets on, so you don't have to go digging through your menus to find your favorites.
/cast [stance:1] Enrage; [stance:3] Tiger's Fury; [stance:5] Nature's Swiftness; Hibernate
This macro will show something different depending upon which form the Druid is in. Stance 1 is bear, 2 is aquatic, 3 is car, 4 is travel, 5 is moonkin or tree of life form, and in this case Hibernate would be shown in caster form. This one is currently very useful for feral druids in PVP because they tend to do a large amount of form changing. This macro is really great for cutting down on how much bar space is taken up on your screen. Now, this macro won't work on your main bar, because that bar changes every time you change form. This one can also be used for warriors and death knights, although it's probably far less useful for them.
The Atonement Macro
/cast [@targettarget,harm] Smite; Smite
Every discipline priest who uses Atonement should have this macro. If I'm targeting a party member, then I will Smite their target. If I'm targeting and enemy, then I will Smite them. The macro would fail if a party member wasn't targeting and enemy. I use this macro so that I can keep my tank targeted but still use my Atonement healing. This way I don't have to change targets to switch from direct heals to Atonement heals. I actually have two of these, the other one has Holy Fire as the spell.
/cast [mod:shift, @mouseover] CCspell; CCspell
This macro will cast your crowd control spell at your mouseover target if you're holding down shift. If you aren't holding down shift, it will cast your crowd control at your regular target. The '/stopcasting' at the front will cause you to stop casting whatever spell you're already casting so you can crowd control faster, since it can be a time-sensitive action.
There are tons of things that can be done with macros, and wowpedia is a great place to start learning how. Their Making a macro page really helped me.
All code in the images comes from projects that I've worked on. Now, enjoy this opening from a 90s show!