How to Fix Errors: Remove #N/A and other Errors from your Spreadsheet

fixerrors1 If you have eaten food in the last 3 days then chances are you have come across the annoying #N/A or #DIV/0! error in Excel.

Although these errors can be useful to detect possible errors, for the most part they cause spreadsheets to look messy and raise unnecessary questions from end users. Excel has created a very easy way to get around this error. Forget 3 steps, this will only take 1!

Let’s say the formula A1/B1 you copied down Column C has some pesky zeros in Column B which inevitably causes the dreaded #DIV/0!. To fix this, try copying and pasting the following formula instead:

=IFERROR(A1/B1, 0)

fixerrors2

fixerrors3

This formula is saying the following: If A1/B1 causes any type of error, put a 0 there instead of an error message, else, put the result of A1/B1.

You are not limited to replacing the error with a number. You could also put text:

=IFERROR(A1/B1, “You cannot divide by zero!”)

You can use IFERROR for any formula. For instance, here is how you would use it for a VLOOKUP:

=IFERROR(VLOOKUP($A$1:$B$8, 5, 2), 0)

Take a few minutes this week and apply the IFERROR to your formulas!

Get your mother something special next Sunday by giving her a clean-looking spreadsheet. You are bound to be hailed as the greatest son/daughter in the family.