Errors in Excel
- #DIV/0!
- #NAME?
- #N/A
- #NULL!
- #NUM!
- #REF!
- #VALUE!
Let’s see what Excel is telling by these messages and how to correct them.
#DIV/0!
Dividing by zero is not allowed. This message tells that you have entered a formula tries to divide a value by zero. The second possibility is that you used the formula refers to an empty cell. Most message #DIV/0! occurs when you create a formula to the data, which have not yet typed.
#NAME?
This error is the result of typos. Frequently occurs when poorly typed a function name or cell / range, referred to by the given function. Correction spelling should fix the problem – otherwise it is something more serious.
#N/A
#N/A is unavailable. The error occurs most often when the VLOOKUP or HLOOKUP can not find a result. The result is unavailable, probably because of wrong choose the search value function.
#NULL!
This is the easiest to fix the error. Appeared, probably not because you typed a semicolon in our formula.
#NUM!
Excel tells us the message that the problem is the result of our formula. The problem may be the argument of the function or function result. It is possible that you used the function requires an argument, which is not typed or that argument is invalid (for example, you calculate the sum of the numbers and text). If the result of our function is too big or too small, it also appears in a #NUM!
#REF!
The error tells us that the address is invalid. Most likely, you have removed a cell that is referenced in the formula.
#VALUE!
The error indicates whistleblowing function argument. In this case, check the two cases of mistakes when creating formulas. The first mistake you make when trying to create a function argument, for example, take the square root of a negative number, or by adding the number and text. The second type of error you make when you try instead of an array function to use a regular function.
Hiding errors
I wish you had the least errors working with Excel. But sometimes you can’t avoid errors in your spreadsheet. Then you can cheat Excel. There are ways to hide errors. You can change error into something different value in cell.
For example you can use that formula:
=IF(ISERROR(A1),””)
This formula changes value in cell A1 from error to nothing. It could be useful when you just want to hide errors in your spreadsheet.
=IF(ISERROR(A1),0)
That formula changes value in cell A1 from error to 0. Use it when you make some calculation eg. commission for clerks.
ISERROR function let you change value with error in your error. Thanks to it your spreadsheet becomes more professional so it’s worth using.
