How to Fix #VALUE! Error in Excel — 6 Causes & Solutions

The #VALUE! error means Excel expected one data type but got another. It's the most common formula error — and usually the easiest to fix once you know where to look.

Cause 1: Text in a Math Formula

The problem: You're trying to add, subtract, or multiply a cell that contains text.

=A2+B2   → #VALUE! if B2 contains "N/A" or "TBD" or any text

The fix:

=A2 + VALUE(B2)              — if B2 should be a number
=A2 + IF(ISNUMBER(B2),B2,0)  — treat text as zero

Cause 2: Spaces in "Empty" Cells

The problem: A cell looks empty but contains a space character. SUM ignores it, but formulas like DATEVALUE or direct math fail.

How to spot it: Click the cell → look at the formula bar. If you see the cursor blinking after a space, the cell isn't truly empty.

The fix:

=IF(TRIM(A2)="", 0, A2*B2)

Or clean the whole column: Find & Replace → Find: " " (space) → Replace with: (nothing).

Cause 3: Date Formatted as Text

The problem: You're doing date math (like =B2-A2) but one of the dates is stored as text, not an actual date serial number.

How to spot it: Text dates align LEFT. Real dates align RIGHT.

The fix:

=DATEVALUE(A2)    — converts text date to real date
=VALUE(A2)        — also works for numeric text

Or: select the column → Data → Text to Columns → Finish → then format as Date.

Cause 4: Using Wrong Argument Type

The problem: A formula expects a number but you passed a range, or vice versa.

=LEFT(A2, B2:B10)   → #VALUE! — LEFT needs a single number, not a range
=FIND("x", 123)    → #VALUE! — FIND needs text, not a number

The fix: Check each argument. Does the formula expect a single value or a range? A number or text?

Cause 5: Array Formula Not Entered Correctly

The problem: In older Excel (before 365), array formulas need Ctrl+Shift+Enter, not just Enter.

The fix: Select the cell → press F2 to edit → press Ctrl+Shift+Enter. You'll see curly braces {} around the formula in the formula bar.

In Excel 365 / 2021, this is not needed — dynamic arrays handle it automatically.

Cause 6: CONCATENATE or & with Error Cells

The problem: If any cell in a CONCATENATE or & join contains an error, the whole result becomes #VALUE!

=A2 & " - " & B2   → #VALUE! if B2 has an error

The fix:

=A2 & " - " & IFERROR(B2, "")

Quick Diagnostic Steps

1. Evaluate formula step by step: Select the formula → Formulas tab → Evaluate Formula. Excel shows which part fails.
2. Check data types: Use =ISNUMBER(A2) and =ISTEXT(A2) to verify what's in each cell.
3. Look for invisible characters: =LEN(A2) — if an "empty" cell shows LEN > 0, it has hidden content.
4. Use IFERROR as a temporary wrapper: =IFERROR(your_formula, "CHECK") to isolate which rows have the problem.

Prevention Tips

• Use Data Validation to restrict input to numbers where needed
• Clean imported data with TRIM and CLEAN before using in formulas
• Use Text to Columns after pasting data from other systems
• Always wrap risky formulas in IFERROR