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 learn some of the most useful methods for ignoring errors or handling them in a way that will not affect the data’s result. We will also learn in the end of the that what are the reason that cause errors while using formula.
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. It allows you to replace error values (like #DIV/0!, #N/A, etc.) with a value you choose, such as zero, a blank cell, or a custom text message.
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.")
- Calculation Part (1/0) – This tries dividing 1 by 0. In mathematics, dividing by zero is undefined and will result in an error. In Excel, this specific division will generate a #DIV/0! Error.
- IFERROR Function – In the First Argument (1/0), Excel first attempts to divide. And then in the second argument (“There’s an error.”), this is the value that Excel should return if the first argument results in an error.
You can also use a formula like below which can help you to get the a 0 instead of a text message and this will not make any difference if you performing a calculation.
=IFERROR(1/0,0)
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.
The Error Checking Options provide the option to manage how Excel deals with formula errors.
These settings allow you to control how Excel flags potential errors in your worksheets. Here’s how you can access and configure the Error Checking Options:
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.
Why Errors Occurs in Excel Formulas
Errors occur in Excel formulas for various reasons, typically indicating something is wrong with how a formula is written or the data it references. Understanding these errors can help troubleshoot and refine formulas to ensure they work correctly. Here are some common reasons why errors might occur:
- Division by Zero (#DIV/0!): This happens when a formula tries to divide a number by zero. For example, =A1/B1 will result in this error if B1 contains zero.
- Non-Existent Cell Reference (#REF!): If your formula refers to a cell that doesn’t exist, perhaps because it was deleted or the reference is incorrect, you’ll get this error. This often occurs after deleting columns or rows on which a formula depends.
- Invalid Formula (#VALUE!): This error appears when a formula has the wrong type of argument. For example, adding a numeric value to a text string.
- Name Error (#NAME?): If Excel doesn’t recognize text in a formula, it will display this error. It often happens when function names are misspelled or named ranges are not defined.
- Null Value (#NULL!): This error is not common and occurs when you specify an intersection of two areas that do not intersect. It’s often caused by a misused space character in formulas that implicitly intersect multiple ranges.
- Not Available (#N/A): This error is used to signify that data is not available to a formula, often used intentionally with lookup functions like VLOOKUP when a lookup value is not found.
- Number Error (#NUM!): This occurs when a formula or function contains invalid numeric values. It’s a common issue with functions that require strict numeric input, such as the square root of a negative number.
- Getting Data (#GETTING_DATA): This is a temporary error seen in Excel for Office 365. It indicates that Excel is currently fetching data required by a formula but hasn’t completed the task yet.
Each error tells you how your formula is written and what might need to be fixed. Using the methods mentioned above in the formula, you can ensure your formulas works correctly.
Do I Really Need to Ignore Errors
As we have discussed, ignoring errors in Excel formulas using the IFERROR function or other methods can offer several benefits, especially when preparing complex data or reports for presentation.
- Readability: By replacing error with custom text or zero, data becomes easier to read and understand. This is particularly helpful when presenting data to individuals who may need to become more familiar with Excel’s error codes.
- Prevent Further Errors: Errors in one part of a data can also cause other formulas that depend on those cells to fail. Handling these errors can prevent such cascading errors, ensuring more formulas in your worksheet correctly.
- Custom Error Responses: You can provide specific messages or values that are more meaningful in the context of your data. For example, instead of showing #DIV/0!, showing “No data” or “Not applicable” can be more informative.