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 value100000
change the cell to the following:
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.=100000
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
