Gearing up with Excel Error handling
MS Excel is an efficient tool for calculations encompassing algebraic and arithmetic equations. All a user needs to do is enter a formula, and pretty much any step can be automated. Need to merge multiple sheets? Need to perform complex calculations? MS Excel can do it.
Continuing our tutorial and trick-guide of MS-Excel basics, we today demonstrate how errors are handled efficiently.
First things first, Error Value Codes
Irrespective of the version you are using, Excel always shows an error value if it is unable to compute the value of a formula. For example, if you divided a number by zero (e.g. using the formula =A1/0) Excel would return the error value #DIV/0.
All error values in Excel begin with the hashtag sign (#). The following table shows MS Excel’s error values, along with their meaning and the most probable cause.
| Error Value | Meaning | Causes |
|---|---|---|
| #DIV/0 | Division by zero | The division operation in your formula refers to an argument that contains the value 0 or is blank. |
| #N/A | No value available | Technically, this is not an error value but a special value that you can manually enter into a cell to indicate the unavailability of a necessary value. |
| #NAME? | Excel doesn’t recognize a name | This error value appears when your formula contains unspecified name-elements like non-existing functions or ranges. It occurs mostly because misspelling of functions or missing quotation marks around text strings in a formula. |
| #NULL! | You specified an intersection of two cell ranges whose cells don’t actually intersect | Because a space indicates an intersection, this error will occur if you insert a space instead of a comma (the union operator) between ranges used in function arguments. |
| #NUM! | Problem with a number in the formula | This error can be caused by an invalid argument in an Excel function or a formula that produces a number too large or too small to be represented in the worksheet. |
| #REF! | Invalid cell reference | This error occurs when you delete a cell referred to in the formula or if you paste cells over the ones referred to in the formula. |
| #VALUE! | Wrong type of argument in a function or wrong type of operator | This error is most often the result of specifying a mathematical operation with one or more cells that contain text. |
In order to remove an error value from a cell, you must discover what caused it to appear and edit the formula accordingly. Once you have corrected all errors, Excel will complete the desired calculation.
Tracing down errors
The cause of an error can be traced by using Excel’s tracing tool.
Firstly, you have to activate the cell with the erroneous formula by clicking on it. In the next step, you need to click on “Trace Precedents” in the formula’s button set. This shows all cells, whose values influence the calculation of the active cell.
In contrast, “Tracing Dependents” shows all cells that the active cell is influencing. This feature is useful to understand how an existing error is propagated throughout a complicated Excel-model.
Partial Calculation
Simpler formulas with low depth can be quickly debugged by using the partial calculation feature. To figure out which term of a formula contains an error, you mark parts of it and evaluate them by pressing F9. Excel will calculate only the marked expression. (Attention: Pressing ENTER fixes the value permanently, while only pressing ESC restores the original formula.)
Partial calculations are a favorable approach for simple formulas with low depth. However, a more frequent case is the occurrence of errors in complicated multi-level formulas.
When a formula yields an error value (other than #N/A) in a cell, Excel displays a green triangular error indicator in the upper-left corner of the cell. An “alert options button” appears to the left of that cell when you activate it.
If you position the mouse pointer on that button, a ScreenTip appears, describing the nature of the error value. A drop-down button also appears to its right. You can click the button to avail a drop-down menu with the following options:
- Help on This Error: Opens an Excel Help window with information on the type of error value in the active cell, including steps to correct it.
- Show Calculation Steps: Opens the Evaluate Formula dialog box, where you can walk through each step in the calculation to see the result of each computation.
- Ignore Error: Bypasses error checking for this cell and removes the error alert and error options button from it.
- Edit in Formula Bar: Activates the Edit mode and puts the insertion point at the end of the formula on the “Formula bar”.
- Error Checking Options: Opens the Formula tab of the Excel Options dialog box, where you can modify the options used in checking the worksheet for formula errors.
Advanced Excel Error Handling
At times, errors are part of your spreadsheet model and you can’t avoid them entirely. For example, the model has to handle multiple, large external datasets that contain faulty sections. In this case, you would need a sophisticated error handling strategy to automate the ad hoc error handling process.
An efficient method is to use the IFERROR formula, which can follow two different processes depending on whether the cell returns an error-code. If the “IFERROR” formula is unavailable, then you can emulate a similar functionality by using a “IF(ISNUMBER()” combination. In upgraded versions of Excel, starting from Excel 2010 upward, you can use an “Aggregate” formula to handle error values. Let’s see examples of these instances below:
-
Without IFERROR :
SUM(IF(ISNUMBER(Range),Range,””) and use Ctrl+Shift+Enter for converting regular formula into an array formula.
-
With IFERROR :
From MS-Excel 2007 upward, you can get a general option for ignoring error values using IFERROR formula to display any character instead of the error value. The IFERROR function also works as an array function.
-
In Excel 2010:
The “Aggregate” function provides an opportunity to choose various options such as error handling. The formula construction can be referenced from the screenshot below.
In this screenshot, the first argument (9) of Aggregate function stands for “SUM”, while the second argument (6) is required for ignoring error values. Finally, the third argument encompasses the sum range.
