#VALUE! error in Excel
A formula got a kind of value it cannot use, most often text where it needed a number.
What #VALUE! means
#VALUE! means a formula received something it cannot work with. The most common version is plain: text where a number was expected. It also appears when a function needs two ranges of the same size and gets mismatched ones, or when a conversion function is given text it cannot read.
The error does not say which cell is the culprit. That is the main skill with #VALUE!: tracing a formula back to the one input that is the wrong kind. Click the formula, look at the colored outlines Excel draws around each referenced cell, and check each input in turn.
Two habits help. First, numbers should be right-aligned by default and text left-aligned, so a left-aligned number in a numeric column is a strong hint. Second, a cell that looks empty is not always empty, which is the cause people find last.
Causes of #VALUE! and how to fix each
1. Text in an arithmetic formula
Plus, minus, times and divide need numbers. A cell holding "12 units" or "n/a" is text, and a formula that tries to use it in arithmetic returns #VALUE!. Functions like SUM quietly skip text in a range, which is why the same data can work in SUM and fail in =B2*C2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price | |
| 2 | Pen | 12 units | 2 | #VALUE! |
| 3 | Pad | 5 | 3 |
Fix. Replace the text with a clean number (type 12, and keep the unit in the header or a number format). If the data comes from an export and there are many rows, strip the unit with SUBSTITUTE(B2," units","") and multiply the result, or use Find and Replace once on the whole column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price | |
| 2 | Pen | 12 | 2 | 24 |
| 3 | Pad | 5 | 3 |
With 12 stored as a number, the product is 24.
2. A space that looks like an empty cell
A cell that appears blank may hold a single space, usually from a paste or an import. A space is text, so =A2*2 fails with #VALUE! while a truly empty cell would be treated as zero. This one is hard to spot because there is nothing to see.
| A | B | |
|---|---|---|
| 1 | #VALUE! |
Fix. Select the suspect cell and look at the formula bar, or test it with =LEN(A1): a result of 1 means a hidden character. Clear the cell with Delete instead of the space bar, or use TRIM to clean a whole column before you calculate.
3. Ranges of different sizes
Functions that pair up two ranges, such as SUMPRODUCT, need both to have the same number of cells. A 3-cell range against a 2-cell range cannot be paired, so Excel returns #VALUE!.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2 | 10 | #VALUE! | |
| 2 | 3 | 20 | ||
| 3 | 4 |
Fix. Make both ranges the same shape. The usual cause is a range that was extended on one side only when rows were added. Check the range addresses in the formula bar, where Excel outlines each one in color.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2 | 10 | 80 | |
| 2 | 3 | 20 | ||
| 3 | 4 |
Both ranges now cover rows 1 to 2: 2 times 10 plus 3 times 20 is 80.
4. A function that cannot read the text it was given
Text-to-number and text-to-date conversions fail when the text is not in a recognizable form. VALUE("abc") has nothing to convert. DATEVALUE on a string like "next Tuesday" fails the same way, as does a date typed in a format your regional settings do not read.
| A | B | |
|---|---|---|
| 1 | 12 units | #VALUE! |
Fix. Remove whatever is not part of the number before converting, for example VALUE(SUBSTITUTE(A1," units","")), or ask for the data in a cleaner form. For dates, build the date from its parts with DATE(year, month, day) so regional formats cannot interfere.
Trapping #VALUE! in a formula
IFERROR is the usual way to keep a column tidy, with one caveat: it hides every error, including real mistakes in your own formula. Use it when you know the only likely failure is the bad input, and show a visible marker such as "Check" rather than 0, so a bad row cannot slip into a total as a silent zero.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price | |
| 2 | Pen | 12 units | 2 | Check |
| 3 | Pad | 5 | 3 |
The first row has text in the quantity column, so it shows the marker. Rows with real numbers calculate normally.
#VALUE! on Excel job tests
If a prompt gives you a column of numbers and asks you to total or multiply it, a few cells may have been typed as text. If your result is #VALUE! and a colleague's SUM works, you have text in the range and SUM is skipping it silently.
Fixing the cell is usually the better answer than wrapping the formula, unless the task explicitly asks you to handle bad rows in the formula itself.
Related guides
#VALUE! questions
- What does #VALUE! mean in Excel?
- Excel received the wrong type of value for the operation, most often text where a number was needed, or ranges that do not match in size.
- How do I find which cell causes #VALUE!?
- Click the formula cell, then use Formulas, Evaluate Formula to step through it. Also check each referenced cell with
ISNUMBER, and useLENto reveal spaces in cells that look empty. - Why does SUM work but plus gives #VALUE!?
- SUM skips text inside a range. The plus sign does not skip anything, so a text cell in
=A1+A2fails. - Can I just wrap it in IFERROR?
- You can, but the bad cell is still in your data. Fix the data when you can, and use IFERROR with a visible marker when bad rows are expected.
Practice working with errors
The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.
Try a 5-item Excel test sample