In Excel, to ignore all the errors that you get while using formulas, you can use an error-handling function. You can wrap your original formula with an error-handling function that shows a meaningful result when an error occurs. Other than that, you can use the ignore error option as well that you have in Excel.
In this tutorial, we will look at all those methods that we can use.
Using IFERROR to Ignore Errors and Replace it with a Meaningful Message
The best way to deal with errors is to use the IFERROR function. In this function, you have two arguments to define. The one is the actual value that you need to test for the error and then the value that you want to get if an error occurs.
In the following example, you can see we have used the same formula, but we have wrapped it in the IFERROR and it has returned the value that we have defined.
=IFERROR(1/0,"There's an error.")IFNA Function to Ignore #N/A Error
If you specifically want to ignore the #N/A error and replace it with a meaningful message, then you can use the IFNA function that only deals with the #N/A. This function also has two arguments that you need to define.
As you can see in the following example, we have the error in cell D1 which comes from the VLOOKUP function.
Now to deal with this error, you can use the IFNA (you can also use IFERROR with the VLOOKUP, but we will use IFNA here).
=IFNA(VLOOKUP("E",A1:B5,2,0),"Value is available.")Ignoring Error from the Options
When you get an error in a cell, Excel gives you an option along with that cell where you can use the “Ignore Error” option. As you can see in the following example, you have an #DIV/0! error in cell A1.
Now with cell A1, you have a small icon on the right side, and when you click on this icon it further gives a you list of options. And from these options, you can select the “Ignore Error” option.
And once you click on this option, Excel stops marking that cell as an error cell.
Turn ON-OFF Error Checking Options
Apart from that, you can use error-checking options from the Excel options to deal with errors in Excel and ignore them.
When you click on “Error Checking Options”, you get the Excel Options dialog box, from where you can turn on and off the error checking options.
From these options, you need to un-tick the “Enable background error checking” to turn off the error checking options. The moment you do this it will turn off error checking for the entire Excel application.