You’d see the #VALUE error popping up in your Excel sheet when there is something wrong:
- With how the formula is typed.
- Or with the cell references in your formula.
For example, here we have specified the wrong data type for the PRODUCT function:
Excel can surely multiply numbers but not text. As the list contains the text “ten”, the formula to add them up returns a #VALUE error ✖
Similarly, if you write arguments for a function that don’t make sense, you’d again see the logical error.
For example, the FIND function of Excel finds the position of a character from a text string.
The formula FIND (“p”, “paint”) gives the result 1. Because the substring “1” comes in the second position in the word “paint” 🎨
Try writing this formula as below:
=FIND (“b”, “kite”)
And Excel returns a #VALUE error. Because the word “kite” just doesn’t contain the character “b”, let alone its position therein 🪁
The exact reasons why the #VALUE error may occur depending upon the formula being used. However, the following general hacks can help you fix it.
- Double-check the format of your data. For example, if it’s a date function, make sure the arguments you enter are in the date format.
- Use Excel’s in-built functions instead of writing formulas yourself.
- Look out for any special characters in cells that may make your formula invalid.
Learn more about how to fix the #VALUE error in Excel here.