#VALUE! error in Excel: what it means and how to fix it

#VALUE! means a formula was handed a value it can’t work with. Most often that’s text in a cell the formula does maths on: a note such as “n/a”, a stray space, or a number that arrived as text. The rest come from functions that return #VALUE! on purpose, such as SEARCH when it finds nothing, or SUMPRODUCT when its ranges don’t line up. Find the cell that’s the wrong kind, and the fix is usually one step.

1. Text in a cell you do maths on

Both rows add three months with the + sign. Rent’s total works; Phone’s February holds the text “n/a”, and + can’t add text, so that total fails:

E3fx=B3+C3+D3

Row	A	B	C	D	E
1	Bill	Jan	Feb	Mar	Total
2	Rent	900	900	900	2700
3	Phone	35	n/a	35	#VALUE!

SUM adds the numbers in a range and leaves text out, so it gives a total where + gives an error:

That total is right only if “n/a” really means nothing was spent. If it stands for a figure you don’t have yet, type the number when you get it: SUM can’t tell a missing figure from a zero.

E2fx=SUM(B2:D2)

Row	A	B	C	D	E
1	Bill	Jan	Feb	Mar	Total
2	Phone	35	35	35	70

2. A cell that looks empty but holds a space

An empty cell counts as zero in maths. A cell holding a single space is text, and looks exactly the same:

D2fx=B2*C2

Row	A	B	C	D
1	Item	Price	Qty	Total
2	Pens	2.5		#VALUE!

=LEN(C2) gives it away: it returns 1 for the space, and 0 for a truly empty cell. Select the cell and press Delete. To clear a whole column of them, select it and use Find and Replace: find a single space, replace with nothing, and tick Match entire cell contents so the spaces inside “New York” stay. =ISTEXT(C2) also returns TRUE for any cell holding text.

3. SEARCH or FIND doesn’t find the text

SEARCH and FIND return the position of one piece of text inside another. When it isn’t there, they return #VALUE!:

C3fx=SEARCH("refund",B3)

Row	A	B	C
1	Ticket	Note	Refund?
2	T-101	Please refund my order
3	T-102	Where is my parcel?	#VALUE!

To flag rows instead, wrap the search in ISNUMBER. A found position is a number, so the result is TRUE or FALSE, never an error:

FIND works the same way but matches upper and lower case exactly; SEARCH ignores case.

4. Ranges of different sizes

SUMPRODUCT multiplies its ranges row by row, so they must be the same size. Here the price range stops one row short:

D2fx=SUMPRODUCT(B2:B4,C2:C3)

Row	A	B	C	D
1	Region	Units	Price	Revenue
2	East	10	4	#VALUE!
3	West	5	6
4	East	8	4

Make both ranges cover the same rows:

SUMIFS, COUNTIFS and AVERAGEIFS have the same rule: every range in them must have the same number of rows and columns.

5. A date stored as text

A date written in a form your computer’s date settings don’t read, such as 15/03/2026 on a computer set to US dates, stays as text. You can spot it because it sits on the left of the cell, where real dates sit on the right (unless someone changed the alignment). Subtracting it from another date gives #VALUE!. Rebuild it with DATE, or retype it in the date format your computer uses.

Practise it: a total that skips the text

Your task: In D2, total the three monthly costs in A2:C2. February's cost is the text "n/a", so the total must leave it out instead of showing #VALUE!.

Graded live

D2fx

Row	A	B	C	D
1	Jan	Feb	Mar	Total
2	$120	n/a	$95

Write the formula in the bar above, then press Check or Enter.

Hint

Use a function instead of the + sign: SUM over A2:C2 adds the numbers and skips the text.

Solution

=SUM(A2:C2) Any formula that returns the same result passes.

Check

Find the cell, then decide

When a long formula shows #VALUE!, select it and use Formulas › Evaluate Formula in desktop Excel: it works the formula out one step at a time, and the step that turns into #VALUE! names the culprit. Hide the error with IFERROR only once you know the cause is harmless, because IFERROR hides every error, including the ones that mean the formula is wrong.