Excel #VALUE! Error: Find the Text or Space Breaking Your Formula

A formula returns #VALUE! instead of a number.

Why: #VALUE! is Excel’s way of saying there’s something wrong with the way your formula is typed, or with the cells it references. The usual culprit is arithmetic on something that isn’t a number — a cell holding text, a stray space, or a hidden non-printing character. Math operators like + and * can’t add text, so they error out.

Fix 1: Use functions instead of + and * operators

Formulas with +, -, or * choke when a referenced cell contains text or a space. Functions like SUM and PRODUCT simply ignore text and treat blanks as zero, so they sail past the problem.

Errors on a text cell: =A2+B2+C2
Works:                 =SUM(A2:C2)

Errors on a text cell: =A2*B2
Works:                 =PRODUCT(A2,B2)

This often clears the error instantly without you having to find the bad cell first.

Fix 2: Remove spaces with Find & Replace

A cell that looks blank may hold a space, and =A2+B2 then errors. Clear spaces across the range in one pass:

1. Select the cells the formula references.
2. Go to Home > Find & Select > Replace (or press Ctrl + H).
3. In Find what, type a single space. Leave Replace with empty.
4. Click Replace All.

If spaces are part of real data (like names) and you can’t blanket-delete them, use TRIM to drop leading/trailing spaces and CLEAN to strip non-printing characters: =TRIM(CLEAN(A2)) in a helper cell, then paste the result back as values.

Fix 3: Pinpoint the bad part with Evaluate Formula

When the formula is long and you can’t tell which reference is the offender, have Excel walk through it.

1. Select the cell showing #VALUE!.
2. Go to Formulas > Evaluate Formula.
3. Click Evaluate repeatedly. Excel solves one piece at a time; the step that turns into #VALUE! is your culprit — note that cell and check it for text or spaces.

Fix 4: Fix dates stored as text

Date math (=B2-A2 for days between) returns #VALUE! when a “date” is really text Excel never parsed. A quick check: true dates sit right-aligned in the cell; text dates sit left-aligned.

Select the column, choose Data > Text to Columns > Next > Next, pick Date under Column data format, then Finish to convert the whole column to real dates. To convert one value in a formula, wrap it in DATEVALUE: =DATEVALUE("2025-10-13").

FAQ

Can I just hide the error? You can wrap the formula in IFERROR, e.g. =IFERROR(A2+B2+C2,"--"), but that masks the problem rather than fixing it. Find the bad cell first, then add IFERROR only if blanks are legitimate.

The cell looks empty but still errors. It probably holds a space or a stray apostrophe/non-printing character. Use Fix 2 to clear spaces, or press Delete on the cell to wipe hidden characters.

SUM ignores my text cell but I need that value counted. Then the value should be a number — convert it. Click the cell’s warning triangle and choose Convert to Number, or use Text to Columns as in Fix 4.

Sources: Microsoft Support — How to correct a #VALUE! error · Microsoft Support — How to correct a #VALUE! error in AVERAGE or SUM functions