#VALUE!
#VALUE! Error in Excel — Causes & Fixes
Excel returns #VALUE! when a formula receives the wrong data type — for example, trying to add a number to a text string.
Why this happens
Common causes of #VALUE!
Math on text cells
Adding, subtracting, or multiplying a cell that contains text (including an empty space " ") will return #VALUE!.
Dates entered as text
Dates typed as "Jan 1 2024" instead of a proper date format are stored as text. Date arithmetic on these cells returns #VALUE!.
Array argument length mismatch
Functions like SUMPRODUCT return #VALUE! when the arrays passed as arguments have different row or column counts.
Space characters in numeric cells
A cell with a leading or trailing space looks numeric but is actually text. Even a single invisible space will break arithmetic formulas.
How to fix it
Step-by-step fixes for #VALUE!
Convert text to numbers with VALUE()
=VALUE(A2) converts a text representation of a number into a real number. Alternatively, multiply by 1: =A2*1.
Remove spaces with TRIM()
=TRIM(A2) removes leading, trailing, and extra internal spaces. Wrap TRIM around the cell reference inside your formula.
Convert text dates with DATEVALUE()
=DATEVALUE(A2) converts a date stored as text into a proper date serial number that supports arithmetic.
Use IFERROR to catch and log the error
Wrap the formula in IFERROR to show a blank or message while you debug: =IFERROR(your_formula,"Check data type").
Frequently asked questions
How do I find which cell is causing #VALUE!?
Use Formulas > Error Checking > Trace Error to highlight the cells feeding into the error. The Evaluate Formula tool (Formulas tab) steps through the calculation to pinpoint the bad value.
Why does a blank cell cause #VALUE!?
A blank cell is not the same as zero in all contexts. If the formula expects a number and gets an empty string, it returns #VALUE!. Use IF(ISBLANK(A2),0,A2) to substitute zero for blank cells.
Related function references
These functions appear most often in formulas that produce #VALUE!.
Other Excel errors