Calculation Settings in Excel

Sometimes Excel doesn't behave as you expect and you learn something. Excel is complex as Joel Spolsky pointed out last week.

Quite often clients will send data updates in Excel spreadsheets as this is the format they are used to working with; even if you as a programmer are not. One such workbook I have worked with this week had a worksheet with formulas to increment id's. The problem was the id's were not incrementing, even though the formula was correct.

After some investigation it turned out that the Calculation was set to manual (Tools -> Options -> Calculation Tab in Excel 2003), as shown below -

"Automatic calculation" mode means that Excel will automatically recalculate all open workbooks at each and every change, and whenever you open a workbook - which is what I was expecting and wanted (if you are in "manual calculate" mode pressing F9 will update the formulas).

This "manual calculate" behaviour is by design and Excel tries to help you realise this by updating the status bar with the word "Calculate". In fact, as this behaviour is set by the first workbook opened, it may seem to randomly change depending on the sequence the workbooks are opened, and their settings.

As Joel Spolsky said in his article...

"Every checkbox, every formatting option, and every feature in Microsoft Office has to be represented in file formats somewhere."