How to fix #VALUE! in Excel
=ISTEXT(B2) or =LEN(B2) and clear it. For totals, =SUM(B2,C2) skips text where =B2+C2 fails.#VALUE! means Excel was given a kind of value it can't use, most often text where it expected a number. The text might be obvious, like "n/a", or invisible, like a single space.
Causes and fixes
- Text in a calculation
- A space or empty text that looks blank
- Ranges of different sizes in SUMPRODUCT, FILTER or SUMIFS
- Dates stored as text
- The #VALUE! comes from another cell
1Text in a calculation
Math operators (+ - * /) need numbers. If any cell they use contains text such as n/a, TBD, - or 5 kg, the result is #VALUE!.
How to recognise it
Click the formula and look at each cell it uses. =ISTEXT(B2) returns TRUE for the problem cell. Formulas > Evaluate Formula shows which step fails.
Fix
Replace the text with a number or clear it. If text is allowed in that column, calculate only when the cell holds a number:
=IF(ISNUMBER(B2),B2*C2,0)For totals, use SUM instead of plus signs, because SUM skips text in cell references:
=SUM(B2,C2,D2)To pull the number out of text like 5 kg (Microsoft 365): =--TEXTBEFORE(B2," ").
2A space or empty text that looks blank
A cell that looks empty may contain a space, or a formula that returns "". Both are text, so =B4*C4 returns #VALUE!. A truly empty cell would count as 0.
How to recognise it
=LEN(B4) returns 1 or more, and =ISBLANK(B4) returns FALSE.
Fix
Select the cells that should be empty and press Delete. For many cells, use Find & Replace (Ctrl+H) with a single space in Find what and nothing in Replace with, but only on columns where spaces never belong, such as a numbers column.
If the "blank" comes from another formula, handle it in the calculation:
=IF(TRIM(B2)="",0,B2*C2)Try it live: A blank-looking cell that isn't blank
B4 looks empty but holds a space, so D4 shows #VALUE! and the total in D7 does too. Click the examples to check B4 with LEN and to make the formula skip text. You can also select B4 and press Delete.
3Ranges of different sizes in SUMPRODUCT, FILTER or SUMIFS
How to recognise it
The formula uses two or more ranges, and one is a row longer or shorter than the others:
=SUMPRODUCT(B2:B10,C2:C11)
=FILTER(A2:C10,B2:B11="East")
=SUMIFS(C2:C10,A2:A11,"East")Fix
Make every range cover the same rows:
=SUMPRODUCT(B2:B10,C2:C10)Prevent it. Use table columns, such as Sales[Qty] and Sales[Price]. They're always the same length.
4Dates stored as text
Dates from exports often arrive as text, especially in formats your Excel doesn't recognise, such as 2026.10.28 or 28/10/2026 on a US system. Subtracting them gives #VALUE!.
How to recognise it
The dates sit on the left of the cell, changing the number format does nothing, and =ISNUMBER(A2) returns FALSE.
Fix
Select the column and use Data > Text to Columns. Click Next twice, choose Date with the order your text uses (for example DMY), and click Finish.
Or rebuild the date with a formula. For text like 2026.10.28:
=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))5The #VALUE! comes from another cell
Errors pass along: if D4 is #VALUE!, any total that includes D4 shows #VALUE! too. Fixing the total won't help. You have to fix the cell it came from.
Fix
Click the error, then Formulas > Error Checking > Trace Error. Excel draws arrows back to the source. Fix that cell and the totals recover.
If you need a total that ignores error cells for now:
=AGGREGATE(9,6,D2:D100)The 9 means SUM and the 6 means ignore errors.
Still stuck?
- Use Formulas > Evaluate Formula to see which part of the formula fails.
- Check every input with
=ISNUMBER(). - Look for cells that look empty but have
=LEN()greater than 0. - Make sure every range in SUMPRODUCT, FILTER and SUMIFS covers the same rows.
- Use Trace Error to find where an error started.
- Avoid wrapping everything in IFERROR. It hides the error but not the wrong data behind it.