Excel Bugs: my worksheet does not calculate when I change the variable of a formula???
Don’t panic! It’s most likely that the Calculation Options has been turned to Manual.
Calculation Options – Automatic vs Manual
In Excel 2007 or above, it’s under Formula Tab of the Ribbon. In Excel 2003, go to Tools->Options->Calculation
This is a screenshot of Excel 2011:Mac
A story of mine:
Even after more than a decade, I still remembered how silly I was at an Excel test for a job interview…
At that time, I had little experience in using Excel. Nonetheless, the test was not a difficult one and it only asked for basic skills like “table drawing” and simple formulation like SUM. However that was a total failure as I was stuck with a spreadsheet that did not calculate itself. The only thing in my mind was… the Excel program I worked with contained bugs and was not functioning properly.
Be frank, how many novel users would expect a powerful spreadsheet program like Excel that offers you such a choice to TURN OFF automatic calculation? In other words, why I need a spreadsheet if it is not calculating automatically???
As my skills grow, my demands of formulating rocket. Then it makes perfect sense to TURN OFF the automatic calculation until we ask Excel to do so (by pressing F9 to recalculate open workbook(s),or SHIFT F9 to recalculate active worksheet)
To many advance users, building spreadsheet with tens of thousand rows and formula is very common. With such a large spreadsheet, a recalculation of all formula would take up noticeable time, e.g. a few seconds. In this sense, automatic calculation drags down performance. Think about you need to wait for 2 seconds when you simply apply a filter… isn’t it annoying?
Therefore, many users will turn OFF automatic calculation and even use a Macro to turn OFF automatic calculation whenever a file is opened.
So next time, when your spreadsheet is idle, don’t panic! Simply Press F9 or change the Calculation Options to Automatic.
If you wish to learn more in details about Calculation Options, here is the link:
What was the result of that interview? Forget it… 🙂
However if you are an employer, I hope you do not test your potential employees in that way as it gives you no particular insights about one’s Excel skills.