Cellwise

Excel guide · 3 min read

#VALUE! error in Excel: causes and fixes

#VALUE! means a formula got the wrong kind of data, most often text where it expected a number, such as "4 units" or "n/a" in a math formula. Fix it by cleaning the text out of the cell, using SUM (which skips text) instead of +, or extracting the number with a text function.

Sample data

Every formula on this page was calculated live against this sheet.

ABC
1ItemQtyPrice
2Paper A4104.25
3Toner4 units62
4Staples5n/a
5Folders121.8

Cause 1: text inside a number cell

Someone typed "4 units" in the quantity column. To Excel that is text, and you cannot multiply text, so the line total fails.

fx=B3*C3

Result: #VALUE!

B3 is the text "4 units", so the multiplication returns #VALUE!.

Fix: pull the number out of the text

The best fix is to type plain 4 in the cell and put "units" in the header or a number format. If you cannot change the source data, extract the number. TEXTBEFORE (Excel 365) takes everything before the first space, and Excel converts "4" to 4 when you multiply it.

In older versions, =VALUE(LEFT(B3,FIND(" ",B3)-1))*C3 does the same job.

fx=TEXTBEFORE(B3," ")*C3

Result: 248

TEXTBEFORE returns "4", which Excel multiplies by 62 to give 248.

Cause 2: adding cells with + when one holds text

The price for staples says "n/a". The plus operator tries to add every cell and fails on the text.

fx=C2+C3+C4+C5

Result: #VALUE!

C4 is "n/a", so the + chain returns #VALUE!.

Fix: use SUM, which ignores text

SUM skips text and blank cells in a range, so it adds the real prices and moves on. Just be aware the staples price is missing from the total; consider a blank or 0 instead of "n/a".

fx=SUM(C2:C5)

Result: 68.05

SUM adds 4.25, 62 and 1.80 and ignores "n/a": the result is 68.05.

Cause 3: SEARCH or FIND cannot find the text

SEARCH and FIND return #VALUE! when the text is not there. That is by design, so do not treat it as a bug. Wrap the check in ISNUMBER to get TRUE or FALSE, then use IF if you need a label.

fx=IF(ISNUMBER(SEARCH("ink",A3)),"Ink","Other")

Result: Other

"Toner" does not contain "ink", so SEARCH errors, ISNUMBER gives FALSE, and the result is Other.

Other causes worth checking

Ranges of different sizes in COUNTIFS, AVERAGEIFS and similar functions return #VALUE!, so check that every range covers the same rows. Dates typed as text (for example "31/12/2026" on a US-format PC) cannot be used in date math. Spaces that look empty can also break math: press F2 on a "blank" cell to see if anything is in it.

  1. Select the cell showing #VALUE!.
  2. Go to Formulas > Evaluate Formula and click Evaluate repeatedly to see which part fails.
  3. Fix the cell or argument that turns into #VALUE! first.

Common mistakes

Typing units into number cells

Keep the cell a pure number and show units with a custom number format such as 0 "units" (Ctrl+1 > Number > Custom).

Assuming a blank-looking cell is empty

A single space is text. =LEN(B5) shows 1 if there is a space. Clear it with Delete.

Hiding #VALUE! with IFERROR

You get a 0 total that is quietly wrong. Fix the input data so the number is actually counted.

FAQ

Why does my formula say #VALUE! when the numbers look fine?

They are probably stored as text, or they contain a hidden space. Check with =ISNUMBER(B2); if it says FALSE, convert the column with Data > Text to Columns > Finish.

Does SUM ever return #VALUE!?

Not because of text in a range, which SUM ignores. It will pass on a #VALUE! that is already in one of the cells, though, so fix that source cell.

How do I find all #VALUE! cells on a sheet?

Use Home > Find & Select > Go To Special, choose Formulas, and tick only Errors. Excel selects every error cell so you can review them.

Functions used

Keep learning

Updated 2026-10-31