Excel practice › Excel errors › #VALUE! error
#VALUE! error in Excel: what it means and how to fix it
#VALUE! means a formula was given the wrong kind of value, and the usual case is text where it needs a number. One cell in the calculation holds a word, a note like n/a, or a date that Excel didn't recognize, and +, -, * and / can't work with it.
Find the cell that isn't a number and correct it. If the text belongs there, add with a function and not with plus signs: =SUM(B3:D3) skips text, where =B3+C3+D3 stops on it.
The causes, in the order worth checking
- A cell in the calculation holds text: a word, a note, a number typed with its unit.
- A cell that looks empty holds a space.
- A date that is really text.
- A plus sign used to join text.
- Ranges of different sizes in a function that needs them equal, like SUMIFS.
1. A cell in the calculation holds text
Marketing's February figure wasn't ready, and someone typed n/a in its place. Every formula that does arithmetic on that cell now shows #VALUE!:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Dept | Jan | Feb | Mar | Q1 total |
| 2 | Sales | 4200 | 3900 | 4650 | 12750 |
| 3 | Marketing | 2800 | n/a | 3100 | #VALUE! |
| 4 | IT | 1500 | 1500 | 1750 | 4750 |
| 5 | Facilities | 900 | 950 | 1100 | 2950 |
| 6 | HR | 650 | 700 | 720 | 2070 |
+ can't add text. SUM ignores text cells in a range: =SUM(B3:D3) returns 5900, the total of the two months that have a number.SUM gets you a number, but decide whether it's the number you want: 5900 is two months, not a quarter. The same goes for a figure typed with its unit, like 12 boxes: to Excel that's text, and the fix is to keep the number in one cell and the unit in the header.
To find the cell in a long row in Excel, test the suspects with =ISTEXT(C3): TRUE means that cell is text, whatever it looks like. (ISTEXT isn't among the functions the sheets on this page know.)
2. A cell that looks empty holds a space
A cell someone "cleared" by pressing the space bar isn't empty. It holds a space, which is text, and arithmetic on it gives #VALUE!. A really empty cell counts as 0 and causes no error.
Select the cell and press Delete. To clean a whole column, Microsoft's method is Find & Replace: put a single space in "Find what", leave "Replace with" empty, and choose Replace All. Do that on the selected column only, or it removes the spaces inside your text too.
3. A date that is really text
Excel stores a date as a number, which is why subtracting two dates gives days. A date it didn't recognize when it was typed or imported stays text, and the subtraction fails. Here the shipping date of order 5003 was typed day first:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Ordered | Shipped | Days |
| 2 | 5001 | 3/2/2026 | 3/5/2026 | 3 |
| 3 | 5002 | 3/3/2026 | 3/9/2026 | 6 |
| 4 | 5003 | 3/20/2026 | 25/3/2026 | #VALUE! |
| 5 | 5004 | 3/6/2026 | 3/10/2026 | 4 |
=C2-B2 gives 3.4. A plus sign used to join text
+ adds numbers and nothing else. To put a label in front of a number, join them with &:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Dept | Jan | Feb | Mar | Q1 total | Label | |
| 2 | Sales | 4200 | 3900 | 4650 | 12750 | #VALUE! | |
| 3 | Marketing | 2800 | n/a | 3100 | 5900 | ||
| 4 | IT | 1500 | 1500 | 1750 | 4750 | ||
| 5 | Facilities | 900 | 950 | 1100 | 2950 | ||
| 6 | HR | 650 | 700 | 720 | 2070 |
+ with &: ="Q1 total: "&E2 returns Q1 total: 12750.5. Ranges of different sizes
SUMIFS goes down its ranges side by side, row by row, so they must have the same number of rows. Here the amounts run to row 8 and the departments stop at row 7:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Dept | Category | Amount | Dept | Total | |
| 2 | Sales | Travel | 1240 | IT | #VALUE! | |
| 3 | IT | Software | 890 | |||
| 4 | Sales | Meals | 310 | |||
| 5 | Marketing | Ads | 2200 | |||
| 6 | IT | Hardware | 1650 | |||
| 7 | Sales | Travel | 780 | |||
| 8 | IT | Software | 420 |
=SUMIFS(C2:C8,A2:A8,E2) returns 2960.Practice: 3 formulas to fix
Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data.
=B3+C3+D3 shows #VALUE! because C3 holds the text n/a. In E3, write a formula for Marketing's total that works whether C3 holds text or a number.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Dept | Jan | Feb | Mar | Q1 total |
| 2 | Sales | 4200 | 3900 | 4650 | 12750 |
| 3 | Marketing | 2800 | n/a | 3100 | |
| 4 | IT | 1500 | 1500 | 1750 | 4750 |
| 5 | Facilities | 900 | 950 | 1100 | 2950 |
| 6 | HR | 650 | 700 | 720 | 2070 |
Show the answer
=SUM(B3:D3) gives 5900.
SUM adds the numbers in B3:D3 and ignores cells that hold text. When C3 gets its figure, the same formula includes it.
=SUMIFS(C2:C8,A2:A7,E2) should total the amounts for the department in E2. It shows #VALUE!. In F2, write the formula that works.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Dept | Category | Amount | Dept | Total | |
| 2 | Sales | Travel | 1240 | IT | ||
| 3 | IT | Software | 890 | |||
| 4 | Sales | Meals | 310 | |||
| 5 | Marketing | Ads | 2200 | |||
| 6 | IT | Hardware | 1650 | |||
| 7 | Sales | Travel | 780 | |||
| 8 | IT | Software | 420 |
Show the answer
=SUMIFS(C2:C8,A2:A8,E2) gives 2960.
Both ranges now cover rows 2 to 8, so SUMIFS can pair each department with its amount.
="Q1 total: "+E2 shows #VALUE!. In G2, write the formula that returns the label followed by the number in E2, like Q1 total: 12750.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Dept | Jan | Feb | Mar | Q1 total | Label | |
| 2 | Sales | 4200 | 3900 | 4650 | 12750 | ||
| 3 | Marketing | 2800 | n/a | 3100 | 5900 | ||
| 4 | IT | 1500 | 1500 | 1750 | 4750 | ||
| 5 | Facilities | 900 | 950 | 1100 | 2950 | ||
| 6 | HR | 650 | 700 | 720 | 2070 |
Show the answer
="Q1 total: "&E2 gives Q1 total: 12750.
& joins values as text, so a label and a number can sit in one cell. CONCAT does the same: =CONCAT("Q1 total: ",E2).
Questions people ask
What does #VALUE! mean in Excel?
The formula got a value of the wrong type. In practice: text in a place where a number is needed. Excel shows it when you add, subtract, multiply or divide a cell that holds a word, a space, or a date it stored as text.
Why do I get #VALUE! when the cell looks empty?
The cell holds a space, or another character you can't see. Select it and press Delete, then check the formula again. A really empty cell counts as zero and doesn't cause the error.
Why does SUM work when adding with + gives #VALUE!?
SUM ignores text cells in the range it's given. The + operator doesn't ignore anything: one text cell and the whole formula is #VALUE!. =SUM(B3:D3) works where =B3+C3+D3 fails.
How do I find the cell that causes #VALUE!?
Look at each cell the formula uses. Text sits on the left of its cell and numbers on the right, unless the alignment was changed. For a cell you can't judge by eye, =ISTEXT(C3) returns TRUE when it holds text.
Can I hide #VALUE! with IFERROR?
You can: =IFERROR(B3+C3+D3,0) shows 0 where the error was. But 0 is now a wrong total that looks right. Fix the cell or the formula first, and keep IFERROR for cases where you know why the error happens and what should show.