Digithoughts

While most of the analytics team has the 2007 version of Microsoft Office, it is not uncommon for client organizations, or individuals within those organizations, to have the outdated 2003 version of Excel. Because of this, it is important that Excel client deliverables are 2003-compatible so that users with old version of the software can still open and view their reports. What follows is a series of tips for making sure that documents written in Excel 2007 can be opened and viewed in Excel 2003 without any problems.

Excel 2007 saves files using a different file format that Excel 2003, using the .xlsx file extension instead of the traditional .xls file format of Excel 2003 and prior. Excel 2003 uses the same file formats as older versions of Excel. going back to 1997, and this, combined with the dwindling user share of version of Excel older than 2003, means there is rarely a reason to support Excel versions other than 2003 and 2007. There is a plug-in for Excel 2003 that allows it to open, view, and interact with files with a .xlsx file extension, although some 2007-only features may not work correctly and any changes can only be saved in .xls format. Excel 2007 can also save files in .xls format, and has the ability to convert documents from .xls to .xlsx format. Which of these formats is preferable depends on the relative usage of different versions of Excel within the client organization. All else being equal, the .xlsx format is preferable, as it produces smaller files, and because the .xls format does not support all of the features available in the .xlsx format.

Testing for Compatibility

When saving a document in 2003 format, by default Excel 2007 should perform a compatibility check to see if there are any issues that would prevent the spreadsheet from being saved as a well-formed document that can be opened in Excel 2003. This function will alert about the majority of data integrity issues and should always be used. However, there are a number of minor issues that it does not detect that will prevent a spreadsheet from working correctly in Excel 2003.

Even in the presence of critical errors, it is possible for Excel 2007 to save the spreadsheet in a .xls file, although such a spreadsheet will usually not work correctly in Excel 2003, and sometimes not even in 2007. Some compatibility issues are resolved by removing data from the spreadsheet; the resulting file, when opened, will differ from the original file that was saved. Other issues result in a malformed .xls file containing 2007-only features. Excel 2007 is capable of opening these files and processing them correctly, but Excel 2003 is not. Because of this, it is important not to turn off the default compatibility-checking feature in Excel 2007. If this feature is disabled, Excel 2007 will save files which will not open correctly in Excel 2003.

Common Compatibility Issues:

1. Column Limit

Problem
Spreadsheets in Excel 2003 may only contain up to 256 columns (up to column "IV"). Spreadsheets in Excel 2007 may contain up to 65,536 columns. Columns beyond the 256-column limit will not be saved in a .xls spreadsheet, and columns beyond the limit in a .xlsx file opened in Excel 2003 will not be accessible.

Diagnosis
Excel 2007 will warn about this issue before saving.

References to a cell that has been removed or is inaccessible will report a #REF! error.
When opening a .xls file in Excel 2007, or a file of either type in Excel 2003, the spreadsheet will not scroll horizontally past the column IV. Data that should be located beyond this row will be inaccessible.

Solution
To access the data, you will need to open a .xlsx version of the file in Excel 2007. In order to make the data accessible in Excel 2003, you will need to restructure the spreadsheet to not contain more than 256 columns. This usually means arraying data vertically instead of horizontally, or splitting a single wide tab across multiple tabs. Note that Excel 2003 and Excel 2007 have the same row limit, so there will be no compatibility issues with laying the data out vertically.

2. Conditional Formatting

Problem
Conditional Formatting is significantly more powerful in Excel 2007 than in Excel 2003. .xls files are not capable of containing the new conditional formatting features, and Excel 2003 is not capable of rendering the new conditional formatting features if they are saved in a .xlsx file. The conditional formatting changes are rather broad:

1. In Excel 2003, conditional formatting is a property of a cell or a cell range. In Excel 2007, conditional formatting rules are separate objects, and have one or more cell ranges to which they apply. If two or more conditional formatting rules apply to two different cell ranges that partially overlap, this cannot be saved in .xls files or displayed properly in Excel 2003.
2. In Excel 2003, the conditional formatting for a range of cells may not contain more than three rules with associated formats. In Excel 2007, the number of rules is unbounded (although spreadsheets often start freezing up around a hundred or so).
3.  In Excel 2007, it is possible for more than one conditional formatting rule to apply at the same time. In Excel 2003, each cell range may only display up to one of its conditional formats at a time.
4. There are a number of conditional formats new to Excel 2007 that are not available in Excel 2003, such as icon sets and gradients. Any format more complicated than applying a single, specific format to a cell based on a single true/false condition is not available in Excel 2003.

Diagnosis
Excel 2007 will warn about all of these issues when saving a file in .xls format. The conditional formats will not save properly in a .xls file and so cannot be viewed, even in Excel 2007, in a file of that format. Excel 2003 cannot display these types of conditional formats when they are saved in a .xlsx file.

Solution
Conditional formatting issues are one of the more benign compatibility issues because they degrade gracefully, i.e. the spreadsheet will lose the conditional formatting, but no other functionality of the spreadsheet will be impacted, and the default (non-conditional) formatting will be used. If the default formatting is sensible the spreadsheet is generally still usable despite losing conditional formatting, with no adverse consequences. The exception is if data is being hidden by conditional formatting. It is not recommended to hide data by using conditional formats, even in Excel 2007, because the data is still present and may be copied to the clipboard unintentionally. The better option is to use IF statements to replace data with the empty string.

The most important constraint is #2, only three rules and three formats may be applied conditionally in Excel 2003. If conditional formats are important for the document, pare down to the minimum necessary, and use fancy but non-functional conditional formatting sparingly or not at all, so that no more than three formats apply to any given cell. Note that rules for conditional formatting are allowed to be long and convoluted, so if two conditions result in the same formatting, they can be combined into a conditional formatting rule by using the OR function in the condition.

Constraint #1 is easy to violate accidentally if there is more than one type of conditional formatting on a single page, because conditional formatting rules tend to breed like rabbits. Copying and pasting data will copy conditional formats as well, and many spreadsheets will end up littered with accidental conditional formats in unrelated cells. Use the Manage Rules tool to check the cell ranges for each conditional formatting rule, and delete any extras. If two conditional formats really must be applied to overlapping ranges, then split each conditional formatting rule into two identical rules applied to different cell ranges: one rule for the cell range in the overlap, and one for the non-overlapping cells.

Constraint #3 can sometimes be addressed by combining the two applicable formats into a single format. For example, if one rule makes the cell have a blue background, and another makes it have yellow text, then a third rule can be added whose condition is the AND of the previous rules and whose format is both a blue background and yellow text. Because this adds a new formatting rule, this will violate constraint #2 unless these two rules are the only formatting rules being applied. Note that this new rule must be placed above the other two rules so that it is checked first in order to be applied successfully.

There is no solution for Constraint #4. These formats will not display in Excel 2003.

3. Formula Issues

Problem
Some formulas are not available or behave different in Excel 2003 than they do in Excel 2007.

• The IFERROR function is not available in Excel 2003. Excel 2007 can save the IFERROR formula into a .xls file, and process correctly a file that contains this formula. Excel 2003 will not correctly process the IFERROR formula in a .xls or .xlsx file.
• Excel 2003's ability to parse dates is not as robust as Excel 2007. Some dates that Excel 2007 can parse, manipulate, and format as a date will be treated as a string in Excel 2003.
• Excel 2003 may only nest formulas inside of each other to a maximum depth of 7. Excel 2007 allows nesting to a depth of 63.

Diagnosis

• Excel 2007 will warn when saving a .xls document with the IFERROR formula. Trying to use the IFERROR formula in Excel 2003 will result in a #VALUE! error.
• Excel 2007 will not warn that a date is being parsed in Excel 2007 that cannot be parsed by 2003. The only diagnosis is to open the file in Excel 2003 and see if formulas manipulating dates calculate and display correctly. The range of possible issues is broad, depending on how the date is being used and what formats they are in. Attempting to use the date in a function that does not recognize it as a date will generally result in a #VALUE! error. Attempts to format it will display the original date format. Other errors are possible.

Solution

• The IFERROR formula can be replaced by using the ISERR or ISERROR formula inside an IF statement. This is more verbose, as the calculation that may generate an error must appear twice, but has identical functionality.
•  Some date calculations are unnecessary. If the date is being reformatted, either pass it through in its original format, or use string manipulation functions instead of format functions. If a date is being looked up, sometimes calculating the date can be replaced with looking it up from a table with the Index function. If the date must be understood as a date for a necessary calculation (such as using the EOMONTH function), then you must use string manipulation functions to change the date into a format that Excel 2003 can parse.

4. Colors

Problem
xls files can only store 8 distinct colors within a single file, while the .xlsx format does not have a color limit. Excel 2003 can only display 8 colors, even from a .xlsx file that contains more. It is possible to customize which 8 colors are stored, and the range of possible colors is the same in Excel 2007 as in Excel 2003. Excel 2007 will attempt to change colors in a .xls file to the closest color in that file’s palette

Diagnosis
Excel 2007 will warn when saving a .xls file if it has to change one of the colors. Opening the file in Excel 2003 will result in changed or missing colors.

Solution
Consolidate similar-looking colors in the same file to be the same color, in order to avoid wasting color slots on redundant shades. It’s best to select an 8-color palette beforehand, and use only those colors in the document. Selecting a color range for a document in Excel and only using colors in those range will ensure the correct colors are saved in the file, with no errors.

5. Graph Styles

Problem
A handful of new graph styling options are unavailable in Excel 2003, and cannot be saved in a .xls file. These are the fancier graph options such as beveled edges on bar graphs.

Diagnosis
Excel 2007 will warn when saving a file with incompatible graph styles to a .xls file. The fancy stylings will be missing when the file is viewed in Excel 2003.

Solution
This is another problem that degrades gracefully: While the beveled edges will be missing, the rest of the bar graph will display properly, including colors (subject to restrictions above), spacing, and borders. Other graph options will degrade gracefully as well. This problem can often be ignored, although it’s worthwhile to see how each graph looks in Excel 2003 to make sure it’s not too hideous without its fancy stylings.

Logan Gordon Web Analyst

Read more from the Analytics category. If you would like to leave a comment, click here: Comment or stay up to date with this post via RSS, or you can Trackback from your site.