How to fix Microsoft Excel formulas not calculating or showing as text

Summary

This guide helps you troubleshoot and resolve: How to fix Microsoft Excel formulas not calculating or showing as text. Follow the steps below to fix the issue.

Common Causes

When Excel formulas stop working as expected, the symptoms usually fall into three categories: the formula appears as plain text in the cell, the formula returns an incorrect or unexpected result, or the formula simply doesn't recalculate after you change the underlying data. Each of these issues has a distinct cause, and most can be resolved in just a few minutes by adjusting cell formatting, calculation options, or how the formula was entered. This guide walks you through the most common fixes in order from quickest to most thorough.

Quick Fix Steps

If you're short on time, work through these checks first. They resolve the majority of Excel formula issues:

1. Make sure the cell is formatted as Number or General, not Text.
2. Confirm the formula begins with an equals sign (=) and not an apostrophe or quote.
3. Press Ctrl + ` (grave accent) to toggle Show Formulas off if it's accidentally enabled.
4. Open Formulas → Calculation Options and ensure it is set to Automatic.
5. Press F9 to force a manual recalculation of the entire workbook.

If the problem persists after these steps, continue to the detailed instructions below.

Detailed Instructions

1. Check Whether the Cell Is Formatted as Text

The most common reason a formula appears as text is that the cell was formatted as Text before the formula was entered. Excel treats anything in a text-formatted cell as a literal string, so =A1+B1 will display exactly that rather than calculating a result.

1. Select the cell or range of cells showing the formula as text.
2. Right-click and choose Format Cells, or press Ctrl + 1.
3. In the Number tab, select General or Number.
4. Click OK.
5. Re-enter the formula in each cell (select the cell, press F2, then Enter).

2. Remove a Leading Apostrophe or Quote

If a formula was pasted from another source or typed with a leading apostrophe ('), Excel will treat the entire entry as text. Apostrophes are sometimes added accidentally by autocomplete or when importing data.

1. Click on the affected cell.
2. Look at the formula bar. If you see a leading apostrophe before the equals sign, that is the cause.
3. Select the cell, press F2 to edit, delete the apostrophe, and press Enter.

3. Confirm Calculation Mode Is Set to Automatic

If formulas calculate correctly when you press F9 but don't update automatically, the workbook is likely set to Manual calculation mode.

1. Go to the Formulas tab on the ribbon.
2. In the Calculation group, click Calculation Options.
3. Select Automatic.
4. If you want to keep manual mode for performance reasons, use Calculate Now (F9) or Calculate Sheet (Shift + F9) when needed.

4. Disable Show Formulas Mode

The Show Formulas view displays the formula in every cell instead of the result, which is useful for auditing but can be confusing if enabled by accident.

1. Go to the Formulas tab.
2. In the Formula Auditing group, check whether Show Formulas is highlighted (active).
3. Click it once to toggle it off, or press Ctrl + `.

5. Check for Circular References

A circular reference occurs when a formula refers to its own cell, either directly or indirectly. Excel will display a warning and may stop calculating.

1. Go to the Formulas tab.
2. Click Error Checking → Circular References.
3. The submenu will list any cells involved in a circular reference.
4. Click each entry to navigate to the cell, then revise the formula to remove the self-reference.

6. Inspect for Hidden Characters or Spaces

Imported or copied data sometimes contains non-breaking spaces or hidden characters that prevent Excel from recognising a formula.

1. Select the cell and press F2 to enter edit mode.
2. Use the arrow keys to check for stray spaces before or after the equals sign.
3. Delete any extra characters and press Enter.

Alternatively, you can use the TRIM and CLEAN functions to strip unwanted characters from imported data. For example, =TRIM(CLEAN(A1)) will remove most non-printable characters and excess whitespace.

7. Verify Regional and List Separator Settings

Australian and European Excel installations often use the semicolon (;) as the list separator instead of the comma. If your formulas were created on a system with different regional settings, the arguments may be parsed incorrectly.

1. Go to File → Options → Advanced.
2. Scroll to Editing options.
3. Untick Use system separators if you want to force a specific separator, or leave it ticked and adjust your Windows regional settings via Settings → Time & Language → Region.

Troubleshooting

If none of the above resolves the issue, consider the following less common causes:

• Protected sheet or workbook: Formulas may be locked or hidden. Go to Review → Unprotect Sheet and enter the password if required.
• External link broken: Formulas referencing another workbook may show errors if the source file has moved. Check Data → Queries & Connections → Edit Links.
• Add-in conflict: Some third-party add-ins can interfere with calculation. Try starting Excel in safe mode by holding Ctrl while launching, then test the formula.
• Corrupted workbook: If the issue only occurs in one file, try opening a fresh workbook and copying your data across. Alternatively, use File → Open, browse to the file, and choose Open and Repair from the dropdown next to the Open button.

Related Articles

• Excel Formulas Not Calculating or Showing as Text
• Fixing Common VLOOKUP Errors in Excel
• Refreshing and Updating Pivot Tables in Excel
• Setting Up the Print Area in Excel
• Freezing Panes and Splitting Windows in Excel

Still Having Issues?