How to fix the #VALUE! error in Excel
#VALUE! means Excel couldn’t use one of the values in your formula the way you asked. The classic case is doing math on something that’s text.
#VALUE!
Why it happens
Math on text
=A2+B2 fails if B2 contains “5 units” or a number pasted with a currency symbol Excel doesn’t recognise.
Hidden spaces
A cell that looks empty but holds a space is text, so =A2*B2 returns #VALUE!.
Ranges of different sizes
Functions like SUMPRODUCT, XLOOKUP and FILTER need ranges with the same number of rows.
Dates stored as text
Imported dates like “2026-11-02” may be text. Date math on them returns #VALUE!. Convert with DATEVALUE.
How to fix it
SUM ignores text, so it’s a safer total than +
=SUM(D2:D9) → 36
Make every range the same size
=SUMPRODUCT(D2:D9,E2:E9) → 657
Convert text to numbers
=VALUE("42")
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Tip: Use ISNUMBER(A2) to test whether a cell is really a number. FALSE on something that looks numeric means it’s stored as text.
Other Excel errors
- #N/A“Not available”: a lookup couldn’t find what you asked for.
- #REF!The formula points at a cell that no longer exists.
- #DIV/0!A formula tried to divide by zero or by an empty cell.
- #NAME?Excel doesn’t recognise a word in the formula.
- #SPILL!A formula that returns several results has no room to spill into.
- #NUM!The math is impossible or a number is out of range.