In this lesson you can learn about errors which happen in Excel.
Errors in Excel happen very often. Sometimes they are serious, often only minor errors in formulas. Unfortunately, they can often disgust you to work and keep it for long hours.
Error messages in Excel
There are 7 different error messages 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 in 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 the 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 the value in cell A1 from error to 0. Use it when you make some calculation e.g. 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.
Using custom number formatting
You can use custom number formatting to display a specific value, such as a dash or zero, instead of an error. To do this, right-click the cell with the error, select "Format Cells", go to the "Number" tab, and select "Custom". Enter the custom format you want to use, such as "-".
Using custom number formatting
You can use conditional formatting to change the background color or font color of cells that contain errors. To do this, select the cells you want to format, go to the "Home" tab, select "Conditional Formatting", and choose "Highlight Cells Rules", then "Errors". Choose the formatting you want to apply to the cells with errors.
Hiding the error with a macro
If you have a lot of cells with errors, you can use a macro to automatically hide the errors. To do this, record a macro that selects the cells with errors, clears their contents, and replaces them with a specific value, such as 0.
These are some of the methods you can use to hide errors in Excel. Choose the method that works best for your data and use case.
Further reading: Counting the number of errors in the sheet How to Remove N/A! Errors in VLOOKUP Formula? Chart that Ignores N/A! Errors and Empty Values