#VALUE! means a formula got the wrong kind of value, almost always text where it needs a number. =B2+C2+D2 returns #VALUE! when one of the cells holds n/a, while =SUM(B2:D2) skips the text and adds the numbers.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Jan | Feb | Mar | Total with + | Total with SUM |
| 2 | Ana | 120 | 95 | 110 | 325 | 325 |
| 3 | Ben | 80 | n/a | 105 | #VALUE! | 185 |
| 4 | Cy | 140 | 130 | 125 | 395 | 395 |
| 5 | Dee | 90 | 100 | 85 | 275 | 275 |
#VALUE! An argument has the wrong type, such as text where a number belongs.E3 shows #VALUE! because C3 holds the text n/a. F3 shows 185: SUM adds 80 and 105 and ignores the text. Type a number into C3 and both totals agree. Click E3 to see the explanation under the grid.
The +, -, *, / and ^ operators try to turn every operand into a number. They succeed with text that looks like a number ("5") or a date Excel can read, and fail with anything else. Functions such as SUM, AVERAGE, MIN, MAX and PRODUCT skip text in a range instead.
A cell that looks empty holds a space
The hardest #VALUE! to see: the cell looks blank, but someone typed a space into it, or it was imported from another system with one. A truly empty cell counts as 0 in arithmetic. A space is text.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Price | Discount | Net price | Characters in C |
| 2 | Desk | 240 | 20 | 220 | 2 |
| 3 | Chair | 85 | #VALUE! | 1 | |
| 4 | Lamp | 40 | 40 | 0 |
#VALUE! An argument has the wrong type, such as text where a number belongs.C3 and C4 both look empty, but D3 shows #VALUE! and D4 shows 40. The LEN column gives it away: C3 has one character, a space. Click C3, delete it, and D3 shows 85.
To clean a whole column in Excel, select it, press Ctrl+H (Control+H on a Mac too), type one space in Find what, leave Replace with empty and click Replace All. That removes spaces inside text too, so do it only on number columns. For text, use TRIM.
#VALUE! with dates stored as text
Excel reads a date typed as text when it matches your regional date format, so "2026-03-15"+30 works everywhere. A date like 15.03.2026 is not a date to an Excel set to US English: it is text, and adding days to it gives #VALUE!.
| A | B | C | |
|---|---|---|---|
| 1 | Delivered | Due (+30 days) | Fixed |
| 2 | 2026-03-15 | 2026-04-14 | |
| 3 | 15.03.2026 | #VALUE! | 2026-04-14 |
#VALUE! An argument has the wrong type, such as text where a number belongs.B2 shows 2026-04-14: the ISO text was read as a date. B3 shows #VALUE!. C3 takes the year, month and day out of the text with RIGHT, MID and LEFT, builds a real date with DATE, and adds 30 days: 2026-04-14 again.
For a whole column, Data > Text to Columns is quicker: click Next twice, choose Date and DMY in step 3, and click Finish. Text dates usually come from CSV files and copy and paste from web pages. More on building dates is on the DATE function page.
A function gets an argument it cannot use
#VALUE! also appears when a function receives an argument of the right type but an impossible value, or a value of the wrong type.
| A | B | C | |
|---|---|---|---|
| 1 | Code | Result | What is wrong |
| 2 | AB-1042 | #VALUE! | negative length |
| 3 | AB-1042 | #VALUE! | start position 0 |
| 4 | AB-1042 | #VALUE! | column number 0 |
| 5 | 5 | #VALUE! | text typed as an argument |
#VALUE! An argument has the wrong type, such as text where a number belongs.Each formula breaks one rule: LEFT cannot take a negative number of characters, MID starts counting at 1, VLOOKUP's column number starts at 1, and AVERAGE skips text in a range but not text typed directly into the formula. Change -1 to 2 in B2 and it returns AB.
Two more causes, both tied to the Excel version or to other files:
- An array formula in an older Excel. In Excel 2019 and earlier, a formula that works on whole ranges, such as
=SUM(IF(A2:A10="North",B2:B10)), must be confirmed with Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac). With a plain Enter it can return#VALUE!. Excel 2021 and Microsoft 365 do not need this. - SUMIF or COUNTIF pointing at a closed workbook. These functions only read other workbooks while they are open. Open the source file, or switch to SUMPRODUCT, which works on closed files.
Find the cell that causes #VALUE!
A long formula with several cells can fail on any of them. In Excel:
- Select the formula cell and choose Formulas > Evaluate Formula. Click Evaluate repeatedly: Excel calculates the formula one step at a time and shows where
#VALUE!first appears. - Or choose Formulas > Error Checking > Trace Error. Arrows point at the cells involved.
- Check suspicious cells with
=ISTEXT(C3)or=LEN(C3). A number that is aligned to the left of its cell is usually text; see text to number for how to convert it.
Fix the formula instead of hiding the error
=IFERROR(B2+C2+D2,0) turns #VALUE! into 0, but for Ben that 0 is wrong: he sold 185 in the months that have data. Write a total that skips text instead.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Jan | Feb | Mar | Total |
| 2 | Ana | 120 | 95 | 110 | |
| 3 | Ben | 80 | n/a | 105 | 185 |
| 4 | Cy | 140 | 130 | 125 | 395 |
Your turn: The n/a in C3 breaks additions with +. Write a formula in E2 that adds Ana's three months and still works if one of them holds text.
Check also tests your formula on copies of the sheet where the n/a sits in Ana's row, so a formula with + fails there. When a text cell should count as zero in a calculation that is not a sum, wrap it: =N(C3) returns 0 for text and the number for a number, and =IFERROR(C3*1,0) also turns text that looks like a number, such as '5, into 5.
Frequently Asked Questions
What does #VALUE! mean in Excel?
A formula received a value of the wrong type: usually text where a number belongs, as in =B2+C2 when C2 holds n/a or a space. It also appears when a function gets an argument it cannot use, such as =LEFT(A2,-1).
Why does adding cells give #VALUE! but SUM works?
The + operator tries to turn every operand into a number and fails on text, so =B2+C2+D2 returns #VALUE! when one cell holds text. =SUM(B2:D2) skips text in a range and adds the numbers that are there.
How do I fix #VALUE! with dates in Excel?
The date is text that Excel cannot read as a date in your regional settings, for example 15.03.2026 in an Excel set to US dates. Rebuild it with =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)), or convert the column with Data > Text to Columns and pick the DMY date format in step 3.
How do I find which cell causes #VALUE!?
Select the formula and use Formulas > Evaluate Formula to step through it, or choose Formulas > Error Checking > Trace Error to draw arrows to the cells involved. A cell that looks empty but returns 1 with =LEN(C2) holds a space.
How do I hide #VALUE! in Excel?
=IFERROR(B2+C2,0) shows 0 instead of any error. It is better to fix the cause or use a formula that skips text, such as =SUM(B2:C2), because IFERROR also hides mistakes you would want to see.
