Excel Quick Tip: Eliminate Spreadsheet Errors with IFERROR

Spreadsheet errors appear as unprofessional and can put the integrity of your whole report into question. However, there are some circumstances where spreadsheet errors are unavoidable. By using the IFERROR statement, you can manage how errors appear on a spreadsheet.

Quick Tip: IFERROR

IFERROR is a useful spreadsheet function added in Excel 2007. It helps eliminate extraneous spreadsheet errors in a single statement. The IFERROR function will test if a formula (first parameter) will return any common errors, including: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. If an error is returned, the function will return a user-specified value (2nd parameter) instead of the error. If no error is returned, the value from the formula will be presented.

Basic Syntax

=IFERROR([Test Formula],[Value If Test Formula Returns Error])

Basic Application Examples

Division by Zero


Will return “#DIV/0!”, a division by zero.


Will return “0”. Will not appear as an error and will not look unusual.


Can be used in VLOOKUP formulas where a value may not exist in a table.


=IFERROR(VLOOKUP(“Bananas”,A:B,2,FALSE),”No Inventory”)

The VLOOKUP formula within the IFERROR statement searches an inventory list for the quantity of bananas. Given that there is no banana quantity, and it doesn’t exist in the table, the error “#N/A!” would normally be returned. However, now that it is in an IFERROR statement, the value “No Inventory” will be returned.

Form Errors

Can be used to help users properly fill out forms, or even validate data.


The above example returns a mathematical error. The user should have entered the number 4 instead of spelling “four”.

=IFERROR(B3*C3,”Error: Use Numbers”)

This formula will provide the user with a helpful tip, informing them of the error and providing a helpful tip.


Can Be Used In Place Of

  • IF(ISNA(…

Leave a Reply