Excel Formulas Recalculation Mode Options: Manual

By default, Excel recalculates all formulas in all sheets of all open workbooks after each data entry. When a sheet contains hundreds or thousands of formulas, automatic recalculation can noticeably slow down your work with the program. Let's explore how you can set up Excel to improve its performance and work seamlessly.



Automatic and Manual Recalculation

For a workbook containing hundreds of complex formulas, you can configure it to recalculate only when demanded by the user. To do this:

  1. Enter a formula on a blank sheet (to verify how this example works).
  2. Select "Formulas" - "Calculation Options" - "Manual."
  3. Make sure that now, after entering data into a cell (for example, changing the number in cell A2 from 1 to 7, as shown in the figure), the formula does not automatically recalculate. It will not recalculate until the user presses the F9 key (or SHIFT+F9).
Result

Attention! The F9 shortcut key performs a recalculation in all formulas in the workbook on all sheets. The SHIFT+F9 shortcut performs a recalculation only on the current sheet.

If your sheet doesn't contain numerous formulas that slow down Excel, you may not need to use the method described above. But it's good to be aware of this feature for future reference, especially when working with complex tables and formulas. Additionally, this function may accidentally be enabled, so knowing how to disable it for standard operation is valuable.

Displaying Formulas in Excel Cells

In Excel cells, you usually see only the result of calculations. The formulas themselves can be viewed in the formula bar, but sometimes you might need to see all functions in cells simultaneously (e.g., for comparison).

To make the formulas in your cells visible, follow these steps:

Excel Formulas

Let's change the Excel settings so that the cells display the formulas, and not the result of their calculation.

Example Hot Key

download file Download

To achieve this, you need to select "Formulas" - "Show" (under the "Formula Auditing" tab). To exit this mode and return to the normal display of calculation results in cells, select the tool again.

You can also use the hotkey combination CTRL+` (above the Tab key). This combination works as a toggle, meaning that pressing it again will return you to the regular mode displaying calculation results in cells.

Note: The actions described above apply only to the display mode of cells on one sheet. If you need this functionality on other sheets, you will need to perform these actions again.