Stop Formula Calculation in Excel - Increase Worksheet Performance

This may seem trivial, but it will save you a lot of time when you are dealing with complex Excel files with thousands of formulas that are linked to other Excel files or with thousands of complex array formulas.  This is because these types of formulas take a long time to update/calculate and Excel recalculates all formulas and functions each time you edit a cell and hit Enter.

Stop Formula Calculations in Excel

Go to the Formulas tab and click Calculation Options and select Manual:

Download the sample workbook for this tutorial to test this out.  All of the numbers come from the RANDBETWEEN() function and so they will change each time the worksheet updates.  You can test this with automatic calculations on and then off to see the effect.

Turn Formula Calculations Back On in Excel

Follow the same steps as above but then select Automatic instead of Manual.

How to Manually Calculate Formulas and Functions

When you have the formula calculation set to manual, you will still need to be able to update formulas and function and this is easy to do:

Keyboard shortcut F9 (calculate the entire workbook)

Keyboard shortcut Shift + F9 (calculate the currently visible worksheet)

Buttons on the Ribbon Menu (Formulas tab, all the way to the right):

Notes

This works for the entire workbook; you can't turn calculations off for just one sheet or tab.

Don't forget to check if calculations have been set to manual or automatic when troubleshooting a workbook.  A common thing is to freak out that none of your formulas are working like they should until you remember that the automatic calculations have been turned off.