XL97: Saving Worksheet in MS Excel 2.1 Format May Change Data

ID: Q168200

The information in this article applies to:

• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, if you save a workbook in the Microsoft Excel 2.1 Worksheet format, numbers in the worksheet may be changed. Specifically, numbers may be reduced in increments of 65,536. Note that you may not notice this problem until you close and reopen the worksheet.

CAUSE

This problem occurs when the following conditions are true:

• The worksheet contains whole numbers that are greater than or equal to 65,536.

  -and-

• The whole numbers are not returned by formulas.

  -and-

• You save the worksheet in the Microsoft Excel 2.1 Worksheet format.

This behavior occurs because of a problem in the Microsoft Excel 2.1 Worksheet converter that is used by Microsoft Excel 97 to save worksheets in the Microsoft Excel 2.1 Worksheet format.

NOTE: Negative values and values that contain decimal points are not affected by this problem. Also, this problem does not occur in earlier versions of Microsoft Excel.

WORKAROUND

To work around this problem, do either of the following:

• Do not save a worksheet in the Microsoft Excel 2.1 Worksheet format unless it is absolutely necessary. Instead, save the worksheet in the Microsoft Excel 3.0 or 4.0 Worksheet format or in one of the Microsoft Excel Workbook formats. In any case, always save a copy of the worksheet in the Microsoft Excel Workbook format.
• You can also save a worksheet in the Lotus 1-2-3 WKS format, or in the Symbolic Link (SYLK) format. These formats are supported by all versions of Microsoft Excel and by many other programs.
• If you must save a worksheet in the Microsoft Excel 2.1 Worksheet format, add an equal sign (=) to the left of each whole number that is greater than or equal to 65,536.

  For example, if a cell contains the following value

        100000
  

  change the cell to the following:

        =100000
  

  The number still appears the same in the worksheet. Because the value is returned by a formula, it is not changed when you save the worksheet.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows. This problem was corrected in Microsoft Excel 97 for Windows, Service Release 1.

MORE INFORMATION

In Microsoft Excel 97, you can save a workbook or worksheet in many different formats by clicking Save As on the File menu and clicking a file format in the Save As Type list.

The problem described in this article occurs only if you save a worksheet in the Microsoft Excel 2.1 Worksheet format. This format, which is for a version of Microsoft Excel that is several years old, does not support many of the features in Microsoft Excel 97 workbooks. Unless you are using another program that requires the Microsoft Excel 2.1 format, Microsoft strongly recommends that you do not save worksheets in this format.

Additional query words: XL97 XL21 2.x 2.1d BIFF2 65535

Keywords          : xlloadsave 
Version           : 97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: November 4, 1998