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.
-
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.
- 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 Fig 302 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.
- 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.
- See at a glance if you are in Manual calculation mode.