#VALUE! Error in Excel: Causes and Fixes

The #VALUE! error in Excel means a formula got a value of the wrong type, usually text where it needed a number. It often comes from a word like "n/a" in a column of numbers, a cell holding only a space, or a date stored as text. This guide shows how to find the cell causing it and fix the formula, so the error doesn't come back.

What does the #VALUE! error mean?#

#VALUE! is Excel saying "I can't do this calculation with this kind of value". Math needs numbers, and date functions need dates. When a formula gets text that it can't turn into a number, it stops and shows #VALUE!.

Excel is more forgiving than you might expect: if a cell holds the text "12", then =B2*3 still gives 36, because Excel can read the text as a number. The error appears when the text can't be read as a number, like sick, n/a, - or a single space.

Cause 1: text in a cell you add, subtract or multiply#

This is the most common cause. Arithmetic signs (+, -, *, /) need numbers in every cell they touch.

The broken formula in F2:

=B2+C2+D2+E2

D2 holds the word sick, and Excel can't add a word. There are two good fixes:

1. Fix the data. If the cell should be a number, type 0 and put the note in a comment or another column.
2. Use SUM. SUM skips text cells and adds the rest:

=SUM(B2:E2)

The result is 23.5. SUM is also shorter and easier to extend. See our beginner's guide to SUM for more on ranges.

The same thing happens with a multiplication like =B2*C2 when C2 holds TBC or -. SUM doesn't help there, so fix the data, or test first: =IF(ISNUMBER(C2),B2*C2,"").

Cause 2: a "blank" cell that contains a space#

A cell can look empty but hold a space, or an empty text value ("") returned by a formula. To Excel that's text, so =A2+B2 returns #VALUE!.

How to check: click the cell and look at the formula bar, or type =LEN(B2) in a spare cell. A truly empty cell gives 0; a cell with one space gives 1.

How to fix:

• Select the cell and press Delete to make it truly empty.
• For many cells, use Find and Replace (Ctrl+H, or ⌃H on a Mac): find a single space, replace with nothing. Check first that no real text needs its spaces.
• Use SUM instead of +, since SUM ignores text.

If a formula returns "" on purpose to look blank, any later math on that cell will fail. Return 0 instead, or make the later formula skip text.

Cause 3: dates or numbers stored as text#

Dates and numbers imported from other systems often arrive as text. Text numbers can still work in + formulas, but text that isn't in a format Excel recognises, like a date written 31.12.2025 on a computer that expects month/day/year, gives #VALUE! as soon as you subtract it from another date.

Signs of text values:

• They sit on the left of the cell, while real numbers and dates sit on the right.
• A small green triangle appears in the corner.
• =ISTEXT(B2) returns TRUE.

To convert them, use the TEXT and VALUE lesson techniques: =VALUE(B2) turns text digits into a number, and =DATE(year, month, day) with LEFT, MID and RIGHT rebuilds a date from its pieces. Text numbers cause a quieter problem too: SUM skips them without any error, so your total is simply wrong. Our guide on why your Excel total is wrong covers that case.

Cause 4: a function gets the wrong kind of argument#

Some functions return #VALUE! when an argument doesn't make sense:

For FIND, the fix is often to handle the "not found" case on purpose: =IFERROR(FIND("@",A2),0). For formatting numbers and dates inside text, use the TEXT function rather than joining raw values; our guide to TEXT function formats shows the format codes.

How to find what causes #VALUE!#

When a long formula fails, work through it instead of guessing:

1. Click the error cell and the small warning icon next to it. Choose Show Calculation Steps to open Evaluate Formula.
2. Click Evaluate repeatedly. Excel works out the formula one piece at a time and shows which part turns into #VALUE!.
3. Check the referenced cells with =ISNUMBER(cell). Any FALSE in a math formula is a suspect.
4. Look upstream. If a cell your formula uses already shows #VALUE!, the error is passed along. Fix the first one in the chain.

You can also open Evaluate Formula from Formulas > Evaluate Formula (Windows).

Should you hide #VALUE! with IFERROR?#

Only when the error is expected and you know why it happens. IFERROR replaces any error with a value you choose:

=IFERROR(B2*C2,"")

That's fine for rows where data isn't in yet. But IFERROR also hides real mistakes, so a typo in your data can quietly turn into a blank. Fix the cause first, and use IFERROR for the cases that are left. Errors in a total often come in groups, so it's worth knowing the #REF! error and the #DIV/0! error too.

Practise it#

Fix the broken total in the exercise on this page. Then work through the Debugging SUM lesson to find text values and other hidden problems in a live sheet, and the TEXT and VALUE lesson to convert text into real numbers.