How to Find Errors in Excel?
Errors are quite common, and you will not find a single person who doesn’t make any errors in excel. When the errors are part and parcel of the excel, one must know how to find those errors and also how to resolve those issues.
When we use excel on a routine basis, we will encounter many errors flagged if the error handler is enabled; otherwise, we will get potential calculation errors as well. So if you are new to errors handling in excel, then this article is a perfect guide for you.
You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Find Errors in Excel (wallstreetmojo.com)
Find and Handle Errors in Excel
Whenever an excel cell encounters an error, it will, by default, shows us the error through the error handler. The error handler in excel is a built-in tool, so we need to enable this to start 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 not aware of this. If your excel worksheet is not showing this error handling message, then we need to enable this by following the below steps.
- Step #1 – Click on the FILE tab in the ribbon.
- Step #2 – Under the FILE tab, click on “Options.”
- Step #3 – This will open the Excel Options window. Click on the “Formulas” tab.
- Step 4 – Under “Error Checking,” check the box of “Enable background error checking.”
And also, at the bottom, we can choose the color which can notify the error; by default, green color has been chosen, but we can change this.
Example #1 – Error Handling through Error Handler
When the format of the data is not proper, we end up getting errors, so in those scenarios, in that particular cell, we 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 saying, “The number in this cell is formatted as text or preceded by an apostrophe.”
- So to fix this error, click on the drop-down list of the icon, and we see the below options.
- The first one says that “Number Stored as Text,” so this is the error. To fix this excel errorExcel ErrorErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.read more, look at the second option. It says “Convert to Number” click on these options, and the error will be fixed.
Now, look at the cell. It has no error message icon now, so like this we can fix data format related errors easily.
Example #2 – Formulas Error Handling
Formulas often return an error, and 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 are going to 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, then we end getting this #DIV/0 error#DIV/0 Error#DIV/0! is the division error in Excel which occurs every time a number is divided by zero. Simply put, we get this error when we divide any number by an empty or zero-value cell.read more.
- #N/A – If the VLOOKUP formula does not find value, then we get this error.
- #NAME? – If the formula name is not recognized, then we get this error.
- #REF! – When the formula reference cell is deleted, or the formula reference area is out of range, then we get this #REF! Error.
- #VALUE! – When wrong data types are included in the formula, you get this #VALUE! Error#VALUE! Error#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more.
So in order to deal with the above error values, we need to use the IFERROR functionIFERROR FunctionWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula.read more.
- For example, look at the below formula image.
VLOOKUP formula has been applied, lookup value “8” is not there in the “Table Array” range A2 to B6, so VLOOKUP returns an error value as “#N/A” i.e., not available error.
- To fix this error, we need to use the IFERROR function.
Before using the VLOOKUP function, we have used the IFERROR function, so if the VLOOKUP function returns an error instead of a result, then the IFERROR function returns the alternative result. “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 in case if the cell value data type is mismatched.
- IFERROR function is typically used to check formula errors.
Recommended Articles
This has been a guide to Finding Error in Excel. Here we discuss how to find and handle errors in excel along with practical examples and a downloadable excel template. You may learn more about financing from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
47.1K subscribers