Types of Errors
There are many types of errors within Excel and it is important to understand the differences between them and why they occur. Below are some error values, what they mean and what they are generally caused from. #DIV/0 – The #DIV/0 error will occur when the division operation in your formula is referring to an argument that contains a 0 or is blank. #N/A – The #N/A error is, in fact, not really an error. This more so indicates the unavailability of a necessary value. The #N/A error can be manually thrown using =NA(). Some formulas will throw the error message as well. #NAME? – The #NAME? error is saying that excel can’t find or recognize the provided name in the formula. Most often this error will appear when your formulas contain unspecified name-elements. Usually these would be named ranges or tables that don’t exist. This is mostly caused by misspellings or incorrect use of quotations. #NULL! – Spaces in excel indicate intersections. It’s because of this that an error will occur if you use a space instead of a comma (union operator) between ranges used in function arguments. Most of the time you will see this occur when you specify an intersection of two cell ranges, but the intersection never actually occurs. #NUM! – Although there are many circumstances the #NUM! error can appear, it is generally produced by an invalid argument in an Excel function or formula. Usually one that produces a number that is either too large or too small and cannot be represented in the worksheet. #REF! – Commonly referred to as “reference”, #REF! errors can be attributed to any formulas or functions that reference other cells. Formulas like VLOOKUP() can throw the #REF! error if you delete a cell that is referred to by a formula or possibly paste over the cells that are being referred too. #VALUE! – Whenever you see a #VALUE! error, there is usually an incorrect argument or the incorrect operator is being used. This is commonly seen with “text” being passed to a function or formula as an argument when a number is expected.
Error Tracing
Excel has various features to assist you in figuring out the location of your errors. For this section, we are going to talk about Tracing errors. On the Formulas tab in the “Formula Auditing” section you will see “Trace Precedents” and “Trace Dependents”. In order to use these, you first have to activate a cell containing a formula. Once the cell is active, select one of the trace options to assist you in resolving your issue. Tracing dependents shows all cells that the active cell is influencing whereas Tracing precedents shows all cells whose values influence the calculation of the active cell.
The Error Alert
When formulas don’t work as we expect them to Excel helps us out by providing a green triangle indicator in the top left corner of a cell. An “alert options button” will appear to the left of this cell when you activate it. When hovering over the button, a ScreenTip will appear with a short description of the error value. In this same vicinity, a drop-down will be presented with the available options:
Additional Error Handling
Although the above error handling features of Excel are nice, depending on your level this may not be enough. For instance, say you have a large project where you are referencing various data sources of information that is populated by multiple members of your team. It is likely that not all members are going to input the data the exact same all the time. This is when advanced error handling within Excel comes in handy. There are a few different ways to handle errors. IFERROR() is a valuable formula providing you two different processes depending on an error being present or not. Other options are using formula combinations such as IF(ISNUMBER()). Most of the time this formula combination is used with SEARCH(). We know that when Excel returns something TRUE it can be represented by a 1. So, when you write =IF(ISNUMBER(SEARCH(“Hello”,A2)),TRUE,FALSE) you are saying, “If you find Hello in A2 return a 1, otherwise return a 0.” Another formula that comes in handy for later versions of Excel is the AGGREGATE() function. We’re going to go over some brief examples below.
Generating an error without the use of IFERROR()
Using the AGGREGATE() function can be a little daunting if you have never used it before.
So as you can see there are many options and ways to handle errors within Excel. There are options for very basic methods such as using the Calculation Steps to assist you or more advanced options like using the AGGREGATE function or combining formulas to handle various circumstances. I encourage you to play around with the formulas and see what works best for your situation and your style.
How to Handle #Div/0! Errors in ExcelHow to Fix Bishock2 Crashes and Stopped Working ErrorsHow to Fix ESRV_SVC_WILLAMETTE Event Viewer ErrorsThe Most Common Blue Screen Errors on Windows 7, 8 and 10
