Detect errors in formula Excel

Detect errors in formula Excel

Errors:

In excel there are various types of errors as listed below. Excel offers great tool to trace and fix the errors in formulas in a sheet. These errors can be trapped using Error Checking command in a sheet.

List of excel errors:

  1. #DIV/0 error: occurs when you try to divide a value by 0
  2. #Name error: occurs when you are using reference formula having name range, custom function or incorrect cell reference coming form other file, sheet etc. which does not exists
  3. #N/A error: occurs when something is not found like cell reference or Name range or user defined function etc.
  4. #NUM error: occurs when a number is too large or small, or when a Excel is unable to handle calculation.
  5. #VALUE error: occurs when a value is not an expected or valid type (i.e. date, time, number, text, etc.) This can happen when a cell is left blank, when a text value is given to a function that expects a numeric value, or when dates are evaluated as text by Excel.
  6. #REF error: occurs when a reference becomes invalid. In many cases, this is because sheets, rows, or columns have been removed.
  7. #NULL error: usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references.
  8. #### (HASH) error: technically not an error, the contents of you cell are shrink if you auto fit the cell it goes well.
  9. #SPILL! error: occurs when a formula outputs a spill range that runs into a cell that already contains data.
  10. #CALC! error: occurs when a formula runs into an calculation error with an array.

Formulas can sometimes result in error values in addition to returning unintended results. The following are some tools that you can use to find and investigate the causes of these errors and determine solutions. To check error manually follow the steps:

  • Select the worksheet you want to check for errors. If the worksheet is manually calculated, press F9 to recalculate.
  • Navigate to Formulas ribbon tab
  • Click on Error Checking under Formula Auditing group a dialog appears as shown below

With the help of above dialog you can trace and fix all the available formulas which are end up with an error. Let’s understand each.

Help on this Error: this button provides relative root cause and fixes which you can apply to fix error in your formula online

Show calculation steps: this button actually provides you dive into excel formula step by step like a programmer does to understand which argument in your formula is broken cause error. It will launch another dialog Evaluate Formula where you can step in and check the issue.

Ignore Error: by clicking on this button you can ignore the error.

Edit in Formula Bar: will activate the formula bar editing where you can self make the changes and fix the error

Previous/Next: will navigate next or previous error within sheet.

Options…: will pops up Excel options dialog where you can manipulate formula related features as shown below:

Next >> Watch and trace cell formulas in Excel

Leave a Reply

Your email address will not be published.