Reference: Formula Errors

Error	Notes
#DIV/0!	The formula is trying to divide by zero. Example: You might have a formula where a cell value/result is unexpectedly zero or blank, and the formula is trying to divide by that number.
#ERROR!	A rare error. Most error messages are fairly specific, but #ERROR might merely specify that a syntax error exists, or that you can't submit the formula for an unknown reason.
#FIELD!	A data type error exists.
#GETTING_DATA!	Not a true error, but it might display temporarily in workbook cells when large or complex calculations are in progress. The message disappears when the calculations complete.
#N/A!	A value isn't available to a function or formula. Examples: A lookup function can't find a key value. An array formula has an argument that's a different size (cell range) from another argument. A function has 1 or more missing arguments.
#NAME!	You referenced a defined name or a function that doesn't exist.
#NULL!	You might have: Used an incorrect range or reference separator such as a space (separating individual cell references in a formula with a space instead of a comma). Used a space (the intersect operator) to do a calculation on intersecting ranges but there was no intersection.
#NUM!	A formula has invalid numeric data for the type of operation. Example: Pivot tables don't support calculations when the data range contains multiple currency units. If you have a pivot table that includes salaries, and some salaries are in USD while others are in CAD, you can't do a SUM, AVERAGE, or other calculation on the data; if you hover over the error cell you see A unit conversion issue exists . The only valid function in this situation is COUNTA. To help troubleshoot a #NUM error that might be a units problem, you can view the units settings by selecting View Show Units .
#REF!	A reference isn't valid.
#SPILL!	The most frequent cause of this error is that array formula results can't be placed into the workbook because the results would overlap existing data. When in an entry area, #SPILL occurs when the formula attempts to place data across the boundary of the entry area.
#VALUE!	You used the wrong type of operand or function argument. Example: You see #VALUE if the formula finds spaces, characters, or text where it's expecting a number.
#######	Not actually an error; your column isn't wide enough to display a value.

Tips for Formula Errors

Remember that you must submit array formulas with the appropriate keyboard shortcut. See Concept: Array Formulas in Workbooks
for information about constrained arrays, unconstrained arrays, and more.

If you see an error related to circular references, you might want to review Concept: Circular References in the Worksheets User Guide.

Take note of the workbook setting for automatic or manual recalculation in

File

Settings

Try to simplify complex formulas:

If the formula contains lots of nested functions, separate out one or more of them, placing their results in a separate range that you refer to in the rest of the original formula.

If the formula has external references that appear not to resolve, try merging the workbooks so that the reference is merely to another sheet instead of to a workbook; or, put all the data on 1 sheet.

Displaying User-Friendly Text Instead of Error Codes

Sometimes you use formulas that do calculations based on cell values, and if one of those values is unexpected you end up with an error such as a #DIV/0 error. If you want to plan for the possibility of an error and give the workbook user a more friendly message, you can use:

The IFERROR function or the ISERROR function

The IFEMPTY formula for unconstrained array formulas where the result is an empty array

Example: We have a workbook that shows salespeople's average bonuses for a quarter. To get a bonus, a salesperson must sell more than $5,000 in goods in a particular month. Because Matt didn't get any bonuses, the Avg Bonus cell would contain a #DIV/0 error. To prevent this unfriendly error from displaying, we can nest our AVERAGEIF function in an IFERROR. This formula is in F2:

=IFERROR(AVERAGEIF(B2:D2,">5000",B2:D2),"No bonuses")

	A	B	C	D	E	F
1	Name	Jan Sales	Feb Sales	Mar Sales	# Bonuses	Avg Bonus
2	Matt Bond	$1500	$3275	$2900	Zero	No bonuses

The ISERROR function is similar to IFERROR, but it returns a TRUE or FALSE result based on whether an error exists.