I've been working on some widely distributed Excel projects at work. This type of scale is something I haven't had to deal with before. During development I worked hard to make sure the files were Excel 2003 and 2007 compatible. We made a conscious decision to not work towards being compatible towards other versions of Excel on Windows machines, or any Mac version, not to mention Linux or any other Excel alternatives. The biggest limiter was the need for macros in the file. Out of hundreds of users, we've had only a handful of compatibility issues.
The first and most frequent we have come across is users still on Excel 2000. Really it's only about 5 users, but it got me thinking. Excel 2000 hasn't been supported by Microsoft since 2005 (if memory serves). Two new versions have been released since. That got me thinking, what keeps organizations from upgrading? For a lot of the organizations we work with, it is cost. This particular file is used purely by non-profit orgs. In fact, I'm actually surprised that only a handful are still on Excel 2000.
We also had a couple of users on Excel 2008 for Mac. These users can't really be faulted much. Microsoft crippled this version and completely left out VBA. No support at all. I can't imagine what they were thinking on that one.
We've had one case of a Linux user opening the files in OpenOffice, but with no luck.
With all of this, I've come to the conclusion that with Excel files that contain macros you cannot cater to everyone. It's just not possible. The exceptions above cover most of the computing world, but there are also users on GoogleDocs, Zoho, and countless other office productivity suites. So, after much thought I've decided to only worry about complying with Excel 2003 and 2007 when I have to and sticking to Excel 2007 when possible - for macro files only.
The reality is that Microsoft is going to start feeling more of a hit on their market share in the coming years. Feature sets for all competitors will grow and compete with Office. And worst yet, there really aren't any standards for productivity software other than file type. And there definitely aren't any standards for the automation aspect.
I've drawn my line, and will continue to move that line as new versions of Office are released. It's a decision that needed to be made. But what next? What are you doing to provide compatibility?