Take the 2-minute tour ×

In excel I have a cell with formula ("=......"). On the cell, I can clearly see the value of the formula, but the formula bar shows "=#VALUE!". How is that possible?

share|improve this question

migrated from stackoverflow.com Sep 14 '09 at 17:10

This question came from our site for professional and enthusiast programmers.

6 Answers 6

highlight the cell and hit F2 then edit it in place.

Additional info:
I also want to be clear that your formula isn't


but instead is


For example,


is a good formula.


is not a good formula.

share|improve this answer

It means that there is something wrong with the formula in the cell. Perhaps there is a cell that you are trying to reference that is empty or there has the wrong datatype.

share|improve this answer
But if i click on that cell and hit "copy" and paste it on a notepad, the "result" is correct (my formula is just a string concatenation of various substrings" and other ranges) –  Saobi Sep 14 '09 at 16:55
can you provide an example that will trigger the exception case? –  akf Sep 14 '09 at 17:03
Even if my formula is wrong, what if i want to edit my formula? I can't even do that now, since the formula bar shows "=#VALUE!", and my formula is not "=VALUE!" –  Saobi Sep 14 '09 at 17:05
It's rather hard to tell if you don't provide an example here. –  user3463 Sep 14 '09 at 17:53

Just double click on the cell and Press Shift+Ctrl+Enter.You will be able to see the values properly.

share|improve this answer

It might be something that only looks correct such as :

A1+A2+A3 - error
A5: =SUM(A1+A2+A3) - error

A6: =SUM(A1,A2,A3) - correct
A7: =SUM(A1:A3) - correct

share|improve this answer

Possible that your formula requires a specific addon that is not installed - For example using yearfrac() in a system which doesn't have Analysis ToolPak (ATP) installed will cause throw this error.

share|improve this answer

methinks... the cell size isnt that big enough to fit the value.. try increasing the width

share|improve this answer

Your Answer

