I notice it a lot in how the bike handles. I feel like and know that I can throw a lighter bike around a bit more for faster handling. I'm seeing this firsthand with my cyclocross bike. I thought I wanted a heavier bike that could take abuse, but as I race it more and more, I know it's not the right bike for me - mostly due to it's weight via handling. I'd feel a lot more comfortable on a lighter bike that I could muscle around a bit more.
Same with my road bikes, for commuting, a heavy bike is fine and preferred, but I like a lighter bike for more aggressive riding.