Microsoft KB Archive/168200

= Saving Worksheet in MS Excel 2.1 Format May Change Data =

Article ID: 168200

Article Last Modified on 5/8/2002

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q168200



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.

When you save a worksheet in this format, the whole numbers are reduced in increments of 65,536 until they are less than 65,536.

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.



RESOLUTION


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: kbprb KB168200

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.