Most Excel formula failures trace to about 12 patterns: wrong calculation mode, text stored as numbers, bad references, hidden spaces, range-size mismatches, unsupported functions, and error values like #N/A or #SPILL!. Miss the pattern and you can “fix” the wrong cell, leave silent calculation errors in place, and trust totals that are still wrong. This guide maps each symptom to its likely cause and the smallest fix.

An Excel formula problem includes any case where a formula returns an error, 0, a blank, text, a stale result, or the wrong value. The quickest way to fix it is to identify the visible symptom first, confirm the cause with Excel’s built-in checks, and then change one thing at a time.

What counts as an Excel formula problem?

A formula problem includes more than obvious error codes. In Excel, trouble also includes formulas that display as text, keep an old result because calculation is manual, return 0 or blank when you expected a number, spill into blocked cells, or produce the wrong value after copy, fill, or paste.

Error codes versus silent failures

Some failures are obvious: #DIV/0!, #VALUE!, #REF!, #NAME?, #N/A, #SPILL!, and #NULL!. Others are quiet. A formula can be syntactically valid and still point at the wrong range, read text instead of a number, or never recalculate.

That is why checking only for red flags in the grid is not enough. The formula bar, Error Checking, Evaluate Formula, and calculation settings still matter when no error code appears.

How formulas are built

Excel generally treats an entry as a formula only if it begins with an equals sign. After that, it can combine operators such as +, -, *, /, and &, functions such as SUM or XLOOKUP, and references such as A1, $A$1, or A1:B10.

Relative references change when you copy or fill a formula. Absolute references stay fixed when written with dollar signs, such as $B$2. Structured references in Tables use headers instead of A1-style references, such as Sales[Amount].

Where the symptom shows up

A formula problem usually appears in one of four ways:

  • The cell shows an error code.
  • The cell shows the formula text instead of the result.
  • The cell shows a blank, 0, or an old result.
  • The result changed after copy, paste, fill, table edits, or source-file changes.

Formula problems matrix

Formula problems matrix
An Excel spreadsheet graphic shows formula errors and a fix icon

How to use the matrix

Start with the symptom you can see in the worksheet. Then use the quick test in Excel to confirm the likely cause before editing the formula. That keeps you from rewriting a valid formula when the real fault is manual calculation, Text format, a blocked spill range, or a shifted reference.

Symptom Error message Probable cause Quick test Resolution
Formula displays exactly as typed None Cell is Text format or starts with a leading apostrophe Click the cell and check Number Format; look for an apostrophe in the formula bar Change format to General, remove the apostrophe, then confirm with F2 and Enter
All formulas display instead of results None Show Formulas view is on Check whether every formula is visible across the sheet Turn off Show Formulas from the Formulas tab or use Ctrl+`
Result stays old after inputs change None Workbook is in Manual calculation mode Change an input, press F9, and see whether the result updates Set calculation to Automatic and recalculate the workbook
Result is 0 but inputs look filled None Numbers stored as text, hidden spaces, or empty precedents Use ISTEXT on an input or inspect alignment and Formula Bar contents Convert text to numbers, trim spaces, or fix blank source cells
Result is blank None or empty string Formula returns "" by design, or custom format hides value Look in the formula bar for "" or switch cell format to General Edit IF logic or remove the custom number format hiding the result
Wrong value after copy or fill None Relative references shifted Compare the original formula and the copied formula in the formula bar Lock needed cells with $ signs or adjust the copied references
Formula replaced by a number after paste None Pasted values overwrote formulas Select a changed cell and check whether the formula bar shows a value, not a formula Undo, restore from a prior version, or paste formulas only where needed
Lookup returns no match #N/A Missing key, extra spaces, data type mismatch, or wrong match mode Test exact equality between lookup value and source cell; inspect spaces and types Clean spaces, align number/text types, or revise the lookup settings
Reference breaks after row, column, or sheet changes #REF! Deleted cells, moved external file, or broken named range Click into the formula and look for #REF! inside the reference Restore the missing range, repoint the formula, or fix the name definition
Function name or name is not recognized #NAME? Misspelled function, unsupported function, or bad named range Use Formula AutoComplete or Name Manager to verify the name Correct the spelling, replace unsupported functions, or repair the name
Array formula will not spill #SPILL! Blocked spill range, merged cells, or table conflict Select the formula cell and review the dotted spill border Excel shows Clear blocker cells, unmerge cells, or move the formula outside the table
Formula errors immediately after paste from another locale Often #NAME? or parse failure Comma versus semicolon separator, or decimal separator mismatch Type a simple function with commas; if Excel expects semicolons, it will reject it Replace separators to match local settings or adjust system and Excel regional settings

How do I find formula problems in Excel?

How do I find formula problems in Excel?
An Excel spreadsheet graphic shows error labels and a fix arrow

Start with Excel’s own checks before you edit the formula. Error Checking can flag inconsistent formulas and common faults, the formula bar shows the exact syntax, Evaluate Formula reveals each calculation step, and calculation settings tell you whether the result is stale rather than wrong.

Use Error Checking and the formula bar first

  1. Select the problem cell. Look at the formula bar before changing anything. You need to see whether the cell contains a formula, a pasted value, a leading apostrophe, or plain text that only looks like a formula.
  2. Run Error Checking. Open the Formulas tab and use Error Checking. Excel can catch inconsistent formulas, unlocked cells containing formulas, and some direct error conditions without forcing a rewrite.
  3. Inspect visible syntax. Confirm the leading equals sign, closing parentheses, operators, and range references. Check whether references use A1 syntax, ranges such as A1:A10, or structured references such as Table1[Qty].

Trace inputs and outputs

Trace Precedents draws arrows to cells that feed the formula. Trace Dependents shows which cells rely on the selected result. Together they answer two separate questions: where the current formula gets data, and what else will change if you fix it.

Say your total in G18 is wrong after a paste. Trace Precedents may show one arrow skipping F18 and pointing to F19 instead. That confirms a shifted relative reference without any syntax error.

Use Evaluate Formula for step-by-step calculation

Evaluate Formula is the best built-in tool for wrong values with no error code. It resolves one part of the formula at a time, so you can see whether Excel is reading a date as text, returning a blank from an IF branch, or pulling an unexpected row from a lookup.

Check calculation mode and Show Formulas

If several formulas stay unchanged after edits, check calculation mode before touching the syntax. In Manual mode, Excel can show a valid but old answer until you recalculate. Also check Show Formulas when cells display formulas across the sheet instead of results.

Why is my Excel formula showing as text?

Why is my Excel formula showing as text?
A desktop monitor and laptop display spreadsheet tables on a desk

Excel shows a formula as text when the cell is formatted as Text, the entry begins with a leading apostrophe, Show Formulas is turned on, or the pasted formula uses separators Excel does not parse in that locale. The fix depends on which pattern you confirm.

Text format and leading apostrophes

If a cell is in Text format, Excel stores =SUM(A1:A3) as characters instead of calculating it. The same happens when the formula starts with an apostrophe, such as '=SUM(A1:A3).

  1. Check the cell format. Select the cell and confirm whether the Number Format box says Text. Text format stops Excel from evaluating the entry as a formula.
  2. Change to General. Switch the format to General. This removes text-only handling for future interpretation of the cell contents.
  3. Re-enter the formula. Press F2, remove any leading apostrophe, and press Enter. Changing format alone often does not recalculate an already stored text string.

Show Formulas view

If every formula on the worksheet appears instead of results, the issue is usually Show Formulas. This is a worksheet display setting, not a formula defect. Toggle it off and the formulas will display their results again.

Pasted formulas with hidden characters

Formulas pasted from email, chat, PDFs, or web pages can contain nonbreaking spaces or other nonprinting characters. Those pasted characters can stop a formula from parsing correctly, even when the visible text looks normal in the cell.

Suppose a pasted formula looks like =IF(A2>0,1,0) but will not calculate. If the formula came from a webpage, a nonstandard space before 1 can turn a valid formula into text or trigger a parse problem. Retyping the affected character often fixes it faster than rebuilding the whole formula.

Locale separators and decimal settings

Excel does not use the same argument and decimal separators in every region. One setup may expect =SUM(A1,B1), while another expects =SUM(A1;B1). Decimal symbols also vary, so 1,5 may be read as text where Excel expects 1.5.

When a formula works on one machine and fails after paste on another, separator mismatch is a prime suspect. Check a simple function manually. If Excel accepts semicolons and rejects commas, convert the pasted formula to the local separator style.

Why is my Excel formula not showing an answer?

If a formula is valid but no answer appears, the usual causes are manual calculation mode, a formula designed to return an empty string, a number format that hides the displayed value, or an external reference that cannot refresh source data. Each leaves a different trace in Excel.

Manual calculation and recalculation

Manual mode creates stale results. The formula is still there, but it does not update after inputs change until Excel recalculates the workbook.

  1. Change one input. Edit a cell that feeds the formula and note whether the result changes. This separates a stale answer from a wrong formula.
  2. Press F9. Recalculate the workbook with F9. If the result updates now, the formula was not broken; calculation timing was.
  3. Set Automatic mode. Go to calculation options and switch to Automatic. Use Calculate Now or a full recalc if the workbook still holds old results.

Formula returns blank by design

Many workbooks intentionally suppress output with logic such as =IF(A2="","",A2*B2). That cell is not empty; it contains a formula that returns an empty string. Check the formula bar before assuming Excel lost the result.

Number formats that hide results

A custom format can hide a value even when the formula returns one. For example, formats can suppress zeros or make dates and numbers look blank. Switching temporarily to General format is a quick way to see the underlying result.

Formulas linked to other workbooks can fail quietly when the source file moved, renamed, or is not available during refresh. Some formulas then show an old cached value; others return an error. Inspect the external path in the formula bar and refresh links deliberately.

Why does my formula return 0, blank, or the wrong value?

These are the hardest formula problems because Excel may show no error code. Common causes are empty or text-valued inputs, hidden spaces, changed relative references, date and number coercion issues, IF logic that suppresses output, or structured references behaving differently inside Tables.

Empty cells, text numbers, and hidden spaces

A sum or multiplication formula can return 0 when the source cells look filled but actually contain text, spaces, or formulas returning empty strings. Functions that expect numbers may ignore text numerals or treat blanks differently than you expect.

A common first-timer mistake is importing values like 250 with a trailing space into a 500-row sheet. The cells look numeric, yet lookups miss matches and arithmetic can behave differently across functions. Testing one suspect input with ISTEXT or re-entering it often reveals the issue.

Relative versus absolute references

Copying a formula down or across can shift the lookup column, criteria cell, or total range. If the formula should always use tax rate $B$1 but you copied a relative reference B1, later rows may point at blanks or unrelated values.

Wrong values after fill are often reference problems, not function problems. Compare the original cell and copied cell side by side in the formula bar.

Date and number coercion problems

Excel stores real dates as serial numbers. If one cell holds a true date and another holds text that looks like a date, comparisons, lookups, and arithmetic can fail. The same issue appears with decimals when locale settings differ.

Check whether the cell aligns like text, changes when you switch to General format, or returns TRUE for ISTEXT. Convert text dates and text numbers before changing the formula logic.

Structured references and table context

Table formulas use header-based structured references such as =[@Qty]*[@Price] or =SUM(Sales[Amount]). These are reliable, but header renames, copied formulas moved outside the table, or mixed A1 and structured syntax can change behavior.

Within a table, Excel may automatically fill the whole column with one formula. Outside the table, that same formula may lose row context. If a formula broke after a column was renamed, inspect whether the structured reference still matches the current header text.

How do I fix Excel error codes?

Error codes point to narrower causes than silent wrong values do. Read the code first, then confirm the matching pattern: divide-by-zero inputs, wrong data types, broken references, misspelled names, no-match lookups, blocked spill ranges, or range intersections that do not exist.

#DIV/0!, #VALUE!, and #NULL!

#DIV/0! means the formula is dividing by zero or by an empty cell. Confirm the denominator and decide whether zero is valid input or should be trapped with IF logic.

#VALUE! usually signals the wrong data type in an operation or function. Text where a number is expected is common. Evaluate Formula helps isolate which argument fails.

#NULL! often appears when a range intersection is written with a space between ranges that do not intersect, such as A1:A3 C1:C3. Replace the operator with the intended separator, often a colon for a range or a comma or semicolon for separate arguments.

#REF!, #NAME?, and #N/A

#REF! means the formula points to a reference that no longer exists. Deleted rows, deleted sheets, moved external files, and broken named ranges are common triggers.

#NAME? means Excel does not recognize the function or defined name. This can happen after a typo, when a workbook uses a newer function in an older version, or when a named range was misspelled.

#N/A usually means a lookup did not find a match. Check for hidden spaces, text-versus-number mismatches, and exact-match settings before wrapping the formula in error handling.

#SPILL! and dynamic arrays

Dynamic array formulas return multiple results into a spill range. #SPILL! appears when something blocks that spill: existing values, merged cells, or a table that does not allow spill expansion in the same way as a normal range.

Older workbook patterns can also clash with spilled formulas. Implicit intersection used to reduce arrays to a single value in many situations; newer Excel may spill instead. If a formula changed behavior after opening an older file, check whether the formula now returns an array.

Why did the formula stop working after I pasted it?

Paste-related failures usually come from shifted relative references, pasted values replacing formulas, locale separator mismatches, broken external links, or formulas moved into or out of an Excel Table. The right fix depends on what paste changed: the text of the formula, the references, or the cell contents.

Relative references shifted during paste or fill

When you paste a formula from C2 to C20, relative references move by the same offset. That is useful until the formula should keep one input fixed. Then the copied formula can silently point at the wrong row, column, or criteria cell.

Values replaced formulas

If the formula bar shows a number or text instead of a formula beginning with =, the paste operation overwrote the formula with values. That is common after Paste Values, CSV imports, or drag-and-drop operations into formula cells.

Broken named ranges and external references

Pasting formulas between workbooks can carry references to names or external files that do not exist in the destination. The result may be #NAME?, #REF!, or a stale value if the external workbook cannot refresh.

Table formulas and structured references changed

A formula copied from a normal range into a Table may auto-fill down the whole column and convert A1 references into structured references. A formula pasted out of a Table may lose row-specific notation such as [@Amount]. Inspect the converted formula rather than assuming paste preserved the original logic.

How do I fix the formula with the smallest possible change?

The safest repair is the smallest one that matches a confirmed cause. Start with display and calculation checks, then verify references, types, and separators, and only then edit function logic. That sequence prevents needless rewrites and keeps existing formulas intact when the issue is elsewhere.

Smallest-fix workflow

  1. Classify the symptom. Decide whether you have an error code, text display, 0 or blank output, stale calculation, wrong value, or a post-paste failure. This narrows the likely causes faster than scanning syntax at random.
  2. Run one confirming test. Use the formula bar, Error Checking, Evaluate Formula, Trace Precedents, Show Formulas, or F9 depending on the symptom. One good test beats several speculative edits.
  3. Change the smallest input. Fix the cell format, remove the apostrophe, switch to Automatic calculation, clear a spill blocker, correct one reference, or replace one separator. Avoid rewriting the whole formula unless the test points to actual syntax or logic faults.
  4. Recalculate and compare. After the edit, recalculate if needed and compare the result against nearby rows or a hand-checkable example. This confirms that the fix solved the cause rather than hiding the symptom.

When to rebuild instead of patch

Rebuild only when several faults stack together, such as a pasted formula with wrong separators, broken names, and shifted references. Even then, keep the old cell for comparison while you reconstruct the logic. That makes it easier to spot where the old and new results diverge.

Keep reading

Frequently asked questions

How do I fix formulas in Excel?

Match the visible symptom to the cause before editing. If the formula shows as text, fix format or apostrophes. If results are stale, recalculate and switch to Automatic. If values are wrong, compare references, test for text numbers, and use Evaluate Formula to find the exact failing part.

How do I fix a formula in an Excel sheet?

Select the problem cell, inspect the formula bar, confirm the cause with one Excel tool, then apply the smallest change. Typical fixes include correcting a shifted reference, changing Text format to General, removing spill blockers, fixing separators, or repairing a broken named range or external link.

Why is my Excel formula not working after I paste it?

Paste can shift relative references, replace formulas with values, break external links, carry invalid named ranges, or introduce locale separator problems. Compare the pasted formula with the original in the formula bar, then fix only what changed: references, formula text, or the paste mode itself.