Do You Need To Run Excel 2007 In Compatibility Mode?

What is compatibility mode and why is it used?

Once you've upgraded to Excel 2007 or started to use it from scratch and got the hang of the new tabbed navigation system you'll have started using Excel 2007's many new features including the greatly improved charting building, SmartArt, themes and styles and table formatting for enhanced presentation effects. Other new features include conditional sorting of data based on colours, greatly increased data sorting levels, and of course a massive increase in the number of rows and columns (Number of rows increased from 65,000 to 1 million).

And then there's the new Excel 2007 file format, with file extensions of .XLSX rather than the traditional .XLS ending. This means that if you want other people to use your Excel 2007 files or if you want to send your Excel files to others, then everyone needs to be using Excel 2007. And there's nothing worse than not being too sure who has which version. If you send your file to someone who only has Excel 2003 then, unless they have taken the trouble to download and install an Excel 2007 viewer, the file will not open. So if you're lucky they email you asking you what's this .XLSX file? If you're unlucky they just don't respond.

But there is a way round this - Compatibility Mode. If you configure Excel 2007 to always save in Excel 2003 format, then all files will be saved with the ending .XLS. If you then save an Excel 2007 file containing some of the new graphical enhancements such as SmartArt, a compatibility checker runs and warns you of a "significant loss of functionality" for listed items if saved in Excel 2003 format. The compatibility checker does have links and a help system to allow to you check compatibility details further if necessary.

In reality all the graphical enhancements such as SmartArt will save successfully in the older format but as images, and will not be editable as SmartArt. However Excel 2003 will display them successfully. If you save an Excel 2007 chart, it will be successfully saved in Excel 2003 format, and Excel 2003 will be able to edit it but only in the Excel 2003 chart builder.

You will lose the extra rows and columns though as compatibility mode switches back to 65000 rows by 256 columns, but generally speaking this should not matter to most Excel users. If you need to use the increased spreadsheet size then it's best to stick with the 2007 format.
Other areas to be careful of are pivot tables, using charting macros and working with the new cube functions as all these contain some advanced features Excel 2003 does not recognise. Also, Excel 2003 does not recognise the Excel 2007 AVERAGEIF function. However apart from these more specialised areas, COMPATBILITY MODE should be workable for most Excel 2007 users with an interest in keeping their work compatible with Excel 2003 users.

When Excel 2007 saves a file in Excel 2003 format it runs in COMPATABILITY MODE - you'll see these words in the top bar after the file name. The same happens if you open an Excel 2003 file in Excel 2007. The file opens without a problem with the words Compatibility Mode shown after the file name.

You may well wonder what the point of all this is. After all if you've gone to all the trouble of obtaining, installing and learning to use Excel 2007 why turn all its new features off? The answer is you only loose some. And most people don't need absolutely every new feature in Excel 2007.

The main benefit is you can continue to use many of the new features and still work with the new tabbed navigation system, use auto previews, styles, themes and charts. However you do revert back to the Excel 2003 number rows and columns. But honestly most Excel users can happily live with this.

With Excel 2007 open you can set it to Compatibility Mode by clicking the Office button and click Excel options on the lower right on the panel. In the Excel Options panel choose Saving in the left hand index. Then in the wider right hand side, under the upper heading Save workbooks, look for the heading "Save files in this format:" Just to its right click the pop down and choose "Excel 97-2003 workbook (.xls), and click OK to finish.

Alternatively rather than turn compatibility mode on you can save a specific Excel 2007 file in the older Excel 97-2003 format via the Save As command.

Compatibility Mode does allow Excel 2007 and Excel 2003 users to work together and is often used in organisations where staff are gradually introduced to Excel 2007, or where Excel 2007 users need to send Excel files to other users outside the organisation.

To really learn all about Excel 2007 and its many enhancements including use of compatibility mode, consider attending one of the many instructor lead training courses available.