Excel #VALUE! error
An argument has the wrong type: Excel expected a number and got text, or the other way round.
Why it appears
- You add with + a cell that holds text, such as "N/A" or "1 450".
- A date typed as text that Excel does not recognize.
- FIND or SEARCH could not find the text.
How to fix it
- Use SUM(range) instead of A1+A2+A3: SUM skips text.
- Find badly entered values with ISTEXT and convert them with VALUE.
- With FIND and SEARCH, wrap them in ISNUMBER or IFERROR.
Gives the error
=B2+B3+B4
Fixed
=SUM(B2:B4)
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Numbers stored as text: ISTEXT
A number pasted from another system can end up as text: it sits on the left and SUM skips it without warning. ISTEXT(cell) catches it before the total goes wrong.
In C2, show "Check" if the amount was stored as text and "OK" if it is a number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Amount | Entry | |
| 2 | F-101 | 1,250 | ||
| 3 | F-102 | 980 | ||
| 4 | F-103 | 2,300 | ||
| 5 | F-104 | 1 450 | ||
| 6 | F-105 | 760 | ||
| 7 | Total | |||
| 8 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What does #VALUE! mean in Excel?
- An argument has the wrong type: Excel expected a number and got text, or the other way round.
- How do I get rid of the #VALUE! error?
- Use SUM(range) instead of A1+A2+A3: SUM skips text. Find badly entered values with ISTEXT and convert them with VALUE. With FIND and SEARCH, wrap them in ISNUMBER or IFERROR.
- What is #VALUE! called in Spanish Excel?
- Spanish Excel shows it as #¡VALOR!.