An error message in a cell is not a failure: it is information. The spreadsheet is telling you that something does not add up and, through the type of error, it also points you in the direction to look. The real danger is wrong results that produce no error at all. Learning to read these codes saves a lot of time. The names here are those used in English versions of Excel, Google Sheets and LibreOffice; the Italian Excel equivalent is given in brackets.

#N/A (#N/D): value not available

This is typical of lookup functions such as VLOOKUP, XLOOKUP and MATCH: the value you searched for was not found. The most common causes:

• the value really does not exist in the table;
• it exists but with an extra space, or as text instead of a number;
• the lookup range is not locked with dollar signs and, when the formula was copied, it slid past the data.

To show a message instead of #N/A use IFNA (SE.NON.DISP.), which catches only this error. Do not use it, though, to hide a data problem that ought to be fixed.

#VALUE! (#VALORE!): wrong data type

It appears when an operation receives a type of data it cannot handle. The simplest example is ="abc"*2. In practice it almost always happens with:

• numbers or dates stored as text, perhaps imported from another program;
• invisible spaces in cells that look empty;
• ranges of different sizes in functions that require them to match.

A useful detail: adding with the + operator returns #VALUE! if a cell contains text, whereas the SUM function ignores text. That can hide the problem rather than solve it, so always check why a cell contains text.

#REF! (#RIF!): invalid reference

The formula points to a cell that no longer exists. This happens when:

• you delete rows, columns or sheets the formula referred to;
• you copy a formula with relative references to a position where they would fall off the sheet, for example a formula looking three rows up copied into row 1;
• VLOOKUP's column index is greater than the number of columns in the table.

REF! replaces the lost reference in the formula text, so the formula has to be fixed by hand or restored with Undo. It is a good reason to avoid blanket IFERROR wrappers: they would hide this too.

#DIV/0!: division by zero

A division where the denominator is zero or an empty cell. It is common in percentage and average calculations on rows not yet filled in. The clearest solution is to check the denominator:

=IF(B2=0, "", A2/B2)

Returning an empty string is acceptable for rows not yet filled in; if the zero is an anomaly, explicit text such as "Total missing" is better.

#NAME? (#NOME?): name not recognised

The program does not recognise something you typed:

• a misspelt function name, for example =SUMM(A1:A5);
• a function name in the wrong language, such as =SOMMA() in an English Excel;
• text without quotes, for example =IF(A2=Paid, ...) instead of "Paid";
• a named range that does not exist or has been deleted;
• a function that does not exist in the version in use.

While typing, the function name autocomplete is the simplest way to avoid it.

#NUM!: numeric problem

The calculation produces an invalid or out-of-range number, for example the square root of a negative number, or iterative functions such as RATE or IRR that cannot find a solution. In the second case it is often enough to supply an initial guess as the optional argument.

#NULL! (#NULLO!)

Rare: it appears when you use the intersection operator (the space) between two ranges that do not cross. It is usually a typo, such as a space instead of the separator between two arguments.

#SPILL! (#ESPANSIONE!)

In versions with dynamic arrays, a formula such as FILTER or UNIQUE has to write several results into adjacent cells. If one of those cells already contains something, this error appears. When you select the cell, the program shows the required area with a dashed border: clear it and the result will appear.

Tools for investigating

• Evaluate Formula (Formulas, Evaluate Formula in Excel) runs the formula step by step and shows where the error arises.
• Trace Precedents draws arrows to the cells the result depends on.
• F9 on part of the formula, in edit mode, shows the value of that piece alone. Remember to press Esc so you do not replace the formula with the result.

In my work, when I inherit a sheet full of IFERROR, the first thing I do is remove them temporarily: the hidden errors say a lot about how the file is built.

In short

• N/A means a value was not found; the cause is often in the data, not the formula.

• VALUE! flags a wrong data type, typically numbers stored as text.

• REF! means a reference was lost, usually because cells were deleted.

• DIV/0!, #NAME? and #SPILL! have precise causes that are easy to fix.

• Prefer IFNA to IFERROR and use Evaluate Formula to see where the problem starts.