How to Fix Your Problems: Remove #N/A and Other Errors from Your Spreadsheet

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. Most likely you have encountered this error as you copy and paste a formula down a large set of data or if you use more complicated functions such as VLOOKUP. Although these errors can be useful to detect possible errors, for the most part they cause speadsheets 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)

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.