Excel 2007 compatibility issues occur due to a significant change in the underlying design of file formats and new features. Excel version transitions are problematic; Excel 95 to 2003, Excel 2003 to 2007 and counting.

It is particularly challenging  for developers and for users when there is a paradigm shift of this nature.

Excel Ribbon is that shift for the ‘Menu paradigm’ and also the Office Open XML file format in comparison to the Binary Interchange File Format (BIFF).

Here we will find practical solutions to the most common Excel 2007 compatibility issues…

Excel 2007 Compatibility Issue # 1 – I cannot open an Excel 2007 file in older versions

If your end users with whom you exchange files are still running an older Excel version and you want to send them files; you can do one of these things…

• Save your Excel files as a previous version. Do it this way: Office Button>Save As>Other formats>Save as type>Excel 97-2003 Workbook
• Set Excel 2007 to run on compatibility mode all the time. Do it this way: Office Button>Excel Options>Save>Save workbooks>Excel 97-2003 Workbook

If you are running Excel 2003 or an older version and you want to be able to receive files created on Excel 2007;  ask your file providers to use the above steps.

Another case is when you want to be able to open files in the new XML format. To be able to do it, you must install the Office 2007 Compatibility Pack. Go here (for instance, so you can see the formatting features that are unique to Office 2007).

By applying the above strategies, you will stop having unrecognized Excel files. You won’t experience the frustrating event when nobody in your surroundings has Excel 2007 to make the conversion easily. Additionally, you avoid causing struggle in your end users and customers. When people notice you care about them, you gain influence and recognition.

Now, let’s discuss the solutions when you have the…

Excel 2007 Compatibility issue # 2 – I can open the Excel 2007 file but I lost functionality

It is a natural process of new software versions that the ‘old version users’ community doesn’t disappear immediately (romantic users may remain with a version for the rest of their life);  so again, in the transition , struggle will exist.

The good news is that Excel 2007 alerts you about incompatible formatting or features that won’t load or load degraded in older versions…

Excel 2007 compatibility checker states… “The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format.”

Here is a very important difference worth talking about. You have two types of  loses…

Significant loss of functionality

These losses occur when one or more functions in your workbook are not available in earlier versions of Excel.

Examples: non existing function, unsupported nesting levels in a formula, etc.

Minor loss of fidelity

These losses occur when some cells or styles in your workbook contain formatting that is not supported by the selected file format.

For example: cell color, table styles, etc. These formats will be converted to the closest format available.

Here are some strategies to use so you make your files ready to be opened in Excel 2003 and older versions

1) Don’t use 2007 Functions

Excel 2003 cannot recognize Excel 2007  functions the same way Excel 2007 is not able to recognize the functions to come in future versions.

So it will be this formula =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) for Excel 2003. When recalculated in earlier versions, these functions will return a #NAME? error instead of their current results.

Here are some new formulas not supported: SUMIFS, COUNTIFS, AVERAGEIFS, etc.

2) Don’t use additional rows/columns

Data beyond 256 (IV) columns by 65,536 rows will not be saved.

And even worse…

Formula references to data in this region will return a #REF! error.

3) Don’t use unsupported format

Yes, the colors will be converted to the closest ones, but what looks good in Excel 2007 may not be reflected in your Excel 2003 end users’ screens. Be cautious when you use Colors, Styles, etc

Take a quick look at the color palettes supported by both versions:

4) Don’t use unsupported tools

Some features will not appear in the old version. This is a serious problem if you gain advantage from such features, your 2003 end users will have that advantage missing.

This is the case with Conditional formatting new features such as data bars, color scales, or icon sets.

5) Don’t use unsupported capabilities

I recently wrote a long IF formula in Excel 2007. The formula was developed for an Excel 2003 user; guess what? I saved it as 2003 format. I lost the formula I spent an entire morning working on. I got a #VALUE!

Excel 2003 converted this formula…

=IF(MID(A2,1,1)<>”0″,A2,IF(MID(A2,2,1)<>”0″,RIGHT(A2,LEN(A2)-1),IF(MID(A2,3,1)<>”0″,RIGHT(A2,LEN(A2)-2),IF(MID(A2,4,1)<>”0″,RIGHT(A2,LEN(A2)-3),IF(MID(A2,5,1)<>”0″,RIGHT(A2,LEN(A2)-4),IF(MID(A2,6,1)<>”0″,RIGHT(A2,LEN(A2)-5),IF(MID(A2,7,1)<>”0″,RIGHT(A2,LEN(A2)-6),IF(MID(A2,8,1)<>”0″,RIGHT(A2,LEN(A2)-7),IF(MID(A2,9,1)<>”0″,RIGHT(A2,LEN(A2)-8),IF(MID(A2,10,1)<>”0″,RIGHT(A2,LEN(A2)-9),IF(MID(A2,11,1)<>”0″,RIGHT(A2,LEN(A2)-10),IF(MID(A2,12,1)<>”0″,RIGHT(A2,LEN(A2)-11),IF(MID(A2,13,1)<>”0″,RIGHT(A2,LEN(A2)-12),IF(MID(A2,14,1)<>”0″,RIGHT(A2,LEN(A2)-13),IF(MID(A2,15,1)<>”0″,RIGHT(A2,LEN(A2)-14),IF(MID(A2,16,1)<>”0″,RIGHT(A2,LEN(A2)-15),IF(MID(A2,17,1)<>”0″,RIGHT(A2,LEN(A2)-16),IF(MID(A2,18,1)<>”0″,RIGHT(A2,LEN(A2)-17),IF(MID(A2,19,1)<>”0″,RIGHT(A2,LEN(A2)-18),IF(MID(A2,20,1)<>”0″,RIGHT(A2,LEN(A2)-19),IF(MID(A2,21,1)<>”0″,RIGHT(A2,LEN(A2)-20),IF(MID(A2,22,1)<>”0″,RIGHT(A2,LEN(A2)-21),IF(MID(A2,22,1)<>”0″,RIGHT(A2,LEN(A2)-21),IF(MID(A2,23,1)<>”0″,RIGHT(A2,LEN(A2)-22)))))))))))))))))))))))))

Into this one… #VALUE!

The fact is that some formulas have more levels of nesting than are supported by the selected file format. Formulas with more than seven levels of nesting will not be saved and will be converted to #VALUE! errors.

Here are some things you must be aware of…

• 7 vs 64 nesting levels
• 1,024 characters vs 8,192 character length of formula contents
• 30 vs 255 arguments in a function

6) Run the file under compatibility mode and attach it to the recommendations or run the compatibility checker

When you are working in 2007  mode you may not be aware that you have inserted a lot of non supported features.  To avoid this, you should run the file in compatibility mode.

Do it this way: Office Button>Excel Options>Save>Save workbooks>Excel 97-2003 Workbook

If your file is run in 2007  format you must perform a check with the compatibility checker before you send the file to 2003 users.

Excel 2007 Compatibility conclusions

If your file doesn’t have extraneous features for Excel 2003 and if you save it in 2003 format, there is nothing to worry about, your end users will open the file easily as normal. Your file will be fully functional

Since you can install both versions, I recommend you do it; so you always check compatibility issues before sending an email or before losing hours of work like happened to me with unsupported nesting levels.