Excel Basic – Calculation Options

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
ImageThis 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.


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Basic and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.