Excel 2007 Compatibility Checker
This is a pretty cool new feature in Excel 2007 that I think will pay some dividends to people.
The main intention is to ensure that users working in 2007 files are notified when they’ll lose functionality by saving back to prior versions. This is a good thing, of course, but admittedly, it does present itself in a cryptic way under the wording “Minor loss of fidelity”.
One of the really great things in it though, is the fact that it checks for external links when saving. Of course, in previous versions it was painful to do this. You either had to write code to do it for you, go browse all the named ranges (Insert|Name|Define), or download Jan Karel’s Name Manger utility to get an easy to read interface. Needless to say, most people didn’t bother looking at this kind of thing on a regular basis.
Of course, we all know the issues that this can cause. If your settings were automatically set to update links, then you’d never even know you were doing it. The values in your spreadsheet could be pointing somewhere else and updated without your knowledge.
I have to admit that since I’ve been using Excel 2007, I’ve now come across 5 workbooks that have had this issue. Here’s the message you get:
This nice little box is provided as soon as you save the file. It’s also worth nothing that you can uncheck the box to avoid this message if you know you are using links to external files. As should be, this setting is maintained on a workbook by workbook basis meaning that you won’t (can’t) accidentally turn it off for all workbooks.
At any rate, a quick trip into Excel 2007′s Name Manager for this file yielded me the following:
As you can see, this link had been carrying forward in the file for over 4 years! (It is a working paper that we create a new copy of every month, preserving our historical data.) Fortunately the link was innocuous, as have been all of the links I’ve found so far, but it does have the serious potential for problems. Suffice it to say that I’m cleaning these up as I find them!
on July 11th, 2007 at 10:01 pm
I dont like this feature and am wondering if you know of any way that I can turn it off. That’s because I am one of the early adopters of Excel 07 in my department where everyone else is using Excel 03. So every file I want to save will have this message although I want to save it back to the 2003 version.
on July 11th, 2007 at 10:19 pm
Well, that shouldn’t be quite true…
To be honest with you, I don’t know if there is a way to turn it off, nor would I encourage you to do so. Because many features in 2007 are built on a different base, they just won’t work with 2003 files. Colours are a big one, as they have been “redesigned” with tints and shades. I even got a message that the pivot table I created today could not be saved into the 2003 format at all. Conditional formatting is another one.
I did say “not quite” at the beginning though, and here’s why. I’ve been able to work in files and save back to 2003 without any issue whatsoever. So basically it seems to only prompt when you do actually have something that is an issue. It’s not just trying to irritate you for fun.
This feature is an integral part of backwards compatibility checking. I’m not saying for 100% certainty that it can’t be turned off, as I don’t know for sure, but I suspect that is the case.
If you get the message on every single file, then you are obviously making use of the new features, which is great. To avoid the irritation though, I’d either remove temptation and go back to 2003, or potentially download the patch that lets 2003 use the openXML file formats. I dont’ know for sure that the latter would solve the compatibility issues, but it would be worth a shot.
on July 11th, 2007 at 10:21 pm
Just to clarify… I’m not sure if it can be turned off globally. The message box itself has a flag to turn off checking on the individual file. I still don’t recommend doing that though.
on July 11th, 2007 at 10:51 pm
Thanks. I think I will go back to Excel 2003 for the time being until Excel 2007 is more widely used.
on September 24th, 2007 at 9:02 pm
we get this error and are not doing anything differently to what we did in office 97.
We open our invoices, which have an add in to do some updates, I believe they link to other sheets too – which is what we want. Then you save it and it comes up with that message box.
We just want to do its thing, without us clicking ok.
on March 7th, 2008 at 5:36 pm
When compatibility checker runs for a workbook using 97% of the non-gray, black or white colors presented by the default color grid, it presents information that is of marginal use, and it also presents INCORRECT information. See this link for more details: http://dearmicrosoftofficeteam.blogspot.com/2008/03/dear-microsoft-office-2007-team-please_03.html
on April 3rd, 2008 at 5:50 am
Just another point that I have found very annoying about the compatibility checker.
If you make a vba application, say a database frontend in Access that links to Excel for data import/export then Excel will be opened in the background to perform these actions but its window will not be visible, by design.
This should work without issue, but if the application attempts to save a file which has a “compatibility issue” then it appears to completley freeze because the Excel is waiting for the user to click on ok, but Excel is invisible!
This is a serious bug which I am puzzled as to how to work around without opening each affected file manually in Excel beforehand and saving them before running them through my Access frontend.
on May 14th, 2008 at 9:51 am
I am having a similar problem (compatibility checker running in background and appearing to freeze the system). Is there any way to switch off the checker globally so this no longer occurs?
on May 28th, 2008 at 11:57 am
I am having an issue with Excel 2007 saving Execl97-2003 files. The workbooks are about 5-6 megs and if we have a few open at once and try to save them, the system hangs(the cpu utalization pegs at 100%). Any ideas on what’s causing the system to freeze up? Apparently this didn’t happen in Excel 2003, so I am thinking it’s a setting or something.
on June 8th, 2008 at 11:05 pm
It would be nice if in the Excel options they had a way to turn off certain compatibility checking. I too was an early adopter with Excel 2007 and am forced to save in 2003 for co-workers. Since I use colors in almost every worksheet–particularly the header row–it’s frustrating that I get the compatibility error just because the colors are off. How can the colors not be compatible?!
What I would prefer, actually, is an “Excel 2003 Color Scheme” so that I could use that by default. I happen to like those colors anyway. This would eliminate the error 99% of the time, leaving the 1% for when I actually do have a backwards-compatibility issue I should be concerned about.
Nevertheless, I wish I could tell Excel to ignore compatibility checking when it is a “minor formatting issue”
on July 11th, 2008 at 10:19 pm
I would also offer that if you are using automation to save a Excel 2007 file, to a Excel 2003 template, then the compatibility checker will probably be causing issues here as well.
The thing is – how would you turn it off in that situation??
Need to glabally disable it when using some forms of automation….
on September 23rd, 2008 at 10:04 pm
Cant somebody please comeup with a solution on how to PERMANENTLY switch off this annoying thing? I have deadlines and it is eating into my time budget!
on September 24th, 2008 at 7:35 pm
[...] Due to several comments about how much people hate this thing, I’ve pulled together a very rough, very simple add-in. It’s goal is to disable the Compatibility Checker as much as possible. [...]
on September 18th, 2009 at 1:59 pm
Please disregard my 9/17/09 comment. Apparently, I may not have been using it correctly, because today it’s fantastic! I no longer receive the Compatibility Checker popup.
Your ADDIN works perfectly for me. It works for the new exported Excel files that I create everyday from our database.
Thank you!
on June 10th, 2010 at 5:35 am
Is it possible to get the details – which cells have the problem? I have only style/format problems which are probably colour.
While it’s useful to know there are 30 incompatible cells, it would be even more useful to know which ones they are so I can modify them!
The checker must know, but where does it put the info? Can you get it to run in a verbose mode?
I guess that as MS considers them minor, they don’t bother giving you the info.
Dave
on April 6th, 2011 at 8:01 am
I consider the implementation of the Compatibility Checker to be partially buggy.
When opening an Excel document using COM Automation which is supposed to supress all (ALL) screen I/O the Compatibility Checker is displayed in the background.
This causes automated processes to get “stuck” and there is no known way to fix this. It is extremely annoying to say the least. The implementation of this feature should adhere to all standards.
on May 20th, 2011 at 6:48 pm
Compatabilty checker doesn’t catch VBA sorts created in 2007 and saved in 2003 .xls format. I created a 3 level sort
(originally used record macro for basic code). Worked fine in compatabilty mode. When the End User opened it in Excel 2003 they received a Run Time Error 9 (subscript out of range). Had to change the code to utilize 2003′s Key1, Key2, Key3, DataOption1,2,3 etc. It would be nice if the “Compatabilty Checker” caught the things that mattered.
on May 24th, 2011 at 12:00 am
[...] received several comments on my blog that people dislike Excel 2007′s Compatibility Checker, and would like to turn it off [...]
on May 24th, 2011 at 2:58 pm
John,
I don’t anticipate that it will ever deal with VBA compatibility. We’ve always had to do our own conditional compilation in code if that is an issue.