Excel 97 bug update



Tip
PC World Editor Robert Thirlwell drew attention to a little-known bug in Excel 97 in last month's editorial. If you missed last month's issue, here's a reminder of the problem.
If you create a worksheet in Excel 97 which contains values greater than 65,535 and then save the worksheet in Excel 2.1 format, you'll be in for a nasty surprise next time you open the file. You'll notice, for example, that the value 65,536 has mysteriously changed to 0; 65,537 has become 1; 65,538 has become 2 and so on. If you happen to be drawing up a budget, you can well imagine that this type of behaviour can lead to health problems, especially if you don't notice what's happening for several days.
It seems that the source of the problem lies in the converter used by Excel 97 to save worksheets in the Excel 2.1 format. Microsoft plan to fix this in the first service release of Office 97 (probably available in August), but in the meantime, they suggest the following workarounds:
1. Do not save a worksheet in the Microsoft Excel 2.1 format.
2. 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 in the worksheet whose value is greater than or equal to 65,536.
This works because cell values derived from formulas are not corrupted by the converter.
We don't want to overstate the significance of this bug. Negative values and values that contain decimal points are not affected. And the problem doesn't occur if you save your worksheet in a format other than Excel 2.1. Granted, Excel 2.1 is very old hat, and most users will have no call to use it. But if you're looking for an older format to act as "lowest common denominator" between offices with divergent software, don't use Excel 2.1!
If you're interested in learning more about this problem, an article has been posted in the Knowledge Base at http://www.microsoft.com/kb/articles/q168/2/00.htm.
- Neville Clarkson

Category: Spreadsheet
Issue: Jul 1997
Pages: 159-160

These Web pages are produced by Australian PC World © 1997 IDG Communications