Excel Tip of the Week #53 - Causes of formulas not calculating

Because we've now covered so much, from now every so often we will take a week to revisit a popular older topic.  This will let the TOTW continue to teach core Excel topics without becoming stale.

Anyhow, for this week, we have a Creator post in which we will be taking a look at a few causes of a common Excel problem: formulas not calculating properly.

This blog is also available on YouTube.

Description: A cell contains what looks like a formula, but the formula doesn’t calculate.  Instead the text of the formula itself appears in the cell directly.  See example below.

Cause: The cell is formatted as Text, which causes Excel to ignore any formulas.  This could be directly due to the Text format, or is particularly common when importing data from a CSV or Notepad file.

Fix: Change the format of the cell(s) to General or some other format.  However, the formulas still won’t start working until you force Excel to reconsider the content.  You can do this in one of two ways.

Method 1: Jump into the affected cell (mouse click or F2) and then immediately exit again.  This is quicker for a single formula but if there are many formulas it will be time-consuming and you should use Method 2 instead.

Method 2: Use Find and Replace (Ctrl + F).  Choose to replace = with = and this will cause Excel to refresh the formulas and begin calculating as normal.

Description: A selection of formulas are written correctly, but display results that don’t agree to the correct inputs or don’t make sense.

Cause: The workbook has been placed in “Manual calculation” mode. This will mean that Excel doesn’t automatically update all formulas whenever the workbook is amended and needs to be manually forced to do so. This also means that if you copy and paste a formula (like the addition formula shown on the right), the result is copied instead of the correct answer until the manual recalculation prompt is used.

Fix: You can either force a manual recalculation, or turn the calculation back to automatic. Note that manual calculation is usually set when the workbook is very large and unwieldy to speed up calculation times.

Method 1: You can force a manual recalculation either by pressing the Calculate button in the bottom left most corner of Excel, or by pressing F9.

Method 2: You can switch the workbook back to Automatic calculation from Formulas => Calculation options.

These two are the cause of 99% of problems of this type.

Last reviewed: December 2021.