What to do if Excel formula does not calculate: causes and fixes

Key points of this article

After entering a formula, only the formula text is displayed. This is probably because the cell is formatted as text, or there is an apostrophe or space before the formula.
When all formulas are displayed as formulas themselves, you can press Ctrl+` to switch the display mode. This is the quickest way to confirm.
The results do not update with the data. Usually the calculation mode is set to manual. Just change it back to automatic.
Numbers look correct but sum to 0, a common reason is that the numbers have spaces or invisible characters and are treated as text.
When "circular reference" appears in the status bar, you must find the cell that refers to itself and modify it, otherwise the result will be unreliable.

Many people enter =SUM(A1:A5) in Excel and press Enter, but only the formula text is left in the cell; or the formula has a result, but the source data is changed, and the result remains unchanged. This type of problem looks like the formula is written incorrectly. In fact, it is mostly due to cell settings or calculation methods that prevent Excel from performing calculations. Figure out why an Excel formula isn't calculating and you can usually fix it in a matter of minutes.

This article classifies symptoms, starting from common and easy-to-check causes, and provides confirmation methods and repair steps for each item. It is suitable for people who already know how to write formulas and just want it to calculate the results normally. It does not teach how to write functions.

The formula does not calculate, what should I do? One sentence conclusion

In most cases, checking the cell format and calculation mode first will solve the problem. The table below lists common situations and corresponding actions. You can find the reasons by comparing them with your own table.

your situation	Recommended practices	reason
The formula appears as is, with an apostrophe or space at the beginning	Delete leading symbols and re-enter	Content starting with an apostrophe or space will be treated as normal text
The entire column of formulas is displayed as formula text	Change the cell format to normal, press F2 one by one and press Enter	When the cell is in text format, calculation will not be performed after pressing Enter
There are results, but they are not updated after changing the data.	Change the calculation mode to automatic and press F9 to verify	In manual mode, Excel only recalculates when you ask it to
All formulas are displayed as the formula itself	Press Ctrl+` to switch display modes	The display formula mode is turned on, and the table displays formulas instead of results.
The result is 0, but the numbers look fine	Check whether the number is text and uniformly convert it to a numerical value	Numbers with spaces or invisible characters will be ignored when summing

First determine what kind of problem it is

The causes corresponding to different symptoms vary greatly. First confirm which category you belong to, and then modify it to avoid repeated trial and error.

symptoms	Common causes	How to confirm
There is only formula text in the cell and no result.	Text format, or leading apostrophe	Click on the cell to see if there is an apostrophe at the beginning of the edit bar; check if the cell format is text
Formulas throughout the table are displayed as text	Show formula mode is on	Press Ctrl+`, if the result returns to normal, this is the reason
The result does not change with the source data	Manual calculation mode	Modify a source data and see if the result does not change; whether the result is updated after pressing F9
The result is 0 or some items are missing.	Numbers are text with spaces or invisible characters	The left side of the number is usually aligned to the left, or there is a green triangle in the upper left corner of the cell.
The status bar shows "Circular Reference"	The formula refers directly or indirectly to itself	Check the reference range of the formula one by one according to the cell address given in the status bar
After copying and pasting, the result becomes a fixed number.	Pasted as a numerical value, the formula has been lost	Click on the cell to see if there is any formula starting with an equal sign in the edit bar.

Check and repair item by item

It is recommended to troubleshoot in the following order: first deal with the issues that only need to change one setting, and then deal with the issues that require checking the data one by one. Each section corresponds to a cause.

Text format and leading symbols

Select the cell range where the formula is located and change the format to "General" in the format settings.
Double-click to enter each cell (or press F2 after selecting it), and press Enter directly to let Excel re-parse the content.
If there are apostrophes or spaces in front of the formula, delete these characters after entering the editing state, and then press Enter.
If there are a lot of cells, you can change the format first, and then use search and replace to batch remove the apostrophes at the beginning.

Show formula mode

Press Ctrl+` to switch display modes. This shortcut is the same as the backtick key below Esc on your keyboard.
If the results return to normal after switching, it means that the entire worksheet is in the state of displaying formulas and there is no need to modify them one by one.
If it is still abnormal after switching, go back to checking the text format and leading symbols.

Manual calculation mode

Check the current mode in the calculation options of Excel. If it is "Manual", change it to "Automatic".
After changing to automatic, modify any source data and the confirmation result will be updated accordingly.
If you don’t want to change the mode temporarily, you can press F9 to force the entire workbook to be recalculated. However, if the table is large, manual operation will be repeated. It is not recommended to use this for a long time.

Numbers are text

Select the data column with the suspected problem and observe whether there is a green triangle or the numbers are aligned to the left.
Use a function such as LEN or TRIM to check the number of characters in the suspicious cells to see if there are extra spaces.
After removing the spaces, use the column sort function or the "add" operation in Paste Special to convert the text numbers into numerical values.
Recheck the total results after conversion to confirm that the numbers have been included in the calculation.

Circular reference

Check whether the "circular reference" prompt appears in the status bar, and write down the corresponding cell address.
Open the formula and check whether the range it refers to contains the cell where the formula is located.
Narrow the reference scope to the data that is really needed and remove references to yourself.
After modification, confirm that the status bar prompt disappears and the result no longer displays 0 or an error value.

How to avoid it happening again next time

Before starting to make a large table, confirm that the calculation mode is automatic to avoid discovering that the results have not been updated midway.
When pasting data from an external system, first use the "Paste Special" numerical method and check whether there are spaces.
Data columns should be set to regular or numerical format. Do not change them to text in the prepared table.
After entering the formula, check the results of one or two cells to make sure they are consistent with expectations, and then copy the formula to a large area.
If you want to retain the original text in the table, it is recommended to place it separately from the calculation area to reduce the chance of mistaken correction.

❓ Frequently Asked Questions (FAQ)

1. After changing the cell format, the formula still does not calculate, what should I do?

After the format is changed to regular, the original formula will not be automatically re-parsed. Double-click the cells one by one and press Enter, or select the range and press F2 and press Enter to make the formula take effect.

2. Why are my empty cells not included in the calculation?

Cells that appear to be blank may have spaces in them, or the formula may return an empty string. After selecting the cell, press Delete to clear it, and then check whether there is still content in the edit bar, which usually solves the problem.

3. What does the green triangle in the upper left corner of the cell mean?

This is Excel's hint that the number in the cell may be in text format or inconsistent with the format of adjacent data. After confirmation, it can be converted into a numerical value to avoid missing calculations when summing.

4. The formula is normal, but the calculation becomes slow when the file is large. How to deal with it?

First confirm whether the calculation mode is automatic, and then check whether there are a large number of repeated or unnecessary formulas. Pasting the results that need to be retained for a long time as numerical values can reduce the burden of each recalculation.

📝 Summary

Excel formulas are not calculated. Common reasons are that the cells are in text format, the display formula mode is turned on, the calculation method is manual, or there are spaces or circular references in the numbers. First judge according to the symptom comparison table, and then fix it one by one in order. Most problems can be solved quickly. Developing the habit of checking the format and calculation mode first can reduce the trouble of most formulas not calculating.

Open OmniDoc and work with your spreadsheet file