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.
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 : xlloadsave
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb