Excel Formula Not Calculating - 10 Fixes

When an Excel formula is not calculating, the cause is usually one of a few practical problems: calculation mode is set to Manual, the cell is formatted as text, the formula was entered with a leading apostrophe, the referenced cells contain text instead of numbers, or the workbook has a circular reference.

Start with the quick checks below, then use the matching fix section. If your formula is calculating but returns an error such as #VALUE!, #REF!, or #N/A, use the broader Excel formula errors guide after checking calculation mode.

1. Check Whether Calculation Mode Is Manual

The most common reason formulas stop updating is Manual calculation mode. In Manual mode, Excel waits until you tell it to recalculate.

Check it here:

1. Go to Formulas > Calculation Options.
2. Choose Automatic.
3. Press F9 to recalculate the workbook.

Use this test formula in a blank cell:

=NOW()

If the time does not update after you edit another cell, calculation mode is probably Manual.

Calculation mode is an application-level setting. A workbook opened in Manual mode can make later workbooks behave the same way during that Excel session.

2. Fix Formulas Showing as Text

If the cell displays =SUM(B2:B6) instead of the result, Excel is treating the formula as text.

Common causes:

After changing the cell format to General, click the formula cell, press F2, then press Enter so Excel re-evaluates it.

Example formula that should calculate:

=SUM(B2:B6)

If it still shows as text, copy the formula to Notepad, clear the cell, format the cell as General, and paste the formula back.

3. Turn Off Show Formulas

Show Formulas is useful for auditing, but it can look like every formula has stopped calculating.

Turn it off with either method:

1. Press Ctrl+` on Windows.
2. Or go to Formulas > Show Formulas and make sure it is not selected.

Here is the difference:

This setting only changes what you see. It does not change the formula itself.

4. Convert Text Numbers to Real Numbers

A formula can calculate and still produce the wrong result if the source values are stored as text. This often happens after importing CSV files, copying data from websites, or exporting reports from other systems.

Check a value with:

=ISNUMBER(B2)

If it returns FALSE, convert the text number before calculating:

=VALUE(B2)

For a total, use a helper column to convert each value, then sum the converted numbers:

=SUM(C2:C10)

For a fuller cleanup workflow, see Excel Data Cleaning. For the number-specific conversion workflow, see Excel numbers stored as text.

5. Check for Hidden Spaces and Non-Printing Characters

Hidden characters can make formulas appear broken because Excel sees two values as different even when they look identical.

Use LEN to compare visible and actual length:

=LEN(A2)

Clean regular extra spaces with:

=TRIM(A2)

Clean non-printing characters with:

=CLEAN(A2)

For non-breaking spaces copied from web pages, use:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

This is especially important before lookup formulas. If a lookup is the formula that is not calculating as expected, also review XLOOKUP troubleshooting or VLOOKUP troubleshooting.

6. Fix Circular References

A circular reference happens when a formula refers to itself, directly or indirectly. Excel may show a warning, calculate incorrectly, or keep an old value.

Example direct circular reference:

=A1+10

That formula is circular if it is entered in A1.

Find circular references here:

1. Go to Formulas > Error Checking.
2. Choose Circular References.
3. Select the listed cell.
4. Rewrite the formula so it points to the intended input cell.

Only enable iterative calculation when you intentionally need a circular model, such as a finance workbook with controlled iterations.

7. Audit the Referenced Range

Sometimes the formula calculates correctly, but it points at the wrong cells. This can happen after inserting rows, copying formulas, or selecting a range too quickly.

Check the formula by selecting the cell and looking at the colored reference borders. Then confirm the start and end cells.

For example, this formula misses row 10:

=SUM(B2:B9)

If the data runs through row 10, use:

=SUM(B2:B10)

For growing data, convert the range to an Excel Table with Ctrl+T, then use a structured reference:

=SUM(Table1[Amount])

Tables reduce the chance that new rows are left outside the formula.

8. Lock References Before Copying Formulas

If a formula works in the first row but breaks after you fill it down, the references may be moving when they should stay fixed.

Example tax calculation:

=B2*$F$1

Here, B2 should change by row, but $F$1 should stay locked as the tax rate.

For more examples, see Excel cell references. If you are using the fill handle to copy formulas quickly, pair this with Autofill options in Excel.

9. Check for Formula Errors Hidden by IFERROR

IFERROR is useful for clean reports, but it can hide the real reason a formula is not working.

This formula hides every error:

=IFERROR(VLOOKUP(A2,$F$2:$G$100,2,FALSE),"")

During debugging, temporarily remove IFERROR:

=VLOOKUP(A2,$F$2:$G$100,2,FALSE)

Then fix the actual error. For lookup-specific errors, use How to Fix VLOOKUP #N/A in Excel or the Excel XLOOKUP tutorial.

Use IFERROR again only after you understand which errors are expected.

10. Recalculate, Save, and Test a Simple Formula

After fixing the likely cause, force Excel to recalculate and test a simple formula in a blank area.

Useful recalculation shortcuts:

Use this small test table:

Test formula:

=SUM(A2:A4)

Expected result:

If this simple formula works but your real formula does not, the problem is in the real formula's references, source data, or function logic.

11. Quick Troubleshooting Checklist

Use this order when a formula is not calculating:

1. Set Formulas > Calculation Options to Automatic.
2. Turn off Show Formulas.
3. Change formula cells from Text to General, then re-enter them.
4. Remove leading apostrophes before formulas.
5. Convert text numbers with VALUE or Text to Columns.
6. Clean hidden spaces with TRIM, CLEAN, or SUBSTITUTE.
7. Check for circular references.
8. Confirm the referenced range includes the right rows and columns.
9. Lock fixed references before copying formulas.
10. Remove IFERROR temporarily so real errors are visible.

12. Related Guides

• Decode #VALUE!, #REF!, #N/A, and other problems with Excel formula errors.
• Learn how references move when formulas are copied in Excel cell references.
• Clean imported numbers, spaces, and hidden characters with Excel Data Cleaning.
• Convert numeric-looking values with Excel numbers stored as text.
• Use Autofill options in Excel after locking references correctly.
• Fix lookup-specific failures with XLOOKUP troubleshooting or VLOOKUP troubleshooting.
• Use LET in Excel to make long formulas easier to debug.

13. Summary

Most formulas that are not calculating can be fixed without rebuilding the workbook. Start with calculation mode, Show Formulas, and text formatting. Then check source data, circular references, copied references, and hidden errors.

Once a simple SUM formula calculates correctly, work outward: verify the real formula's inputs, ranges, and expected result one piece at a time.