#VALUE! error: what it means and how to fix it
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
Maths on a cell that holds text
=F2*D2multiplies 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+C2Fixed
=SUM(A2:C2) - 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
TRIMandCLEAN, then use the cleaned value.Shows the error
=B2*C2Fixed
=VALUE(TRIM(B2))*C2 - 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
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.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Total |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | #VALUE! |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | #VALUE! |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | #VALUE! |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | #VALUE! |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | #VALUE! |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | #VALUE! |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | #VALUE! |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | #VALUE! |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | #VALUE! |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | #VALUE! |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | #VALUE! |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | #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”Need a quick reference? The formula cheat sheet is free to read and print.