How to Fix Excel Formulas That Are Not Calculating
Published 2026-10-16 · Marion Remote Fix
You type a formula in Excel and instead of a calculated result, the cell shows the formula text itself — literally displaying =SUM(A1:A10) instead of the total. Or the formula worked before but now shows a stale result that does not update when you change the input cells. This is one of the most common Excel problems, and the fix is usually a calculation setting, a cell formatting issue, or a formula error. Here is how to fix every variant.
Check the calculation mode
The most common reason formulas stop updating is that Excel's calculation mode has been switched from Automatic to Manual:
- Go to the Formulas tab on the ribbon
- In the Calculation group (far right), click Calculation Options
- If it is set to Manual, change it to Automatic
- All formulas in the workbook should immediately recalculate
How this happens: when you open a workbook that was saved with Manual calculation, Excel keeps that setting for your session. It can also happen if you opened a very large workbook with thousands of formulas — Excel or an add-in may have switched to Manual to prevent slow recalculation. Some VBA macros also toggle calculation mode and may not switch it back.
If you are in Manual mode intentionally (for performance with a large workbook), you can force a recalculation anytime by pressing F9 (recalculate all open workbooks) or Shift+F9 (recalculate the active sheet only). Ctrl+Alt+F9 forces a full recalculation even if Excel thinks nothing changed.
Fix formulas showing as text
If the cell displays the formula text instead of the result (you see =A1+B1 in the cell, not the answer), the cell is formatted as Text or has a leading character preventing calculation:
Cell formatted as Text
- Click the cell showing the formula as text
- Check the Number Format box in the Home tab (it shows "Text" instead of "General" or "Number")
- Change the format to General
- The formula may still show as text even after changing the format. You need to force Excel to re-evaluate: click into the formula bar (or press F2) and press Enter. This tells Excel to re-parse the cell content as a formula
- For many cells at once: select all affected cells → change format to General → then use Find and Replace (Ctrl+H): find = and replace with = (equal sign for both). This forces Excel to re-enter every formula
Leading apostrophe or space
If someone typed a space or apostrophe before the formula (like '=SUM(A1:A10) or a space followed by =SUM), Excel treats it as text:
- Click the cell → look in the formula bar for a leading space or apostrophe before the equals sign
- Delete the extra character so the formula starts with = as the very first character
- Press Enter
Check for Show Formulas mode
Excel has a display mode that shows all formulas instead of their results — you may have toggled it accidentally:
- Go to the Formulas tab → click Show Formulas (in the Formula Auditing group). If it is highlighted/active, click it to toggle it off
- Keyboard shortcut: press Ctrl+` (the backtick key, above Tab on most keyboards). This toggles between showing formulas and showing results
- When Show Formulas is on, ALL cells show their formulas, columns become wider, and the sheet looks completely different. This is a display toggle — your formulas are fine, they are just being shown instead of calculated
Fix circular references
A circular reference occurs when a formula refers to its own cell, either directly or through a chain of references. Excel cannot calculate these by default and shows a zero or an error:
- Excel usually shows a warning when you create a circular reference: "There are one or more circular references..." If you dismissed this warning, the formula is stuck
- Find the circular reference: Formulas tab → Error Checking dropdown (arrow) → Circular References. It shows the cell addresses involved
- Click the listed cell and examine the formula. It references itself — trace the reference chain to find which cell should be changed to break the circle
- Fix the formula so it does not refer back to its own cell. Common cause: =SUM(A:A) in a cell within column A sums itself; change to =SUM(A1:A99) excluding the formula cell
Note: Excel has an option for iterative calculation (File → Options → Formulas → Enable iterative calculation) that allows circular references to calculate by running multiple passes. This is used intentionally in some financial models, but if it is off (default), circular references just show zero.
Fix common formula errors
If the formula calculates but shows an error value instead of the expected result:
| Error | Meaning | Common Fix |
|---|---|---|
| #REF! | A cell reference is invalid (deleted row/column/sheet) | Find the #REF! in the formula bar and replace with the correct cell reference |
| #VALUE! | Wrong type of argument (text where number expected) | Check that referenced cells contain numbers, not text that looks like numbers |
| #NAME? | Excel does not recognize a name in the formula | Check for misspelled function names or missing quotation marks around text |
| #DIV/0! | Division by zero | The denominator cell is 0 or empty. Use =IF(B1=0,0,A1/B1) to handle it |
| #N/A | Value not found (VLOOKUP/INDEX/MATCH) | Check that the lookup value exists in the lookup range. Check for extra spaces with TRIM() |
| #NUM! | Invalid numeric value | The formula produces a number too large or too small for Excel, or an impossible calculation (like square root of a negative number) |
| #NULL! | Incorrect range operator | You used a space instead of a colon between cell references (A1 A10 instead of A1:A10) |
Fix numbers stored as text
A formula may calculate but give the wrong result because input cells contain numbers that are actually stored as text. This is extremely common with data imported from CSV files, databases, web pages, or other systems:
- Signs that numbers are stored as text: the cell shows a small green triangle in the upper-left corner, numbers are left-aligned instead of right-aligned, and SUM of those cells returns zero
- Quick fix: select the affected cells → click the yellow warning diamond that appears → choose "Convert to Number"
- For many cells: select the range → Data tab → Text to Columns → click Finish immediately (without changing any settings). This forces Excel to re-parse the cell content and converts text-numbers to real numbers
- Formula approach: if cell A1 contains a text number, =A1*1 or =VALUE(A1) forces it to a real number
Fix formulas not updating across sheets or workbooks
Formulas that reference other sheets or external workbooks may stop updating:
- Cross-sheet references: if you renamed or deleted the referenced sheet, the formula shows #REF!. Check the sheet name in the formula (Sheet1!A1) and update if the sheet was renamed
- External workbook references: if the source workbook is closed, Excel shows the last cached value and does not update. Open the source workbook and press F9 to recalculate, or update the link: Data tab → Edit Links → Update Values
- Broken links: if the source file was moved or deleted, Data tab → Edit Links shows the file path. Click Change Source to point to the new location
Repair the Excel file
If formulas are behaving erratically (some calculate, some do not, or results are wrong) and the fixes above do not help, the workbook file itself may be corrupted:
- Close the workbook
- Open Excel → File → Open → browse to the file → click the dropdown arrow on the Open button → select Open and Repair
- Try Repair first. If that does not fix it, try Extract Data (recovers formulas and values but may lose formatting and charts)
- If the file cannot be repaired, try opening it in Google Sheets (upload to Google Drive → Open with Google Sheets). Google Sheets can sometimes read corrupted Excel files. Then export back as .xlsx
Check add-in and macro interference
Excel add-ins and VBA macros can interfere with calculation:
- Start Excel in Safe Mode: hold Ctrl while clicking the Excel icon (or run
excel /safefrom Win+R). Safe Mode disables all add-ins and macros - Open your workbook and check if formulas calculate correctly in Safe Mode
- If they do, an add-in is causing the problem: File → Options → Add-ins → Manage: COM Add-ins → Go → uncheck each add-in one by one to find the culprit
- Common interfering add-ins: PDF converters, data analysis tools, and CRM integrations that hook into Excel's calculation engine
Remote Excel troubleshooting
If your Excel formulas are not calculating and the fixes above have not resolved the issue, Marion Remote Fix can diagnose the problem remotely — checking calculation settings, repairing corrupted workbooks, resolving formula errors, and fixing add-in conflicts through a secure remote session.