Excel formula errors and fixes – Best article

In this video, we’ll review the error codes that Excel displays when there’s something wrong with a formula.

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 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 by a cell that is empty.

You also may see the divide by zero error with the AVERAGEIF and 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 range does 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.

In the second example, the first formula is fine and returns 28 months between the dates using the DATEDIF function, but the send formula has the start date and end dates reversed, and throws a NUM error.

You’ll see a value error when an argument is not the expected type. In the first example, “apple” is not a valid date, so the NEYWORKDAYS 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’ll also see a VALUE error if you create an array formula and forget to enter the formula with the proper syntax, using Contrl + Shift + Enter.

The REF error is one of the most 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 function will return the REF error. IF we check the function, we’ll see that the last reference has been replaced with the error code.

The NULL error is quite rare and occurs when you’ve specified two ranges that don’t intersect. To illustrate, 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 at 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 unintentionally uses a space instead of a comma. Since the space is the range intersect operator and because B7 and B8 do not intersect, SUM returns NULL.

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 with a long format so it is not displayed. To fix this error, just make the column wider.

 

Leave a Reply