Stop Formula Calculation in Excel - Increase Worksheet Performance

Add to Favorites
Author:

I'll show you how to stop formulas and functions from performing their calculations and updating in Excel.

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.

 


Excel Function: RANDBETWEEN()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Excel Function: RANDBETWEEN()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course