#VALUE! Error
The Swiss Army knife of Excel errors — appears whenever a formula receives the wrong type of data. Text where a number was expected, a range where a single value belonged, a date that isn't really a date. Here's every cause and every fix.
Signals a data type mismatch. Excel expected one kind of value (usually a number) but got something else (usually text or an empty cell). The formula itself is syntactically valid — it's the inputs that are wrong.
What #VALUE! means
#VALUE! is Excel telling you: "Your formula is written correctly, but the data going into it doesn't match what I need." Multiplication expects numbers; if it gets the word "twenty" instead of 20, you get #VALUE!. A function that expects a single cell reference gets a whole range; #VALUE!. A date function receives a text string that only looks like a date; #VALUE!.
Because the error is about data quality rather than formula syntax, the fix is almost always upstream — clean the source data, convert types explicitly, or wrap the input in a coercion function. The formula itself usually stays the same. This is what makes #VALUE! tricky: the error appears in the formula cell, but the problem is somewhere in the referenced data.
🔍 Fastest diagnostic trick
Click the cell showing #VALUE!, then look for the yellow warning triangle that appears at the corner. Click the dropdown arrow next to it and choose "Show Calculation Steps" or "Trace Error". Excel highlights exactly which input triggered the type mismatch. This turns 20 minutes of debugging into 20 seconds.
The 6 root causes of #VALUE!
Math operators (+, −, *, /) and math functions (SUM, AVERAGE, etc. via arithmetic) can't process text. Doing =A1+B1 where A1 contains "10" as text and B1 contains 5 as a number gives #VALUE!. Note: SUM and AVERAGE are smart enough to ignore text; direct arithmetic isn't.
Cells that look empty can contain spaces or non-printing characters that make Excel treat them as text. The cell displays nothing but arithmetic returns #VALUE!. Common in data pasted from PDFs, websites, or old databases.
Functions like DATEVALUE, LEN, VALUE expect one cell. If you pass A1:A10 instead of A2, you get #VALUE!. Common when copying formulas — the range accidentally expands.
Subtracting dates requires actual date values. If your dates were imported as text like "2026-01-15", arithmetic returns #VALUE!. Test with =ISNUMBER(A1) — dates should return TRUE.
You entered a formula with array syntax (curly braces or SEQUENCE-style) into a function expecting a scalar. Common when learning dynamic arrays and mixing old/new patterns.
A formula depends on its own output through a chain of references. Excel sometimes returns #VALUE! during the resolution attempt before eventually catching the circular reference warning.
5 real-world fix scenarios
Numbers won't add up
Column of prices imported from a CSV file. Each cell displays a number but SUM returns 0 or arithmetic gives #VALUE!.
Root fix: select the column, Data → Text to Columns → Finish. Converts all text-numbers to real numbers in one click.
Cells look empty but break arithmetic
Cells that appear blank but contain a space or tab character. Arithmetic on them returns #VALUE!.
Root fix: Find & Replace → find a space, replace with nothing, then re-test. Or use =CLEAN() to strip non-printing characters.
DATEVALUE returns #VALUE! on a range
You wrote DATEVALUE thinking it would convert a whole column of text-dates to real dates.
Modern alternative: in Excel 365, use MAP or BYROW to apply DATEVALUE across an array. But copying down a single-cell formula still works fine.
Combining text and dates displays serial numbers
Related to #VALUE! chains — sometimes concatenation of a date with text returns numeric serial like 45678 instead of the date, and downstream formulas break.
Root fix: always wrap dates in TEXT() when concatenating. Same principle for numbers with decimals.
When you can't fix the source data
Sometimes the source data is mixed and cleaning it isn't feasible. Wrap the formula in IFERROR to display a friendly value instead of #VALUE!.
Warning: IFERROR hides the problem — good for cosmetic dashboards, bad for data quality auditing. Use with care.
Prevention checklist
Ways to stop #VALUE! before it happens
- Use SUM instead of +. SUM ignores text values; direct arithmetic doesn't.
=SUM(A1:A10)is safer than=A1+A2+A3+... - Apply data validation to input cells. Restrict cells to whole numbers or dates. Users can't accidentally type text.
- Standardize CSV imports. Use Data → Get Data → From Text/CSV. The Power Query import lets you specify column types before loading.
- Add ISNUMBER checks in critical formulas. Wrap with
=IF(ISNUMBER(A1), A1*B1, 0)for defensive calculations. - Convert Excel Tables early. Tables enforce consistent column types better than raw ranges.
- Turn on background error checking. File → Options → Formulas → check "Enable background error checking". Yellow warning triangles help you spot issues before they cascade.
#VALUE! vs other formula errors
Version compatibility
The #VALUE! error has existed since Excel's earliest versions and behaves identically across all modern Excel platforms.
Related errors and resources
Frequently asked questions
Why does my SUM return 0 but arithmetic gives #VALUE!?
SUM is designed to ignore text values silently. Direct arithmetic (+, −, *, /) requires all operands to be numbers and returns #VALUE! if any are text. If SUM returns 0 unexpectedly, your "numbers" are stored as text — same root cause, different symptom.
How do I check if a cell contains text or a number?
Use =ISNUMBER(A1) which returns TRUE for real numbers and FALSE for text. Also useful: =ISTEXT(A1) and =TYPE(A1). Format the cell as General and you'll also see: numbers align right by default, text aligns left.
Should I always wrap formulas in IFERROR?
No. IFERROR hides errors — great for presenting a polished dashboard, terrible for catching data quality issues. Use IFERROR at the presentation layer only. In the calculation layer, let errors surface so you can fix them properly.
Why does copy-paste sometimes cause #VALUE!?
Two reasons. First: the paste may have shifted a range reference to include cells outside the intended data (which are text or blank). Second: the paste may have converted formulas to values incorrectly, leaving text remnants. Undo, then paste as values instead.
Can #VALUE! appear intermittently?
Yes. If a linked cell updates from an external source (Power Query, database, other workbook) and momentarily contains text before being converted, formulas depending on it flash #VALUE! then recover. Wrap in =IFERROR(formula, "") to smooth this out, or check the source connection timing.
Why do dates sometimes cause #VALUE! errors?
Because dates that look like dates aren't always dates. When imported from CSV or copied from PDFs, "2026-01-15" is often just text. Date arithmetic on text returns #VALUE!. Test with =ISNUMBER(A1) — real dates return TRUE. Convert with DATEVALUE if needed.
Does #VALUE! affect PivotTables and charts?
Yes. #VALUE! in source data usually blocks PivotTable creation or causes chart segments to disappear. Clean the source first — filter to find the offending rows, fix them, refresh the PivotTable/chart.
Diagnose #VALUE! in seconds.
Paste the broken formula and the Add-in identifies the exact input causing the error, then rewrites the formula to be #VALUE!-proof.
Get the Add-in →