Have you ever encountered the #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors in Excel? Learn more about these errors and how to avoid them in simple terms.
Why does Excel show #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors?
The #NAME error is generated when a cell tries to reference a non-existent cell call a non-existent function. If you enter a formula that does not exist in Excel, say =getmeburger(), you will get the #NAME? error. Check if the function name has been spelled correctly and that the formula is a valid formula.
When a cell tries to reference a cell that can’t be located on the spreadsheet either due to deletion or having been overwritten by pasting by a user or a VBA code, it can result in a #REF! error. If you get the #REF Error, try checking if the cell is using a non-existent cell.
If the denominator in a fraction evaluates to 0, Excel will show the #DIV/0 error. Copy the denominator separately to another cell and check out why is causing to evaluate to 0.
An invalid value or wrong number of values being specified as argument to a function can cause a cell to show the #VALUE! error. For example entering =if(“a”,1,0) will result in a #VALUE! error. Check for arguments to the function and see if they are of the same format (text, number) that the formula expects.
Entering a non-numeric argument to a function that expects a numeric value. A function or a value that evaluates to a numeric value beyond the Excel’s capabilities to handle. Result of a function that could not find a valid value in spite multiple iterations.
A generic error code indicating all other result code not represented by any of the above error codes. Indicates missing data, non-existent function name, providing wrong values as arguments to functions etc. Most frequently indicates that the value that you are trying to find/match does not exist.
How to avoid printing errors In Excel
Select the worksheet that you would like to print. Click Page Setup on the menubar and then click the Sheet tab. Select the Print tab and in the Cell errors box, chose
How to hide display of errors in Excel
Select a cell (Let’s take cell A1 for example). Click on ‘Format’ -> ‘Conditional Formatting’ on the menubar. Choose ‘Formula Is’ and enter =ISERROR(A1) as formula in the next box. Click on format and choose white as the font color. Click ok. This will prevent errors from showing up when users go through your workbook. Apply the same format to all cells that can result in an error.
How to check errors in Excel
Simply enclose the formula that can potentially generate an error in an =ISERROR(condition) formula. Use it along with the IF function like =IF(ISERROR(condition),””,condition). This way, even if an error occurs, the cell will show nothing though it would work well under all other conditions.