Hands on: Checking for compatibility

The differences between Excel 2007 and previous versions of the spreadsheet are so extensive that Microsoft has included several features to bridge the gap.

Not only does Excel 2007 look different but the specifications have been greatly improved, the functions and features expanded and, most significantly, the basic file format has been changed and several other formats are now available.

The people with the advantage here are those who have always navigated with keyboard shortcuts, which remain the same, and those who are using Excel for the first time and so have nothing to unlearn. But what of all those users who like Excel the way it has always been?

Fortunately, all their existing workbook files will be recognised by Excel 2007 and displayed as intended. The latest version carries forward the features and functions of earlier versions and has just added to them. The main Excel file expanded from 9MB to 17.5MB.

Some harshly call this Microsoft bloat, but today’s PCs with faster processors and more memory can take advantage of the extra features now available. Among the various ways in which an Excel 2007 file can be saved is the familiar .xls format. However, if that file includes features that were introduced with the latest version then Excel will switch into Compatibility Mode.

It will advise you of this in the Title Bar. A file you have saved as myfile.xls, or described as an Excel 97-2003 Workbook, will be shown as ‘myfile.xls [Compatibility Mode]’. More than that, the Compatibility Checker will start and warn you of the problem (see the attached PDF).

You may have used a newly introduced function, such as COUNTIFS, or gone beyond the limit of rows or columns of earlier versions, or used a deeply nested formula that is allowed in Excel 2007 but not in earlier versions. The worksheet size has expanded from 65,536 rows x 256 columns to 1,048,576 rows x 16,384 columns.

Previously you were limited to seven nested levels of functions. That has been increased to 64. There were 16 Undo levels, and in Excel 2007 there are 100. Suppose you want to run the Compatibility Checker without saving a file. No problem. Click on the Office button and choose Prepare.

At the end of the ‘Prepare the document for distribution’ list you’ll find an option, Run Compatibility Checker. Even better, there’s a button at the bottom of the Compatibility Checker message box named ‘Copy to New Sheet’. Click this and an extra worksheet is added to the workbook with Compatibility Report on the tab.

This lists the number of occurrences of each type of incompatibility. The Compatibility Checker message box also has an option should you want the Checker to run every time you re-save as an .xls file, not just the first time.

Other formats
Beyond an .xls file, Excel 2007 lets you save a workbook in two dozen other formats. If you download add-ins there are even more. But the main Excel 2007 format is .xlsx and there are two variations, .xlsm and xlsb. Under Save As options these are Excel Workbook, Excel Macro-Enabled Workbook, and Excel Binary Workbook.

The default Excel 2007 .xlsx format is a zip file. In fact, if you change the name of a file called myfile.xlsx to myfile.zip you can right-click on it, choose Open and see the folders, and files within the folders, that are saved. Unlike an .xls file, an .xlsx file saves each worksheet as a separate file, and there other files to store printer settings, styles, and related information.

Close the zip file, rename it to myfile.xlsx, and you can open it again in Excel with no harm done.
Incidentally, if you are using Excel 2000, 2002 or 2003 and a friend sends you an Excel 2007 .xlsx file there is an add-in to help translate.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!