MrExcel.com - Your One Stop for Excel Tips & Solutions

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

January 25, 2022 - by

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.

The formula in C2 is =A2+B2. Both A2 and B2 contain the number 2. But the answer in C2 is 3 instead of 4.
Figure 291. 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.

On the Formulas tab, open the Calculation Options drop-down and make sure it is set to Automatic. In this case, it is set to Manual.
Figure 292. 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 291 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.

If you frequently use Manual calculation mode, right-click on both Automatic and Manual in the Calculations Options dropdown and choose Add to Quick Access Toolbar.

Right-click on Automatic and Add to Quick Access Toolbar. When on the QAT, it appears with a checkbox so you can quickly see at a glance when your workbook is not in Automatic recalc mode.
Figure 293. Add both Automatic and Manual to the QAT

The result: you will have two checkboxes on the QAT that always show you if you are in Manual or Automatic calculation mode.

The QAT, shown with both Automatic and Manual added. Checkboxes appear next to each. Currently., Automatic is checked.
Figure 294. See at a glance if you are in Manual calculation mode.

This article is an excerpt from Power Excel With MrExcel

Title photo by MARIOLA GROBELSKA on Unsplash