Table Of Contents
How to Find Errors in Excel?
Errors are quite common. You will not find a single person who does not make any errors in Excel. When the errors are part and parcel of Excel, one must know how to find those errors and resolve those issues.
When using Excel routinely, we may encounter many errors flagged if the error handler is enabled. Otherwise, we may get potential calculation errors. So if you are new to error handling in Excel, this article is a perfect guide for you.
Find and Handle Errors in Excel
Whenever an Excel cell encounters an error, it will, by default, display the error through the error handler. The error handler in Excel is a built-in tool. We can enable this using this and get the full benefit of it.
As we can see in the above image, we have an error notifier showing that there is an error with the cell B2 value.
You also must have come across this error handler in Excel but are not aware of this. If the Excel worksheet does not show this error handling message, we must enable this by following the below steps:
- We must first click on the "File" tab in the ribbon.
- Under the "File" tab, click on "Options."
- It will open the "Excel Options" window. Next, click on the "Formulas" tab.
- Under "Error Checking," check the "Enable background error checking" box.
At the bottom, we can choose the color which can notify the error. The green color has been selected by default, but we can change this.
Example #1 - Error Handling through Error Handler
When the data format is not proper, we may get errors. So in those scenarios, in that particular cell, we may see that error notification.
- For example, look at the below image of an error.
When we place our cursor on that error handler, it shows the message, "The number in this cell is formatted as text or preceded by an apostrophe."
- To fix this error, we must click on the drop-down list of the icon, and we see the below options.
- The first displays "Number Stored as Text," which is the error. To fix this excel error, look at the second option. The "Convert to Number" mentions clicking on these options. Therefore, it will solve the error.
Now, look at the cell. It has no error message icon now. Like this, we can fix data format-related errors easily.
Example #2 - Formulas Error Handling
Formulas often return an error. To deal with those errors, we need to employ a different strategy. Before handling the error, we need to look at the kind of errors we encounter in different scenarios.
Below are the kind of errors we see in Excel.
- #DIV/0! - If the number is divided by 0 or an empty cell, we may get the #DIV/0 error.
- #N/A - If the VLOOKUP formula does not find a value, then we get this error.
- #NAME? - If the formula name is not recognized, we get this error.
- #REF! - When the formula reference cell is deleted, or the formula reference area is out of range, we may get this #REF! Error.
- #VALUE! - When wrong data types are included in the formula, we may get the #VALUE! Error.
So, to deal with the above error values, we need to use the IFERROR function.
- For example, look at the below formula image.
The VLOOKUP formula has been applied. However, the LOOKUP value "8" is not mentioned in the "Table Array" range A2 to B6, so the VLOOKUP returns an error value as #N/A, i.e., not available error.
- To fix this error, we must use the IFERROR function.
Before using the VLOOKUP function, we used the IFERROR function. If the VLOOKUP function returns an error instead of a result, the IFERROR function returns the alternative outcome, "Not Available," instead of the traditional #N/A error result.
Like this, we can handle errors in Excel.
Things to Remember
- The error notifier will show an error icon if the cell value data type is unsuitable.
- The IFERROR function is typically used to check formula errors.
