Thursday, November 13, 2008

Excel prompts to save when no changes made

I have a large Excel workbook that is a catch-all for budgeting, time analysis & other miscellaneous items. When I would open the file to look at something, not change anything but simply close it, Excel would prompt me to save changes?

I found this article that explains "volatile formulas" in Excel such as CELL(), NOW(), RAND(), TODAY() and a few others. Here is the full article:

"Save changes in " prompt even if no changes are made

In the database world, expressions like NOW() are called non-deterministic functions, because they don't have a single repeatable value - NOW() always returns the current date / time which of course changes every time you invoke it.

If your worksheet uses those functions, the only way to avoid the "save" prompt is to turn off automatic recalculation & check the option for recalculate before save. But that makes your worksheet "dead" in the sense that changing cell values does not recalc, which is one of the great things about Excel. You'd have to either press F9 or click the Save icon to recalc after every edit.

Apparently this affects all versions of Excel from 97 up thru 2007.

No comments: