Why Aren’t My Formulas Working in Excel? | Fix The Top Causes

You type a formula, press Enter, and… nothing. Or you get the same value no matter what you change. Or the cell shows the formula text instead of a result. Excel formulas can “break” in a bunch of different ways, and the fix depends on the symptom you’re seeing.

This article walks you through the most common causes in a clean order: settings first, then the worksheet view, then reference issues, then data-type traps, then error codes. If you follow it top to bottom, you’ll usually find the culprit in minutes.

Start With What You See In The Cell

Before you change anything, note what Excel is showing you. That one detail narrows the hunt fast.

Formula Text Shows Instead Of A Result

If the cell displays something like =SUM(A1:A5) instead of a number, Excel is treating it as text or you’re in a view mode that reveals formulas.

• Check Show Formulas: On the ribbon, look for a toggle that shows formulas across the sheet. If it’s on, turn it off so results display again.
• Check the leading apostrophe: If you see an apostrophe before the equals sign (or the formula behaves like plain text), remove the apostrophe and re-enter the formula.
• Check the cell format: If the cell is formatted as Text, switch to General, then re-enter the formula (edit the cell and press Enter).

Result Looks Frozen And Won’t Update

A “stuck” result often points to calculation settings. Excel may be set to manual calculation, so it won’t recalc when inputs change.

Check Calculation Mode First

Open your calculation settings and confirm the workbook is set to automatic recalculation. Microsoft’s steps for fixing non-calculating formulas start here: enable automatic workbook calculation.

If you need a one-time refresh after switching settings, force a recalculation from the Formulas tab. Microsoft also documents recalculation controls and options, including switching back to Automatic: change formula recalculation options.

Result Updates, But It’s Wrong

If the formula calculates but the answer is off, the issue is often a shifted reference, a hidden data type problem, or an unexpected blank/text value in the input range. The rest of this guide is built for that scenario.

Why Excel Formulas Aren’t Working After Edits Or Copy-Paste

Many formula issues start right after a copy-paste, a fill-down, inserting rows, or moving columns. Excel tries to help by adjusting references, yet it can’t read your intent. A small reference change can send the math to the wrong cells.

Absolute Vs. Relative References Got Mixed Up

If you copy a formula down a column, relative references shift by design. That’s great when you want each row to use its own inputs. It’s a mess when a reference should stay fixed.

• Spot it: A formula copied down shows different referenced cells each row, when one of them should be constant.
• Fix it: Use $ to lock the row, the column, or both. For example, $A2 locks column A, A$2 locks row 2, and $A$2 locks both.
• Quick check: Click the formula cell, then click into the formula bar and watch which parts shift when you copy to a nearby cell.

You Copied A Formula That References The Wrong Sheet

When you copy across worksheets, references can quietly start pointing to the new sheet instead of the original. This happens a lot with models that reuse the same layout on multiple tabs.

• Spot it: The formula contains a sheet name you didn’t expect, like Sheet2!B5.
• Fix it: Edit the formula and reselect the intended range on the correct sheet. If you want a fixed sheet reference, keep the sheet name as part of the reference on purpose.

Structured References Broke When A Table Was Converted

If you used Excel Tables, formulas may rely on structured references like Table1[Amount]. When a table is converted back to a range, those references can change or fail.

• Spot it: You see table-style references that no longer match anything, or you see a name-related error.
• Fix it: Convert the range back to a table, or replace structured references with standard cell ranges.

Named Ranges Point Somewhere Else Now

Named ranges are great until they drift. If a name points at the wrong range (or got deleted), formulas that use it can return odd results or fail.

• Spot it: A formula works in one workbook version but fails in another after you reorganized sheets.
• Fix it: Open the Name Manager, click the name, and verify its “Refers to” range matches what you meant.

Fast Triage Table For Common Symptoms

Use this to match what you’re seeing to the likeliest cause, then jump to the matching section below.

What You See	Likely Cause	What To Do Next
Cell shows the formula text	Show Formulas on, Text format, or leading apostrophe	Turn off formula display, set format to General, re-enter formula
Result never changes	Manual calculation mode	Switch to Automatic calculation, then recalc the sheet
Answer is off after fill-down	Relative reference shifted	Add $ locks where needed, then copy again
Result looks blank when it shouldn’t	Hidden spaces, empty strings, or error-handling returning blank	Check inputs for spaces, inspect IF/IFERROR outputs
#VALUE! appears	Wrong data type, text in numeric math, stray spaces	Convert text numbers, trim spaces, use numeric-safe functions
#NAME? appears	Misspelled function name, missing quotes, bad named range	Fix spelling, confirm quotes, verify names
#REF! appears	Deleted cells that the formula referenced	Undo deletion, restore range, or rebuild the reference
Circular reference warning	Formula refers to itself (directly or indirectly)	Move the formula or redesign the calculation flow

Data Type Traps That Make Formulas Misbehave

Excel formulas can be perfect and still fail if the inputs aren’t what they seem. The usual villains are numbers stored as text, hidden spaces, and date formats that don’t match what Excel expects.

Numbers Stored As Text

A cell can look like a number and still be text. When that happens, math operations may return errors or strange results, and comparisons may act “off.”

• Spot it: Left-aligned numbers, a green triangle warning, or SUM ignoring values you swear are there.
• Fix it: Convert text to numbers using a built-in conversion option, Paste Special (Multiply by 1), or a conversion function when you need a formula-based fix.
• Sanity check: Use =ISNUMBER(A1) in a helper cell to confirm the data type.

Hidden Spaces And Non-Printing Characters

One extra trailing space can break an exact match, cause lookup misses, and make two strings that look identical refuse to match.

• Spot it: A lookup returns not found even though the value is visible in the list, or a comparison like A1=B1 returns FALSE unexpectedly.
• Fix it: Clean the input with trimming and cleaning functions, or re-import the data with a cleaner delimiter setup.
• Quick reveal: Compare lengths with =LEN(A1). A “same” value with a different length almost always has stray characters.

Dates That Are Really Text

Date math gets weird when Excel isn’t storing dates as date serial values. You might see sorting that looks random or date differences that make no sense.

• Spot it: Dates sort alphabetically, or formulas that rely on dates produce errors.
• Fix it: Convert text dates to real dates using Excel’s date conversion tools or a formula-based conversion approach, then apply a date format.

Percentages And Currency That Lost Their Formatting

If you paste values from another system, you might get “0.15” where you expected “15%,” or “1500” where you expected “$1,500.” The formula works, the meaning doesn’t.

• Spot it: Values seem off by a factor of 100, or totals are inflated.
• Fix it: Reapply formatting, then confirm the underlying value is what you want. Formatting changes display, not the stored number.

Reference Issues That Break Working Formulas

When formulas fail after you restructure a sheet, it’s often a reference problem. Excel can adjust many references when you insert rows and columns, yet deletions and moves can leave formulas pointing nowhere.

Deleted Rows Or Columns Caused #REF!

#REF! means the formula is pointing at a reference that no longer exists. Excel can’t guess what you wanted, so it stops.

• Fast fix: Undo the deletion if it was accidental.
• Manual fix: Edit the formula and rebuild the reference by selecting the correct range again.
• Prevention: When you need to remove data, clear contents instead of deleting cells that other formulas depend on.

Ranges Don’t Match The Real Data Size

This happens when your dataset grows. Your formula still points to last month’s range, so new rows never get counted.

• Spot it: Totals look short after you add new data at the bottom.
• Fix it: Expand the referenced range, or use an Excel Table so formulas grow with the data.
• Quick check: Click the formula, then click each referenced range to see the highlight overlay.

Circular References

A circular reference is when a formula depends on its own result, directly or through a chain of other formulas. Excel warns because it can’t settle on a single value without iteration rules.

• Spot it: Excel shows a circular reference message, or results feel unstable.
• Fix it: Move the formula to a non-dependent cell, split the logic into helper cells, or redesign the calculation path so inputs flow one direction.

Error Codes That Point To The Fix

Excel error codes look cryptic at first, yet they’re helpful once you treat them as labels. Each one narrows the list of likely causes.

Error	What It Usually Means	Typical Repair
#VALUE!	A function or operator got the wrong data type	Convert text to numbers, remove hidden spaces, adjust function inputs
#NAME?	Excel doesn’t recognize something in the formula	Fix function spelling, add missing quotes, verify named ranges
#REF!	A referenced cell or range was deleted	Undo deletion or rebuild references
#DIV/0!	Division by zero or blank denominator	Guard with a check for zero/blank, then divide
#N/A	A lookup didn’t find a match	Clean the lookup value, align match types, check spaces and data types
#NUM!	Numeric result is out of range or invalid for the function	Check inputs for domain limits, fix negative/overflow cases
#SPILL!	A dynamic array can’t spill into blocked cells	Clear the spill range, remove merged cells, make room for the output

Checks That Catch Subtle Problems

If you’ve handled settings, references, and data types, the remaining issues tend to be subtle. These checks help you catch them without guessing.

Evaluate The Formula Step By Step

Excel can walk through a formula and show what it’s calculating at each stage. This is the fastest way to locate the exact part that returns an error or an unexpected intermediate value.

• Start the evaluator from the Formulas tools on the ribbon.
• Step through each part and watch for the first odd value.
• Once you find the failing segment, isolate it into a helper cell so you can see it plainly.

Check For Hidden Errors Under IFERROR

IFERROR is handy, yet it can hide the real failure and make a worksheet feel “quietly wrong.” If you see blanks or zeros where you expected real output, inspect the inner formula.

• Temporarily remove IFERROR to reveal the original error code.
• Fix the root cause, then put IFERROR back if you still want a cleaner display.

Watch For Merged Cells Blocking Results

Merged cells can interfere with spill behavior and make references harder to maintain. If you’re using dynamic arrays or expecting results to fill a range, merged cells are a common blocker.

• Unmerge the target area where results should appear.
• Use alignment settings to mimic the look of merged cells without merging.

Confirm You’re Not Editing A Different Workbook Or Sheet

It sounds obvious, yet it happens a lot: two similarly named files are open, or you’re editing a copied tab, and you’re watching the wrong result cell.

• Check the workbook name in the title bar.
• Color-code tabs for drafts vs. live sheets, so you can spot copies instantly.
• Use a small “version” cell in a corner of the live sheet to confirm you’re on the right one.

Build Formulas That Stay Stable

Once your formulas work again, a few habits can prevent the same pain next week.

Use Helper Cells For Long Logic

One massive formula is hard to debug. Splitting the logic into clear steps makes errors easier to spot and keeps your future edits safer. A short chain of readable helper cells also makes auditing faster when someone else opens the file.

Turn Ranges Into Tables When The Data Grows

Tables reduce the “my range didn’t expand” problem. They also make formulas easier to read and keep references tied to column meaning instead of raw coordinates.

Validate Inputs Where Users Type Data

Many failures start with bad inputs: stray spaces, wrong types, or out-of-range values. Data validation can block the worst entries before they reach your formulas.

Keep A Small Debug Row Or Column

A compact set of checks can save hours. For example: a cell that reports calculation mode, a cell that flags whether a range contains text numbers, and a quick check for blanks in critical inputs. Keep it off to the side so it doesn’t clutter the main view.

When you treat formula problems as a short diagnostic chain instead of a guessing game, Excel gets a lot less frustrating. Start with calculation settings, confirm the sheet view, then work through references and data types. One of those usually flips the switch back on.