Spreadsheet Error Values
An error value is what a spreadsheet puts in a cell when it will not guess: a value of its own kind, distinct from a number or text, that says which step could not be completed. It is the spreadsheet’s equivalent of an exception, and like an exception it is evidence rather than noise. Microsoft’s reference lists seven of them, each with a specific cause, along with one display symbol that only looks like an error.
Grouped by what they actually tell you: #DIV/0! means division by zero or by a cell with no value. #VALUE! means the formula met a data type it cannot use — usually text where a number or date belongs. #NUM! means the numbers themselves are invalid for the operation, such as a reversed date range. #N/A means a lookup found nothing. #NAME? means the application does not recognize some text in the formula, typically a misspelled function. #REF! means a reference is no longer valid, classically because the cells it pointed at were deleted. #NULL! means two ranges that were asked to intersect do not. And ##### is not an error at all — the column is too narrow, or a date or time went negative.
Two properties make them worth understanding rather than clearing. They propagate: a single #N/A inside a range turns the SUM that reads it into #N/A too, which is why one broken lookup can blank an entire report. And they are positional evidence — the first cell to show an error is rarely where the mistake was made, so read outward from it the way you would read a traceback.
IFERROR replaces any error with a value you choose, which is correct when the error is expected and carries no information, and dangerous otherwise: it catches a misspelled function and a deleted column just as readily as the empty lookup you meant to handle. Suppress only the error you have diagnosed, prefer a visible label such as “n/a” to an empty cell, and note that Google Sheets additionally shows #ERROR! for a formula it cannot parse — a message to read as “invalid syntax”, not as a diagnosis.
References:
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.