Start for free

All Excel functions

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

#VALUE! means a formula is getting the wrong type of data, usually text where Excel expects a number.

Try the first exercises for freeIn the lesson Understanding error messages

What does #VALUE! mean?

#VALUE! means that a formula gets something it can't calculate with. Usually there's text where Excel expects a number, such as the word ill in a column of hours, or a number stored as text in the cell.

Excel can't carry out the calculation, so it shows the error instead of a wrong result. The error also passes on: a formula that refers to a cell with #VALUE! gets the same error.

When do you get #VALUE!?

  • You add up cells with plus signs and one cell contains text: =B2+B3+B4 with ill in B3.
  • You calculate with a number stored as text, with a unit added, such as 12 kg.
  • You calculate with a date stored as text in the cell.
  • SEARCH doesn't find the text you're looking for.

How to fix #VALUE!, step by step

This is how to fix the error:

  1. Click the cell with #VALUE! and look in the formula bar to see which cells the formula uses.
  2. Click the yellow exclamation mark next to the cell and choose Show Calculation Steps: Excel shows which part goes wrong.
  3. Check each cell: is there text, a space or a unit with a number? Numbers stored as text are on the left of the cell.
  4. Fix the cause: remove the text, convert numbers with VALUE, or use SUM instead of plus signs, because SUM skips text.

Examples

The error

D1 contains =B2+B3+B4. With plus signs you get #VALUE!, because ill isn't a number.

D1
A
B
C
D
E
F
G
H
1
Day
Hours
#VALUE!
2
Monday
8
3
Tuesday
ill
4
Wednesday
7
5
6
7
8
9
10
Ready

The fix with SUM

D1 contains =SUM(B2:B4). SUM skips the text and adds up the hours: 15.

D1
A
B
C
D
E
F
G
H
1
Day
Hours
15
2
Monday
8
3
Tuesday
ill
4
Wednesday
7
5
6
7
8
9
10
Ready

A number with a unit

C1 contains =A2*2. 12 kg is text. Remove kg with SUBSTITUTE, or type only the number in the cell.

C1
A
B
C
D
E
F
G
H
1
Weight
#VALUE!
2
12 kg
3
4
5
6
7
8
9
10
Ready

Where it often goes wrong

  • =A1+B1 gives #VALUE! if B1 contains text, whereas =SUM(A1:B1) skips that text.
Want to try it yourself?
Practise this in the lesson Understanding error messages. The first exercises are free.

Try the first exercises for free

Frequently asked questions

How do I hide #VALUE!?

With IFERROR, but it's better to fix the cause first.

Read next

#N/A#DIV/0!#NAME?#REF!#SPILL!