Excel 2007: Ensure Compatibility with Previous Excel Versions

By David H. Ringstrom, CPA

Excel 2007 is going to affect you, whether or not you choose to upgrade. For instance, if you are a user of Excel 2003 or earlier, you can be stymied when an Excel 2007 user sends you a spreadsheet saved in the new format. Conversely, if you use Excel 2007, you must be cognizant of those still using earlier versions, so that you don’t send someone an unusable spreadsheet. Fortunately some simple steps will ensure equal access for all, no matter which version of Excel you’re using.

Try it now: A free 60-day trial of Office 2007 is available for immediate download. Note that you can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version. Or, try the online test drive.

Transition tools: Office Online offers an online command reference that shows where your favorite Excel 2003 commands now reside in Excel 2007. You can also download and install the command reference tool on your computer. Yet another option is to add the Get Started tab to your Excel 2007 ribbon — this puts numerous training aids right at your fingertips.

How to Ensure Accessibility

If you upgrade to Excel 2007, be assured that you’ll be able to open any spreadsheet that comes your way, but it’s Excel 2007’s new features that might cause problem in previous versions. As shown in Figure 1, Excel 2007 spreadsheets can span beyond 16,000 columns and 1 million rows — a significant increase from the previous 256 columns and 65,536 rows. In addition, other features such as new functions like IFERROR, SUMIFS, and AVERAGEIF , and data features like cell icons and databars are not backward compatible, regardless of the file format you choose.

Figure 1: Excel 2007 spreadsheets can be vastly larger than earlier formats.

 

Special Pricing: If you haven’t already done so, join the free Microsoft Professional Accounting Network. MPAN members can purchase a Microsoft Action Pack Subscription for less than the cost of a single Office license. In return you’ll receive a myriad of Microsoft software licenses — including ten copies of Office 2007 Enterprise — for use within your firm.

Excel 2007 enables users to save files in the traditional Excel 97-2003 format, among others, but some users will inevitably overlook this feature. Accordingly, anyone using Excel 2003 or earlier should install the free Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 file formats. This enables users to open many Office 2007 documents as long as the spreadsheets don’t exceed the 256 column and 65,536 row limits — Excel will truncate any data outside of these limits.

Stay Compatible

Excel 2007 does offer some helpful tools for upgrading and downgrading your documents:

• Compatibility Mode
• The Compatibility Checker
• The Convert command

As shown in Figure 2, Compatibility Mode appears at the top of your Excel 2007 screen when you open documents saved in the Excel 97-2003 format. Although the article Use Office Excel 2007 with earlier versions of Excel states that Compatibility Mode prevents you from using any enhanced Excel 2007 features, my experience was different. I found that I can readily use any Excel 2007 feature in an Excel 97-2003 document. However, I can confirm that Compatibility Mode automatically launches the Compatibility Checker whenever I save a file in the Excel 97-2003 format.

Figure 2: Compatibility Mode alerts you that your document not in the Excel 2007 format.

 

The Compatibility Checker, shown in Figure 3, is a tool that alerts you to features or data that will be lost by saving the file the Excel 97-2003 file format. Depending upon the severity of the issues, you might opt to save your work as an Excel 2007 document instead, or you can manually address the individual issues. A comprehensive discussion is available in the article Office Excel 2007 features that are not supported in earlier versions of Excel.

Figure 3: The Compatibility Checker identifies features and data incompatible with earlier versions of Excel.

 

Severity Rating: The Compatibility Checker rates issues as significant or minor. Significant issues should be addressed carefully, but you can typically disregard issues flagged as minor.
At this point you have several options:

• Click Continue to ignore the warnings and save your file. As long as the issues are minor in nature other users will be able to use Excel 97 through 2003 to open and modify the worksheet.
• Click the Find link for a given issue, which instructs Excel to select the affected cells.
• Click the Copy to New Sheet button. Excel adds a new worksheet to your file that comprises the entire report from the Compatibility Checker, along with hyperlinks for the affected cells, as shown in Figure 4.
  

  Figure 4: The Compatibility Report identifies incompatible data and features.

  
  
  
• Close the Compatibility Checker. Click the X in the upper right-hand corner to cancel saving the file in the Excel 97-2003 format.

You can launch the compatibility checker at any time by following these steps:

1. Click the Office button.
2. Choose Prepare.
3. Choose Run Compatibility Checker.

This enables you to confirm whether you’ve resolved all of the compatibility issues without having to save the file each time.

Default format: You can automatically save all files as Excel 97-2003. To do so, click the Office button, click the Excel Options button, and then choose Save. Change the Save Workbooks in this Format choice, and then click OK.

Convert Your Spreadsheets

Finally, you may notice a new Convert command sometimes appears on Office button menu. The Convert command is an alternative to the Save As command, but with a special caveat. Let’s say that you carry out these steps:

1. Open an Excel 97-2003 workbook in Excel 2007.
2. Click the Office button, choose Save As and then Excel Workbook.
3. Specify a name for the file, and then click Save.

At this point you have two copies of the file: the original 97-2003 format and the new Excel 2007 format. Conversely the Convert command provides different results:

1. Open an Excel 97-2003 workbook in Excel 2007.
2. Click the Office button, and then choose Convert.
3. Click OK on the warning prompt shown in Figure 5.
4. Click Yes on the conversion successful prompt. You must close and then reopen the converted file to gain full access to all Excel 2007 features.

At this point the original Excel 97-2003 file has been deleted and replaced with the Excel 2007 format file.

Figure 5: The Convert command upgrades your file to the Excel 2007 format and discards the Excel 97-2003 copy.

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.