Fix the #VALUE! Error

Excel & Google Sheets

How it works

#VALUE! appears when a formula receives the wrong kind of data — most often text where it expected a number or date. The text can be invisible: a number with a stray space ("42 "), a date typed as "July 8", or an empty-looking cell that actually contains a space. The quick diagnostic is ISNUMBER: point it at each input cell, and the one that says FALSE is your culprit. Then fix the source — convert text numbers with VALUE(), re-enter text dates as real dates, and clear cells that hold lone spaces.

ISNUMBER(B2)

Diagnostic: returns FALSE if B2 holds text pretending to be a number or date.

=VALUE(B2)

Fix: converts a text number like "42" into the real number 42.

When to use it

Reach for this diagnosis whenever arithmetic or date math errors out right after importing or pasting data — imports are the number-one source of text-formatted numbers.

Common mistakes

• ✕Fixing the formula when the data is the problem.

  ✓Rewriting the formula won't help if B2 holds text. Diagnose inputs with ISNUMBER first, then clean the data.

• ✕A cell that looks empty but contains a space.

  ✓Arithmetic on " " gives #VALUE!. Select the cell and press Delete, or check with =LEN(B2).

• ✕Suppressing it with IFERROR and moving on.

  ✓=IFERROR(A2*B2,"") blanks the error but your totals are now silently missing rows. Fix the source data.