Excel errors / #VALUE!
#VALUE! error in Excel
The formula was given the wrong kind of value, such as text where it needs a number.
#VALUE! means a formula was given something of the wrong kind. Most often it is text where a number is needed: a price typed as "12 USD", a cell holding "n/a", a date that Excel reads as words.
It is the vaguest of Excel's errors, because many different things produce it. The quickest way to find the cause is to look at each cell the formula uses and ask whether it really holds a number.
Have a formula that shows #VALUE!? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.
6 causes of #VALUE!, and how to fix each
Text mixed into a number
A unit, a currency code or a stray letter turns a number into text. Excel can multiply 12 by 3; it cannot multiply "12 USD" by 3.
A B C D 1 Price Qty 2 12 USD 3 #VALUE! In D2:
=A2*B2→ #VALUE!How to fix it. Keep only the number in the cell and put the unit in the heading or in the number format. To calculate with the data as it is, strip the extra text out first.
A B C D 1 Price Qty 2 12 USD 3 36 In D2:
=VALUE(SUBSTITUTE(A2, " USD", ""))*B2→ 36One cell of text in a row of numbers
Adding cells with + fails as soon as one of them holds text, such as "n/a" or a dash for a missing figure.
A B C D E 1 Jan Feb Mar 2 120 n/a 150 #VALUE! In E2:
=A2+B2+C2→ #VALUE!How to fix it. Use SUM. It adds the numbers and skips text, so one "n/a" does not break the total.
A B C D E 1 Jan Feb Mar 2 120 n/a 150 270 In E2:
=SUM(A2:C2)→ 270A cell that only looks empty
A cell with a single space in it looks blank but holds text. A truly empty cell counts as 0 in arithmetic; a space gives #VALUE!. In the sheet below, A2 holds a space.
A B C D 1 Qty Bonus 2 5 #VALUE! In D2:
=A2+B2→ #VALUE!How to fix it. Select the cell and press Delete to clear it. To find them all, use Find and Replace: search for a single space with "Match entire cell contents" ticked, and replace with nothing.
A B C D 1 Qty Bonus 2 5 5 In D2:
=A2+B2→ 5A date stored as text
Excel can subtract dates because a real date is a number. "March 15" typed as words is text, and so is a date in a format your regional settings do not recognise.
A B C D 1 Start End 2 2026-03-01 March 15 #VALUE! In D2:
=B2-A2→ #VALUE!How to fix it. Retype the date in a form Excel recognises, or convert a column of them with Data > Text to Columns. A real date sits on the right of its cell.
A B C D 1 Start End 2 2026-03-01 2026-03-15 14 In D2:
=B2-A2→ 14Ranges of different sizes
SUMIFS, COUNTIFS, SUMPRODUCT and XLOOKUP pair their ranges up row by row. If one range has three rows and another two, the pairs do not line up.
A B C D 1 Region Sales 2 North 100 #VALUE! 3 South 80 4 North 60 In D2:
=SUMIFS(B2:B4, A2:A3, "North")→ #VALUE!How to fix it. Make every range start and end on the same rows.
A B C D 1 Region Sales 2 North 100 160 3 South 80 4 North 60 In D2:
=SUMIFS(B2:B4, A2:A4, "North")→ 160FIND or SEARCH did not find the text
FIND and SEARCH return #VALUE!, not #N/A, when the text they look for is not there. Any formula built on them fails with it.
A B C 1 Name 2 Ana Silva #VALUE! In C2:
=LEFT(A2, FIND("-", A2)-1)→ #VALUE!How to fix it. Decide what should happen when the text is missing, and say so with IFERROR.
A B C 1 Name 2 Ana Silva Ana Silva In C2:
=IFERROR(LEFT(A2, FIND("-", A2)-1), A2)→ Ana Silva
Hiding it on purpose
To add up a range that contains #VALUE! errors without fixing them, use =AGGREGATE(9, 6, A2:A20): the 9 means sum and the 6 means ignore errors. Better still, fix the cells, since an error in the data usually means a figure is missing.
Common questions
How do I fix #VALUE! in Excel?
Find the cell that holds text where the formula needs a number. Click the formula, then check each cell it refers to: text sits on the left of a cell and numbers on the right. Look for units typed with the number, a space in an "empty" cell, and dates stored as text.
Why do I get #VALUE! when I add cells?
One of the cells holds text. =A2+B2+C2 fails if any of the three is text, even a single space. =SUM(A2:C2) gives the total of the numbers and ignores the text.
How do I check whether a cell is a number or text?
Use =ISNUMBER(A2). TRUE means Excel can calculate with it. FALSE for something that looks like a number means it is stored as text; =VALUE(A2) converts it.
Practise it
Functions involved
- VALUE: Converts text that looks like a number into a number.
- SUM: Adds up numbers, cells and ranges.
- ISNUMBER: TRUE if the value is a number (dates count too).
- ISTEXT: TRUE if the value is text.
- SUBSTITUTE: Replaces specific text with new text.
- IFERROR: Returns a fallback value if a formula results in an error.
- SUMIFS: Adds the cells that meet every one of several conditions.
Other errors
- #N/A: A lookup could not find the value it was given.
- #REF!: The formula refers to a cell that no longer exists.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NAME?: Excel does not recognise a word in the formula.
- #NUM!: The calculation has no valid number as its answer.
- #SPILL!: A formula that returns several values has no room to put them.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.