Most valuable professional
  • Excel Book Excerpt

Excel You Change a Cell in Excel but the Formulas Do Not Calculate

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

You Change a Cell in Excel
but the Formulas Do Not Calculate

Problem: Sometimes when I change a cell in Excel, the formulas do not calculate. Below, cell C2 indicates that two plus two is not four.

LE10000280.jpg 

Figure 303 Excel isn’t calculating.

Strategy: In this case, someone has put the worksheet in Manual calculation mode. You can try pressing F9 to calculate.

There are several variants of recalculating:

  • Pressing F9 will recalculate all cells that have changed since the last calculation, plus all formulas that depend on those cells in all open workbooks.
  • For quicker calculation, use Shift+F9. This will limit the calculation to the current worksheet.
  • For thorough calculation, use Ctrl+Alt+F9. This will calculate all formulas in all open workbooks, whether Excel thinks they have changed or not.
  • Pressing Ctrl+Shift+Alt+F9 rebuilds the list of dependent formulas and then does a thorough calculation.

Additional Details: You can change the Calculation Options. Select Formulas, Calculation Options to see the various calculation options.

LE10000281.jpg 

Figure 304 Change calculation settings.

Gotcha: Before you begin using manual calculation mode, you need to understand a dangerous situation. The calculation mode is global for all workbooks that are currently open. Say that you open WorkbookA and it is in manual calculation mode. You then open Workbook2 through Workbook9, change a few cells and save them. All the while, WorkbookA remained open in the background. This will change the calculation mode on Workbook2 through Workbook9 to manual. While it is easy to see in Figure 303 that something is wrong, it is not easy to notice that manual calculation mode is on in most workbooks. You can see how manual calculation mode can insidiously spread through your workbooks like a virus.

Gotcha: Before you go back to Automatic calculation mode, ask the person who created the worksheet why it is in Manual calculation mode. Sometimes you will find a spreadsheet with tens of thousands of calculations that takes 30–45 seconds to calculate. It is very frustrating when the system pauses for 45 seconds after every single data entry. If you have a lot of data entry to do, a standard strategy is to use Manual calculation mode because in this mode, you can make several changes and then press F9 to calculate.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: