There are 8 error codes that you’re likely to run into at some point as you work with Excel’s formulas. First, we have the divide by zero error. You’ll see this when a formula tries to divide a number by zero, as in this first example. Note that you’ll see the same error when a formula tries to divide a number by a cell that is empty. You also may see the divide by zero error with the AVERAGEIF or AVERAGEIFS functions, when the criteria does not match any cells in the range. You’ll see a NAME error when Excel does not recognize a name or formula. In the first example, the AVERAGE function is misspelled so it’s not recognized. In the second example, we are trying to get the AVERAGE for a range named “prime”, but that named range does not exist in this worksheet. The NA error means that data is not available. You might see this error when you’re working with a range that contains the NA error, as in this first example. If B7 is blank or contains a normal value, the error disappears. NA is also a common error when you’re using VLOOKUP or MATCH. It means that the lookup value is not found in the table or array. In this case, Sushi is not in the table. If we change the value in B12 to Pizza, all is well. You might see the NUM error if a value is too large, as in this first example. If we make the exponent a smaller value, Excel is able to display the result and the error disappears. In the second example, the first formula is fine and returns 28 months between the dates using the DATEDIF function, but the second formula has the start date and end dates reversed and throws a NUM error. You’ll see a value error when the argument is not the expected type. In the first example, “apple” is not a valid date, so the NETWORKDAYS function can’t compute working days and returns VALUE. The MONTH function can’t extract a month value from “apple” for the same reason. You also might see a VALUE error if you create an array formula and forget to enter the formula with the proper syntax, using Control-Shift-Enter. To fix this problem, just enter using Control-Shift-Enter. The REF error is one of the more common errors you’ll see. It occurs when a reference becomes invalid, most often because cells have been deleted. For example, the formula in B6 sums the three values in column E. If I delete row 10, which contains one of these values, the formula will return the REF error. IF we check the function, we’ll see that the last reference has been replaced by the REF error code. The NULL error is quite rare and occurs when you’ve specified two ranges that don’t intersect. To illustrate, let’s look first at this small sales table. In the formulas below, we are using the space character to specify the intersection of two ranges. This works fine for the first two formulas, which retrieve a specific value from the intersection of both ranges. However, the third formula contains two ranges that do not intersect, so the formula returns NULL. Now let’s look at the first example, which is a SUM function. You can see that this formula is unintentionally using a space instead of a comma. The space is the range intersect operator and because B7 and B8 do not intersect, SUM returns NULL. To fix this problem, just replace space with a comma. Finally, you will often see a string of hash characters when a value does not fit in a cell. Cell B6 contains a simple date, but the date is formatted in a long format so it is not displayed. To fix this error, just make the column wider.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
