I use Excel at work for at least a few hours a day, and I would class myself as an advanced user. I moved to Excel 2007 about 3 working days ago.
I do not enjoy the new layout. The logical reasons for the restructure are certainly sound, however suddenly things are not where one expects them. If, like me, you make use of lots of keyboard shortcuts then you will no doubt suffer at first. However, this is analogous to `texting' using a new phone and finding that the full-stop and space have been swapped. You learn quickly, especially if you are using the application a lot. In addition, Excel 2007 will interpret legacy alphabetical short-cuts in most cases.
The short term pain is small when weighed against the increased features in the new version. For me this is most notable in the presentation of data. Formatting options, especially conditional formatting, are much improved.
There are some _great_ ways to automatically represent cell values visually (without producing graphs). For example automated cell shading based on value.
Pivot-tables have changed a bit, and will probably give transition users some frustration. But again I think this will be short lived for any advanced users, who will quickly familiarise themselves.
Filtering too is changed, allowing filtering by colour. However, I find that it is harder to shortcut through the expanded options.
There are also a few handy time savers (eg 'remove duplicates') which will be appreciated by many users who might not create macros for automate tasks.
Compatibility with non-2007 users is the only caveat I would add. File formats have fundamentally changed - for the improvement of security and size (and maybe to encourage upgrades). If sharing and collaborate are important, then you will need to get everyone onboard to benefit.
Overall I think for any proficient and regular user the upgrade is worthwhile (pain vs payoff). For those who are not advanced users, pain of change may not be worth it for the new features. For completely new users I would definitely go for 2007.