I have been asked so many times how to get rid of the #N/A or #REF errors is an Excel spreadsheet without manually going through them.
My Top Excel Tip of all time is to use the IF and ISERROR functions to eliminate error messages in cells.
The IFERROR function also works great for this, but you need to be using Excel 2007 for greater for this.
To do this, let’s say you have a VLOOKUP function that results in an error:
Column B has the VLOOKUP in it to pull in the grades from the table in D through F. Cell B3 has an error come up because Jill is not in the table on the right.
To get rid of this error, we need to insert the IF and ISERROR functions.
Our new function will look like:
What it is doing is that the main function is the IF statement.
The criteria is ISERROR(VLOOKUP(A3,D:F,2,FALSE)) . Excel is check if the VLOOKUP is an error. If it is an error it gives us the TRUE result.
The TRUE result is the second part of the IF statement, which I set to “”. That just means it will leave it blank.
The last part is the FALSE statement. It is simply the VLOOKUP function we originally had.
Below is a short clip of how you can do this quickly by copying and pasting.
The IFERROR function will work the same way as long as you are using Excel 2007 or greater.
Let’s say we have the following:
We are doing a VLOOKUP in cells B2 and B3 to get scores from the table on the right.
We can use the IFERROR function as seen above.
The VALUE is the formula we want to show if its not an error. In this case it is our VLOOKUP
The VALUE_ERROR is the value we want to show if it is an error. In this case it is a 0.
So after we do that formula in both cells, we get a 0 in B3, which means there is an error. Which there is because Jim is not in the table we are looking at.
There are so many great Excel tips out there and it’s hard to pick just one. But to me, this one is my top one, just because of the number of people who didn’t know it and have since started to use it in my office.
To Check out the Power Pivot course by Chandoo, go to http://bit.ly/PowerPivotExcelCourse