Errors are part of every Excel spreadsheet you have ever worked on. Without errors completing a project is not possible. Errors are not necessarily made intentionally but most of the errors simply appear by chance and you have no idea about them. So, the question is how to find errors in Excel sheet. If the errors are not identified then how would you fix them?
You may have to face troubles while getting results when there are errors in the sheet but unidentified. With Error Handler you can simply find errors in a sheet. An error handler is a feature used in Excel that simply helps in finding errors. Let’s see how you can find errors while working on a sheet.
Formula Errors:
Just as the name shows, formula errors are those errors you encounter because of formulas or functions used to execute a task. Below are some examples:
#DIV/0!: In this error, you will see a number is divided by 0 or with a blank cell.
#N/A: When a value is not available to a function or formula this error occurs such as VLOOKUP is unable to find a match.
#NAME?: When a part of the text is unrecognized in a formula, you often see this error, such as in a named range you added text with a typo.
#NULL!: This error occurs when the intersection of two areas doesn’t intersect.
#NUM!: When a formula or function has invalid numeric values you may encounter this error such as if IRR is unable to find an outcome.
#REF!: It often appears when a cell reference is not valid.
#VALUE!: It appears when a formula has cells with multiple data types. For instance, in two cells when one value is numeric and another value is a text.
How to Find and Deal with Errors in Excel?
Error handler always shows an error whenever a cell in Excel appears with an error. This built-in feature must be enabled to find an error in Excel. Otherwise, you cannot find an error in the sheet.
In this example, you will notice an error is notified with the cell B2 value. If you see the error handler is not working and errors are not being notified, you need to first enable the error handler. For this, below is the procedure to follow:
- Open the File tab from the ribbon.
- Select Options from the File tab.
- A new Excel Options window will pop up. Now, click on the Formulas tab.
- Check the “Enable background error checking” box given under the “Error Checking” menu.
- In the end, you can select a color in which the errors are notified. By default, the green color is selected that can be changed.
How to Find Errors with Go to Special?
With the help of the Go To Special feature, you can simply perform this task. Let’s follow the steps:
- Click on the Find & Select option from the Home tab.
- Now, choose the “Go To Special” option from the menu.
- Click on the Formulas tab and check the Errors button.
- Click Ok and you will see the cells are formatted with errors in gray color on the sheet.
How to Use Inquire Add-In to Find Errors in Excel?
Updated versions of Excel have Inquire add-in feature. When the Inquire tab is not visible in the ribbon, you need to activate it. For this, open the File menu.
- Click on “Options” and then choose “Add-ins”.
- Select COM Add-ins at the end of the Manage option.
- Click on the Go button located beside Inquire. Now, the Inquire tab will start showing in the ribbon.
- Click on Workbook Analysis once the Inquire tab starts showing.
How to Find Errors with Automated Error Checking?
Excel by default lets you see some formula errors by highlighting the green triangle present on the top left side of a cell. To explain that error, you simply need to select that cell and choose the trace error button.
You can make settings according to your needs and you can handle errors that appear with this green triangle.
Open the File tab, click Options, and then choose Formulas:
Things to Remember:
When the cell value data type is not suitable, you will see an error icon displayed by the error notifier.
The IFERROR function is normally used to check formula errors.
To Wrap Up:
In this post, you have come to know a few ways that let you identify an error that occurred in the Excel sheet. You can try all of the above methods to find formula errors as well as other errors.
