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: Check whether ignoring nonnumeric cells is intended

SUM and PRODUCT ignore text and empty cells in cell references. This can be useful, but it changes how missing inputs are handled. PRODUCT does not treat a blank factor as zero: if A2 is 5 and B2 is blank, =A2*B2 returns 0, while =PRODUCT(A2,B2) returns 5. Validate or convert required inputs rather than silently omitting them.

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

Errors on a text cell: =A2*B2
Ignores text/blanks:   =PRODUCT(A2,B2)

Use these replacements only when ignoring nonnumeric entries matches the calculation you intend. For a required price, quantity or other factor, find the invalid input with Fix 3 and correct it.

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

Additional sources: Microsoft — PRODUCT function