Excel Formula Not Working? Fix It by Cause, Step by Step (2026)

An Excel formula usually fails for one of five reasons, and the cell tells you which. If you see the formula text instead of a result, the cell is formatted as Text or Show Formulas is on. If the number will not update, calculation is set to Manual. If you see a #NAME? or #VALUE! code, the code names the problem.

Why is my Excel formula not working?

Most formulas that “stop working” were never wrong. The formula is fine and one of three things around it is off: the cell format, the workbook’s calculation setting, or the data feeding it. Excel does exactly what you typed, and that includes treating the text “1,200” as a word instead of a number.

I triage by symptom before touching anything. This takes about a minute and saves a lot of retyping.

What you see	Most likely cause	First move
The formula itself, like =SUM(A1:A5)	Cell formatted as Text, or Show Formulas on	Set format to General, F2, Enter
A result that will not update	Manual calculation	Formulas, Calculation Options, Automatic
SUM is 0 or too low	Numbers stored as text	Convert to Number
#NAME?, #VALUE!, #REF!, #N/A	Error-specific	See the error table below
A circular reference warning	Formula refers to its own cell	Formulas, Error Checking, Circular References

The screenshots and menu names here follow Microsoft 365 on Windows in English, US region. Mac menus differ slightly, but the causes are identical. If you want a refresher on the functions themselves, keep my Excel functions cheat sheet open in another tab.

Why does Excel show my formula as text instead of the result?

Two causes account for nearly every case. The cell was formatted as Text before you typed the formula, or Show Formulas is switched on.

Check the second one first, because it is a single click. On the Formulas tab, look at Show Formulas. The keyboard shortcut is Ctrl plus the grave accent key, the one left of the 1. It flips the whole sheet between formulas and results, which Microsoft describes in its guide to displaying or hiding formulas. If every formula on the sheet is showing, this is your answer.

If only one cell or column misbehaves, it is the format:

1. Select the cells, then set the Number Format on the Home tab to General.
2. Press F2 to enter edit mode, then press Enter.
3. If it still shows text, look for a leading space or an apostrophe before the equals sign.

Changing the format alone does nothing to a formula already typed as text. Excel only reevaluates the cell when you re-enter it. That Enter step is the one people skip.

Why is my Excel formula not calculating automatically?

If you edit a cell and the total stays put, calculation is probably on Manual. Excel normally recalculates a formula when anything it depends on changes, but you can change that behavior. Microsoft documents the setting in its page on changing formula recalculation, iteration, or precision.

Go to Formulas, then Calculation Options, and select Automatic. In a hurry, F9 recalculates open workbooks, and there is also a Calculate Sheet button for just the active sheet.

Here is the trap that bites people. Excel often takes the calculation mode from the first workbook you open in a session. If a colleague saved a huge model on Manual to keep it fast, opening that file first can flip every other workbook to Manual too. When a healthy file suddenly stops updating, check which file you opened first.

Why are my numbers stored as text, and how do I convert them?

This is the most common hidden cause, and it is behind a surprising share of “SUM is wrong” questions. Data exported from accounting software, a CRM, or a website often arrives with numbers stored as text.

Clues you can see without any formula:

• The numbers are left-aligned, while real numbers align right.
• A small green triangle sits in the top-left corner of the cell.
• SUM returns 0, or silently skips some rows.

Microsoft notes that text-formatted numbers cause calculation and sorting problems and often appear after importing from a database or other source; the steps are in its article on fixing text-formatted numbers.

Situation	Fix	Example
Green triangle present	Click the warning icon, Convert to Number	Works on a whole selection at once
No triangle	Type 1 in a blank cell, copy it, select the range, Paste Special, Multiply	Each value becomes value × 1
Need a formula	=VALUE(A2) or =A2*1	”1200” becomes 1200
One crowded column	Data, Text to Columns, Finish	Converts the entire column

Fixing the data once is not enough if the same export arrives every week. Write down the cleanup steps or build them into a Power Query step, because the problem will return with the next file.

What do #NAME?, #VALUE!, and other Excel error codes mean?

An error code is a diagnosis, not an insult. Read it before reaching for IFERROR. On #NAME?, Microsoft’s correction guide is explicit that hiding the error with IFERROR is the wrong move, because the error signals a syntax problem you should fix.

Error	What Excel is saying	Common causes	Fix
#NAME?	I do not recognize this	Misspelled function, text without quotes, missing colon in a range, undefined name, function not in your version	Pick from autocomplete, wrap text in quotes, check B2:B12
#VALUE!	Wrong kind of value	A space or text where a number belongs, date stored as text	Test with ISNUMBER, clean with TRIM or VALUE
#REF!	Reference no longer exists	Deleted row, column, or sheet	Undo, or rebuild the reference
#DIV/0!	Divided by zero	Blank or zero denominator	=IF(B2=0,"",A2/B2)
#N/A	Lookup found nothing	Mismatched types or hidden spaces	Match types, use TRIM
#SPILL!	Result has nowhere to go	Cells below are not empty	Clear the blocking cells
#####	Column too narrow	Width, or a negative date	Widen the column

Microsoft’s #VALUE! guide admits this error is hard to pin on a single cause, and organizes its fixes by function. In practice, nine times out of ten it is a non-numeric cell in the math. For #SPILL!, Microsoft’s guide explains that the spill range must be empty; selecting the error cell shows you what is in the way.

Why does Excel say #NAME? even though my function looks right?

Three culprits hide behind a correct-looking formula. They are typos you cannot see, copied characters, and version gaps.

Version gaps. XLOOKUP, FILTER, SORT, and LET exist in Microsoft 365 and Excel 2021 and later. Send a workbook full of XLOOKUPs to someone on Excel 2016 or 2019, and they get #NAME? across the sheet while yours looks perfect. If you cannot control the recipient’s version, use VLOOKUP or INDEX and MATCH, or paste the results as values.

Curly quotes. Formulas copied from a blog, email, or chat often carry curly quotation marks instead of straight ones. Excel rejects them. Retype each quotation mark by hand.

Separators. The US region uses commas between arguments. A workbook built in a region that uses semicolons can show errors when its formulas are pasted into a US machine. If a formula from a European template fails, swap semicolons for commas.

Autocomplete is your best defense against typos. Type =VL and pick the function from the dropdown rather than finishing the word by hand.

How do I find and fix a circular reference?

A circular reference means a formula refers to its own cell, directly or through a chain. Excel cannot resolve it, so it warns you and often returns 0.

Microsoft’s circular reference guide says to open Formulas, then Error Checking, then Circular References, which lists the cell. If the loop is on another sheet, the status bar may show only “Circular References” without an address, so check there too.

Broken formula (typed in D5)	Why it loops	Fixed
=SUM(D1:D5)	Range includes D5 itself	=SUM(D1:D4)
D2 =D3+1 and D3 =D2+1	Two cells depend on each other	Make one a fixed input

Excel has an iterative calculation option that silences the warning. Unless you are deliberately modeling something that feeds back on itself, leave it off. Turning it on to make a warning disappear just hides a mistake.

How do I clean up spaces and mixed text in my data?

Invisible characters cause more lookup failures than bad formulas do. To your eyes “Austin” and “Austin ” match. To VLOOKUP, COUNTIF, and Remove Duplicates, they do not.

Problem	How to spot it	Fix
Leading or trailing spaces	LEN is larger than the visible characters	=TRIM(A2)
Non-breaking space from the web	TRIM does not work	=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Units mixed into numbers, like “$1,200 USD”	Cell is left-aligned	=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),"USD",""))
Hidden line breaks	Text wraps strangely	=CLEAN(A2)

The non-breaking space is the one that wastes hours. It looks like a space, TRIM ignores it, and it almost always arrives via copy and paste from a web page. SUBSTITUTE with CHAR(160) removes it.

After cleaning, paste the results back as values. If you delete the original column while the cleaned column is still formulas, you get #REF! and start over.

What does a real troubleshooting session look like?

The following is a composite example, not a single real case.

Picture a month-end sales sheet exported from a billing system, 120 rows. SUM is short by a few thousand dollars. The formula, =SUM(C2:C121), is correct.

First, a handful of cells are left-aligned with green triangles. Those are text, so SUM skipped them. Converting them to numbers closes most of the gap. A smaller gap remains. Seven cells carry a trailing “USD”. SUBSTITUTE plus VALUE fixes them, and the total ties out.

The lesson is the same one I give everyone: when the answer looks wrong, suspect the data before the formula. People rewrite correct formulas for an hour when the fix is a cleanup of the inputs.

What order should I check things in when nothing works?

Work top to bottom and stop at the first thing you change that helps.

1. Is Show Formulas on? Toggle Ctrl + `.
2. Is calculation Automatic? Press F9 to test.
3. Is the cell format Text? Change to General and re-enter.
4. Are the inputs numbers? Test with =ISNUMBER(A2).
5. Are there hidden spaces? Compare =LEN(A2) to what you can see.
6. Is there an error code? Use the table above.
7. Still stuck? Use Formulas, Evaluate Formula to step through the calculation and see where the value changes.

Prevention beats repair. Set the format of an input column before pasting data, glance at alignment after every import, and check the calculation mode when you open someone else’s model. If you are still building your formula toolkit, start with 10 Excel formulas everyone should know, and if subtraction is what is tripping you up, read the Excel subtraction formula guide.

When the whole machine is sluggish and Excel hangs, try fixing a slow computer first. If a cloud-hosted workbook will not load, WiFi troubleshooting may be the real fix. And once your formulas behave, tools from my AI productivity tools guide can automate some of the repetitive cleanup.

This article is general information. Menu names and behavior vary by Excel version, platform, and region settings. Save a copy of any important workbook before changing it, and confirm current behavior in Microsoft’s official support documentation.