Excel #SPILL!, #REF! and #N/A: Recovering a Model Without Hiding the Problem

These three errors describe three completely different failures. The first means the formula is valid but has nowhere to spill into. The second means the reference itself no longer exists, usually after a deletion. The third means the lookup ran correctly and did not find the value. Only the first is solved by clearing cells.

Three errors, three different stories

The third row is the practical difference. Two of them are messages; the third is evidence that something structural in the workbook has already been destroyed.

What causes a spill error and how do you clear it?

The underlying cause is always the same - the result does not fit - but Microsoft documents several variants, and each needs a different move:

• The spill range isn't blank. One or more cells in the range already contain data. Microsoft notes the range may appear blank while still containing invisible characters or formulas that return an empty string.
• The spill range is too big. A sheet holds 1,048,576 rows and 16,384 columns, and any spill beyond that fails. This happens when a whole-column reference is used from a cell that is not in row one.
• An array formula inside a table. Spilled array formulas are not supported in Excel tables. The fix is to move the formula out of the table or convert the table to a range.
• Merged cells in the way. A spill cannot enter merged cells. Un-merge them or move the formula to a range that does not intersect them.
• Out of memory. The formula is attempting to produce an array too large to hold. Reference a smaller array or range.

The fast tool: click the error indicator and choose Select Obstructing Cells, and Excel highlights exactly which cells are preventing the spill instead of leaving you to find them.

A reference error is not a bug - it is damage

It appears when a formula refers to a cell that is no longer valid, and that happens most often when cells referenced by the formula were deleted or pasted over. The substantive difference from the other two: the information about what used to be there no longer exists in the formula. Excel does not remember where you were pointing.

The common forms, as Microsoft describes them:

• Deleting a row or column that was named in an explicit cell-by-cell reference. A range reference survives this far better than a comma-separated list of individual cells.
• A VLOOKUP asking for a column number larger than the number of columns in the lookup range.
• An INDEX asking for a row or column outside the boundaries of the defined range.
• An INDIRECT pointing at a workbook that is closed.
• Broken OLE or DDE links to a program that is not running.

The operational rule: if you are looking at a reference error that just appeared, stop working and do not save. Undoing immediately after the deletion is the only route that returns the reference without rebuilding it by hand.

When is a not-found result actually the right answer?

When the value genuinely is not in the source. Microsoft defines #N/A as indicating that a formula could not find what it was asked to look for, and in a healthy model that is frequently the conclusion you wanted: a customer with no order, an item not in the catalogue, a month not yet closed.

The causes that do need fixing separate out quickly, per Microsoft:

• The lookup value and the source data are different data types - a number against text stored as text is the classic case.
• Leading or trailing spaces in the cells. TRIM solves it, but only once you have confirmed that is the cause.
• Approximate match instead of exact, meaning TRUE rather than FALSE in the match argument.
• An array formula referencing a range that does not have the same number of rows or columns as the range holding the formula.
• A value entered manually via NA() and never replaced with real data.
• A required function argument missing.

Why IFERROR is usually the wrong fix

Because it catches all three errors together and returns the same value for every one of them. A model wrapped in IFERROR shows zero for a customer that was not found, zero for a deleted reference, and zero for a formula broken by merged cells - with no way to tell them apart afterwards.

• If you only want to handle a not-found value, IFNA is narrower and leaves reference errors visible.
• Decide what the substitute value asserts. Zero and "not found" are two different statements in a report.
• Do not wrap before you have diagnosed. Wrapping is a presentation decision, not a diagnostic tool.

A recovery order that preserves the evidence

1. Stop. Do not save and do not keep editing a workbook that has just filled with reference errors.
2. Try an immediate undo. It is the only action that restores a deleted reference without rebuilding it.
3. If the file was already saved, restore a previous version into a new file rather than overwriting the current one.
4. Map the damage by type and count how many of each. The counts set the priority.
5. Fix the reference errors first, because every other repair depends on the structure being sound.
6. Then clear the spill ranges, and only afterwards work on the lookup values.
7. Leave any not-found result that is not really an error visible, or replace it with explicit text such as "not found".

That order is not arbitrary. Repairing lookup values before repairing references creates double work, because every formula you fix breaks again the moment you restore the structure underneath it.

Preventing the next round

• Replace individual cell references with range references or named tables. Deleting a row inside a range does not break it the way it breaks a list of individual cells.
• Separate the input sheet from the calculation sheet. Deletions almost always happen on the sheet people edit by hand.
• Do not use merged cells anywhere formulas write into. It is the quietest cause of spill errors there is.
• Confirm the file has active version history. Without it, an undo is the only chance you get.

Related: Concurrent spreadsheet edits and conflict control, When you have outgrown spreadsheets, A method for consolidating dozens of Excel sheets.

Sources

• Microsoft - How to correct a #SPILL! error
• Microsoft - How to correct a #REF! error
• Microsoft - How to correct a #N/A error