What Does #VALUE! Mean?
The #VALUE! error is Excel's way of saying: "One of the arguments in this formula is the wrong type." Usually this means a formula is trying to do maths on a text value — like adding "apple" to 10 — which Excel cannot do.
Unlike some errors, #VALUE! is usually visible and immediate. The tricky cases are when the wrong type is not obvious — a number that looks correct but is secretly stored as text, or a date that Excel is reading as a string.
Cause 1: Text in a Maths Formula
The most common cause. If any cell in a maths formula contains text, Excel cannot perform the calculation and returns #VALUE!.
=A1+A2+A3If A2 contains the text "N/A" or even an empty-looking cell with a space in it, this formula returns #VALUE!.
Fix: Use IFERROR or wrap each cell reference with a type check:
=SUM(A1:A3) ← SUM ignores text automatically =SUMIF(A1:A3,"<>",A1:A3) ← sums only non-blank, non-textSUM is more tolerant than the + operator — it silently ignores text and blank cells, whereas + will error immediately on any non-numeric argument.
Cause 2: Numbers Stored as Text
A cell can display a number but store it as text. When another formula tries to use it in a calculation, #VALUE! appears. This is especially common with data imported from CSV files or copied from web pages.
How to spot it: Numbers are right-aligned; text-numbers are left-aligned. A green triangle in the cell corner is another signal.
Fix: Select the affected range → click the warning icon → Convert to Number. Or use the VALUE() function: =VALUE(A1)+B1.
Cause 3: Date Stored as Text
Date formulas — like =B2-A2 to calculate days between two dates — return #VALUE! if either date is stored as text rather than as a real Excel date serial number.
Fix: Use DATEVALUE() to convert a text date to a real date: =DATEVALUE(A1). Or re-enter the date by clicking the cell and pressing Enter — sometimes this forces Excel to recognise it as a date.
Check by selecting the cell and looking at the Number Format box. Real dates show "Date" or "Short Date". Text dates show "General" or "Text".
Cause 4: Wrong Argument Type in a Function
Passing a text value to a function that expects a number, or a range where a function expects a single value, causes #VALUE!:
=SQRT("hello") ← #VALUE! — SQRT needs a number =LEFT(A1:A10, 3) ← #VALUE! — LEFT needs a single cellFix: Review the function's expected argument types. Use Excel's function tooltip (appears as you type) or press F1 with the function selected to see the help page. Ensure each argument is the correct type.
Cause 5: Extra Spaces or Non-Printing Characters
A cell that appears empty may contain a space character, a line break, or a non-printing character imported from another system. These cells look blank but are actually text, causing #VALUE! in formulas that reference them.
Fix: Use TRIM and CLEAN together to remove spaces and non-printing characters:
=TRIM(CLEAN(A1))Apply this in a helper column, paste as values, then delete the original column.
Cause 6: Array Formula Applied to Non-Array Range
Some formulas expect a single value but receive a range, or expect a range of a specific size. For example, passing two ranges of different sizes to a formula expecting arrays of equal length returns #VALUE!.
Fix: Ensure range sizes match when using array operations. For SUMPRODUCT, both arrays must have identical dimensions: =SUMPRODUCT(A1:A10, B1:B10) — both ranges must have exactly 10 rows.
The Universal Quick Fix: IFERROR
When a formula returns #VALUE! and you need the spreadsheet to continue working while you diagnose the root cause, wrap it with IFERROR:
=IFERROR(your_formula, 0) ← returns 0 on any error =IFERROR(your_formula, "") ← returns blank on any errorThis is a temporary measure — always fix the underlying cause rather than permanently suppressing the error, since IFERROR also hides genuine formula mistakes.
Frequently Asked Questions
Why does #VALUE! appear only in some rows of a copied formula?
The formula is correct but some of the cells it references contain text instead of numbers. The rows that work have real numbers; the rows that fail have text-formatted numbers or other text values. Use ISNUMBER() on the source column to identify which cells are the problem.
Can #VALUE! be caused by a problem in a different cell?
Yes — if your formula references a cell that already contains #VALUE!, your formula will also return #VALUE!. Trace back to the original source cell using Formulas → Trace Precedents.
Is there a way to find all #VALUE! errors in a large workbook?
Yes: press Ctrl+F, search for #VALUE!, change "Look in" to Values, and click Find All. For a full workbook scan across all sheets at once, use the ExcelErrorFinder audit tool.