Find Errors in Excel

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.

Find Errors in Excel

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

You can download this Error Checking Excel Template here – Error Checking Excel Template

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.

Error Checking Excel Example 1

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.

  1. Step #1 – Click on the FILE tab in the ribbon.

    Error-Checking-Excel-Example-1.1

  2. Step #2 – Under the FILE tab, click on “Options.”

    Error-Checking-Excel-Example-1.2

  3. Step #3 – This will open the Excel Options window. Click on the “Formulas” tab.

    Error-Checking-Excel-Example-1.3

  4. Step 4 – Under “Error Checking,” check the box of “Enable background error checking.”

    Error-Checking-Excel-Example-1.4

    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.
Error Checking Excel Example 1.5

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.
Error Checking Excel 1.6
Example 1.7

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.

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.
Example 1.8

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.
Error Checking Excel Example 1.9

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
LEARN MORE >>