Excel Beginners
Data Types and Formatting: Step 2 To Mastering Spreadsheets
Sometimes it’s right to be wrong
Error Values
What Is An Error Value?
An error value is the result which occurs when an action results in an invalid response. The simplest example of this would be trying to divide 1/0. We know that this results in an error as you cannot have zero as a divisor.
Error values are very easy to spot as all contain a number sign (#), followed by the error type in uppercase letters, and many close with an exclamation mark (!). There are seven error types in Excel.
How Do Error Values Work?
Error values are the response which Excel uses to signify that something has gone away from what could otherwise be an intended result. Errors can occur for a wide variety of reasons, but are most commonly due to human error. The other cases are situations where Excel is unable to return an output based on the inputs which were provided.
What Are Error Values Used For?
The error value which is returned will notify you of the type of issue which occurred. By knowing what each code means, you will be able to quickly isolate what the problem is with that cell.
#DIV/0The divided by zero error code is caused as a response to at least one instance of a division taking place in the formula where the divisor (bottom value) evaluates to zero.
#N/AThe no value available error code is caused due to the fact that the intended value does not exist, and it can even be placed within a cell to denote the fact that a value is missing and needs to be updated at a later time.
#NAME?The name not recognized error code is caused when a formula, named range, text string, or cell reference is improperly formatted. This could be a spelling mistake, or forgetting a quotation mark around text.
#NULL!The range not valid (also considered as intersection is invalid) error is caused in the situation where a set of ranges are given which have no intersecting points. Normally creating a range of cells involves using a colon to separate the start and end value of the range, when the colon is replaced by a space, the null error occurs. This is because excel treats this as if you are comparing both cells as independent ranges, and looks for a point in which they intersect.
#NUM! The invalid number error code is caused when the number result of a formula or function is too large or small to be shown, or if the value cannot be calculated due to an invalid argument. An example of this would be attempting to take the square root of a negative number.
#REF!The invalid cell reference error is displayed whenever the cell being referenced in a formula or function is deleted, or pasted over. This is an important indication that a recent change to the document caused issues in existing parts of the sheet, and to revert these changes if possible.
#VALUE!The incorrect argument error signals that a value of the wrong type was placed within a formula or function. Most often, this is due to a text value being passed into an argument that is meant to be a number.
Now knowing that each error corresponds to, identifying the cause of one within a formula will become much easier. Even the most skilled Excel users will cause, or come across, these errors. An important part of being skilled in Excel is being able to identify the cause behind a workflow that results in errors or incorrect information.