How to Fix the Excel #VALUE! Error

#VALUE! means a formula received a value of the wrong type — most commonly, it expected a number or a date and got text instead. The error is rarely in the formula's logic; it is usually in one of its inputs. Find that input first, before rewriting the formula.

Verification note: These steps describe standard Excel behaviour. They have not yet been verified against a live Excel 365 file for this site, and no sample workbook is published yet. Work on a copy of your file and test each fix on the failing cell before applying it across a range.

Diagnose first: which input is the wrong type?

For each cell the failing formula refers to, test it in a blank cell:

=ISNUMBER(A2)
=ISTEXT(A2)

ISNUMBER TRUE — a real number (or date). That input is probably not the problem.
ISTEXT TRUE — text. If the formula needs a number or date from this cell, this is your prime suspect.

Work through the inputs one at a time. If a formula refers to a range, test a few cells in that range — a single text cell in a range can break the whole calculation, and it is often the one you did not look at.

Common causes, in the order to check them

Cause	How to spot it	Fix
Numbers stored as text	ISNUMBER FALSE, ISTEXT TRUE, on a cell that looks like a number; SUM ignores it	See numbers stored as text: Text to Columns, or VALUE(), after cleaning spaces
Dates stored as text	ISNUMBER FALSE on a cell that looks like a date; date arithmetic fails	See date formats: convert with Text to Columns and the correct DMY/MDY choice
Spaces or invisible characters	The cell looks empty or looks like a number, but LEN returns 1 or more, or ISNUMBER is FALSE	Clean with `=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))` — TRIM alone does not remove CHAR(160) — then convert
Text in an arithmetic formula	A formula like `=A2+B2` where one input is text, or a formula that concatenates and then calculates	Convert the text input to a number first, or restructure the formula so text and numbers are not added together
Wrong argument type for a function	The function expects a number, date, or range and receives text, a single value instead of a range, or vice versa	Check the function's arguments one by one against what it expects; rebuild the formula piece by piece in a scratch cell until the piece that breaks is found
A formula returning text feeding another formula	An intermediate formula returns `""` or a quoted number, and the next formula calculates with it	Change the intermediate formula to return a number (or 0) where a number is needed, or wrap it in VALUE() only if the text is a plain number

Worked diagnostic, in order

Note the failing formula and list every cell and range it refers to.
Run ISNUMBER/ISTEXT on each input. Mark every text input.
For each text input, ask: should this be a number, a date, or text? If it should be a number, use the text-number fix. If it should be a date, use the date fix. If it should be text, the formula — not the input — needs to change.
Fix one input at a time and re-check the formula. If it still shows #VALUE!, there is another wrong-type input — repeat from step 2.
If every input tests as the right type and the error remains, rebuild the formula piece by piece in a scratch cell: enter each part separately until the part that returns #VALUE! is isolated, then check that part's arguments.

What NOT to do

Do not wrap the whole formula in IFERROR just to hide #VALUE!. That hides the error without fixing the wrong-type input, and the wrong values will flow into totals, reports, and decisions.
Do not bulk-convert a column without checking it first. A column of product codes that look numeric should stay as text — converting it can destroy leading zeros.
Do not assume the first input you check is the only problem. A range with one text cell among many numbers is a common cause, and it is easy to miss.

← Back to home