XL2000: Using Excel 2000 Files with Earlier Versions of Excel
Microsoft Excel 2000 includes a feature that allows you to save a workbook in a dual format. The format is called "Microsoft Excel 97-2000 & 5.0/95 Workbook." This article explains how the format works, why you would want to use it, and includes a list of potential problems that you may encounter.
What Is the Dual Format and How Does It Work?
The dual format is a special kind of file format. A workbook saved in this format contains two sections: one section that is written in the Microsoft Excel 5.0/95 file format, and one section that is written in the Microsoft Excel 97/2000 file format. Using Excel 2000, you can continue to work in a workbook saved in the dual format without losing any features or formatting unique to this version. If you open the workbook in Excel 5.0 or 7.0 (95), you will receive a warning that recommends you open the file read-only. If you ignore the recommendation and save the workbook in an earlier version of Excel, features and formatting that are available only in Excel 2000 and Excel 97 are lost.
When you save a file in the dual format in Microsoft Excel 2000, the following events take place:
1. The Excel 97/2000 section of the workbook is written.
2. The Excel 5.0/95 section of the workbook is synchronized with the Excel 97/2000 section, so that they contain the same values.
3. The Excel 5.0/95 section of the workbook is written.
4. The workbook is saved.
When you open the workbook in Microsoft Excel 2000, the Microsoft Excel 97/2000 section of the workbook is read. If you make changes to the workbook and then resave it, the changes to the Microsoft Excel 97/2000 section are copied to the Microsoft Excel 5.0/95 section of the workbook, and the workbook is saved.
When you open the workbook in Excel 5.0 or 7.0, only the Microsoft Excel 5.0/95 section of the workbook is read. If you make changes to the workbook and save it, the Microsoft Excel 97/2000 section of the workbook is lost. This behavior occurs because earlier versions of Excel are not aware of the Excel 97/2000 section of the workbook.
A workbook stored in the dual format may be up to twice as large as a normal workbook. This is because data stored within the workbook is stored twice, once in each section of the workbook.
To save a workbook in the dual format in Excel 2000, follow these steps:
1. On the File menu, click Save As.
2. In the Save as type list, click "Microsoft Excel 97-2000 & 5.0/95 Workbook."
3. Change the file name if you want, and then click Save.
Why Would I Want to Use the Dual Format?
The dual format is useful when a mix of users who run different versions of Excel all need to be able to view the same workbook.
If users running Excel 97 open a workbook that is saved in this format, they will not notice any unusual behavior. They can make changes and save the workbook just like any other workbook.
If users running Excel 5.0 or 7.0 open a workbook that is saved in this format, they are prompted to open the workbook as read-only. If a user chooses not to open the workbook as read-only, and then saves the workbook, the Excel 97-2000 section of the workbook is lost. Features that are specific to Microsoft Excel 2000 are lost because earlier versions of Excel do not support these features. Also, if worksheets in the workbook contain more than 16,384 rows of information, data in rows below row 16,384 are lost.
What Potential Problems Might I Encounter When Using the Dual Format?
You may encounter the following problems when you use this format:
? If anyone running an earlier version of Excel opens a workbook saved in the dual format, and then saves it, the Excel 97-2000 section of the workbook is lost.
Because of this, Microsoft recommends that you keep workbooks saved in this format as simple as possible. The Microsoft Excel 97-2000 & 95-97 Workbook file format preserves all worksheet and chart data, formatting, macros and other features available in Excel 2000. If you save an Excel 2000 workbook in another file format, some of the formatting, data, and other features might be lost. For example, if you save an Excel 2000 workbook in Excel 97, the following features are not preserved:
? OLAP PivotTable and PivotChart reports appear as read-only.
? AutoRefresh, column formatting, filtering and sorting don't work with external data ranges.
? Indented PivotTable reports change to nonindented layout, but maintain character and cell formatting.
? PivotChart reports appear as regular reports. Multiple level category labels are modified and value axis display units are converted to literal values.
? ScreenTips for hyperlinks are not saved.
Note that you can protect the workbook with a password, so that if you share your workbook with users who have earlier versions of Excel, they cannot save changes to the workbook.
? Workbooks saved in the dual format are larger than workbooks that are not saved in this format. This may cause problems when you save or store such workbooks on floppy disks.
Microsoft recommends that you do not directly save a workbook in the dual format on a floppy disk. Instead, save the workbook to the hard disk, close the workbook, and then copy it to the floppy disk.
For more information about the dual format, click Microsoft Excel Help on the Help menu, type About using Microsoft Excel 2000 files with earlier versions of Excel in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
? Microsoft Excel 2000 Standard Edition