How to hide #DIV,#N/A,#NAME,#REF,#VALUE errors in excel


The resource is about excel errors, in excel there are many types of errors like #REF,#DIV,#VALUE,#NAME,#N/A, we can prevent such errors in formulas using ISERROR function in excel2003 and using IFERROR function in excel 2007, now if there is error(s) in your workbook than not to worry about it, you workbook looks errors free, so relax yourself.

Info: excel formula start with = sign
Info: () is function symbol, so if i write anything before () is called function, for example: square(), i can call it "square function"
When you are using LOOKUP(VLOOKUP/HLOOKUP) functions in excel than if the value you are looking for is not available that time excel shows #N/A error. Also i think you are come across many errors (#REF,#DIV,#VALUE,#NAME) in excel, in such cases, you can put you formulas or cell address in excel built-in function ( built-in functions means when you install ms-excel these functions are already available, ready to use, built-in function are also called library function) IFERROR(), to prevent excel errors.
Syntax of IFERROR() function is as follows

=IFERROR(Value,Value if error)


in above syntax first argument "Value" means the cells address or formula you want to prevent from error, and second argument "Value if error" means if any error occur than what value you want to display instead of error value.
For example: your formula is 100/B2, here B2 is cell address and 100 is divided by cell B2 value, when excel evaluates this formula if cell B2 value is 0 (ZERO) than it encounter #DIV (divide by zero) error, to prevent excel to show this error you can use IFERROR() function like this, =IFERROR(100/B2,0), now when excel evaluates 100/B2 that time it will not show #DIV error if cell B2 value is 0 (ZERO), instead of #DIV it will show 0 (ZERO) or whatever you set in "Value if error" argument of IFERROR() function, like this you can use IFERROR() function for other excel errors (#REF,#DIV,#VALUE,#NAME).
Note: if you are using excel 2003 than instead of IFERROR() function you can use ISERROR() function, but you have to use ISERROR() function with IF() function, see below how you can use ISERROR() function with IF() function (General format).

=IF(ISERROR(the formula to be checked,what if formula result is error value, what if formula result is not error value)


Example: =IF(ISERROR(100/0),0,100/0)


Comments



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: