formulastreak

#VALUE! error: what it means and how to fix it

#VALUE!

Something in the formula is the wrong type of value, usually text where a number should be.

Microsoft's own summary: there's something wrong with the way the formula is typed, or with the cells it uses. Most often it's maths on text: +, -, * and / need numbers, and a cell holding a word (or a number stored as text with odd characters) breaks them.

Common causes, ranked

  1. 1

    Maths on a cell that holds text

    =F2*D2 multiplies by a product name instead of its price. + and * can't skip text.

    Fix: Point at the right cell. To add a range that might contain text, use SUM, which skips text, instead of +.

    Shows the error

    =A2+B2+C2

    Fixed

    =SUM(A2:C2)
  2. 2

    Hidden spaces or characters

    A cell that looks empty may hold a space, and a number copied from the web may carry invisible characters. Both count as text.

    Fix: Clear the cell properly, or clean the text with TRIM and CLEAN, then use the cleaned value.

    Shows the error

    =B2*C2

    Fixed

    =VALUE(TRIM(B2))*C2
  3. 3

    Dates stored as text

    Subtracting two dates that are really text (for example typed with an apostrophe, or imported) gives #VALUE!.

    Fix: Turn the text into real dates (re-enter them, or use DATEVALUE), then subtract.

  4. 4

    A function given the wrong kind of argument

    For example text where a function needs a number, or a range where it needs one value.

    Fix: Click the cell and check each argument. In Excel, Formulas › Evaluate Formula shows which part fails.

See it live

Edit the formula in I2 and fix the error yourself.

The formula multiplies the quantity by the product name, so every row shows #VALUE! · edit I2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI2 = #VALUE!
ABCDEFGHI
1OrderDateCustomerProductCategoryQtyPriceStatusTotal
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped#VALUE!
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped#VALUE!
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped#VALUE!
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending#VALUE!
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped#VALUE!
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned#VALUE!
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped#VALUE!
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending#VALUE!
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped#VALUE!
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped#VALUE!
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped#VALUE!
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending#VALUE!

How to hide it safely

You can catch it with IFERROR, but for #VALUE! it's almost always better to fix the data or the formula:

=IFERROR(B2*C2,"Check the data")

Careful: Microsoft warns that IFERROR hides all errors, not just #VALUE!. A message is fine while you clean the data; a silent 0 or "" hides a real problem.

Excel vs Google Sheets

#VALUE! is an error value in both Excel and Google Sheets. In Excel, IFERROR needs both arguments; in Google Sheets the second is optional and leaves the cell blank when you skip it.

Sources

Practise this in 5 minutes

Fixing #VALUE! is taught in Lesson 32: Numbers stored as text (Unit 8, Cleaning text at scale), on a live sheet with instant feedback. Pro

Open “Numbers stored as text”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.