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

  1. 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.

    ABCD
    1PriceQty
    212 USD3#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.

    ABCD
    1PriceQty
    212 USD336

    In D2: =VALUE(SUBSTITUTE(A2, " USD", ""))*B2 → 36

  2. One 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.

    ABCDE
    1JanFebMar
    2120n/a150#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.

    ABCDE
    1JanFebMar
    2120n/a150270

    In E2: =SUM(A2:C2) → 270

  3. A 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.

    ABCD
    1QtyBonus
    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.

    ABCD
    1QtyBonus
    255

    In D2: =A2+B2 → 5

  4. A 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.

    ABCD
    1StartEnd
    22026-03-01March 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.

    ABCD
    1StartEnd
    22026-03-012026-03-1514

    In D2: =B2-A2 → 14

  5. Ranges 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.

    ABCD
    1RegionSales
    2North100#VALUE!
    3South80
    4North60

    In D2: =SUMIFS(B2:B4, A2:A3, "North") → #VALUE!

    How to fix it. Make every range start and end on the same rows.

    ABCD
    1RegionSales
    2North100160
    3South80
    4North60

    In D2: =SUMIFS(B2:B4, A2:A4, "North") → 160

  6. FIND 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.

    ABC
    1Name
    2Ana 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.

    ABC
    1Name
    2Ana SilvaAna 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

Other errors