U Love Office
Home › Blog › Excel & Data
Excel & Data

How to fix common Excel errors: #SPILL!, #N/A, #VALUE! and #REF!

✍️ admin 📅 22 November 2026

Quick answer: Each Excel error tells you what went wrong. #SPILL! means a dynamic array formula cannot output its results because cells in the way are not empty. #N/A means a lookup found no match. #VALUE! means a formula received the wrong type of data, often text instead of a number. #REF! means a formula refers to cells that were deleted. #DIV/0! means division by zero or a blank cell. #NAME? means Excel does not recognise a function or name, usually a typo. #NUM! means an impossible calculation. Click the error cell, use the small warning icon or Formulas > Evaluate Formula, find the cause and fix the data or formula, rather than hiding the error.

Errors are not failures; they are messages. Learning to read them turns a frustrating spreadsheet into a fixable one. This guide covers the errors office workers meet most often, with real causes and fixes.

Table of common Excel errors, their meaning and most common cause
Read the error first; it usually points straight to the fix.

Tools for finding the cause

Before fixing, find the exact cause:

#SPILL!

Dynamic array functions in Microsoft 365, such as FILTER, UNIQUE, SORT, SEQUENCE and TEXTSPLIT, return several results that “spill” into neighbouring cells. #SPILL! appears when they cannot.

Causes and fixes:

Excel example of a #SPILL! error caused by text blocking the spill range
Select Obstructing Cells finds the blocker instantly.

#N/A

#N/A means “not available” and usually comes from lookups: VLOOKUP, XLOOKUP, MATCH, INDEX/MATCH.

Causes and fixes:

Showing a friendly result: once you know #N/A means “not found”, you can display something clearer:

Use IFNA rather than IFERROR here, so other errors still show.

#VALUE!

#VALUE! means a formula got the wrong kind of input.

Causes and fixes:

Excel example of a #VALUE! error caused by text in a price cell
Keep units in the number format, not in the cell value.

#REF!

#REF! means a formula points to a cell that no longer exists.

Causes and fixes:

Prevent it: use Excel Tables and structured references (Sales[Amount]), which adjust automatically, and avoid deleting columns that other sheets use. Lock important formula cells; see our guide on how to lock cells and protect formulas.

#DIV/0!

Division by zero or by an empty cell, common in percentage and average calculations.

Fixes:

Our guide on calculating percentages in Excel has more examples.

Office worker frustrated by a spreadsheet error on a laptop

#NAME?

Excel does not recognise something in the formula.

Causes and fixes:

#NUM!

Causes: impossible maths (square root of a negative number), an iterative function that cannot find a result (such as IRR with unusual cash flows), or a number too large for Excel. Check inputs and, for financial functions, try providing a guess value.

Hiding errors: when and how

IFERROR is useful in finished reports, but use it carefully. Wrapping every formula in IFERROR(…, 0) hides real problems: a #REF! from a deleted column becomes a zero, and your total is quietly wrong.

Better practice:

Worked example: fixing a broken monthly report

An accountant opens the monthly sales report and sees errors everywhere. Working through them in order:

  1. #REF! in the summary sheet: someone deleted a “Region” column from the data sheet. Undo was no longer possible, so the formulas were updated to the correct column.
  2. #N/A in the client lookup: three new clients were not yet in the client master list. Added them.
  3. #VALUE! in totals: the new month’s export had prices with “AED” in the cell. Removed with Find and Replace.
  4. #SPILL! in the unique client list: an old note was sitting in the output column. Deleted.

Fifteen minutes later the report was correct, and the accountant converted the data range into an Excel Table to prevent the #REF! problem next month.

Do and avoid list for handling Excel formula errors
Hiding an error doesn't fix the number behind it.

Preventing errors before they happen

Most errors come from the same few habits. A few settings and rules prevent them:

Checklist of habits that prevent Excel formula errors
Set these up once and most errors never appear.

When you inherit a workbook full of errors, fix them in dependency order: start with the data sheet, then calculation sheets, then reports. Errors flow downstream, so fixing the source often clears dozens of report errors at once.

Key takeaways

📘 Continue reading

Frequently asked questions

Why does my formula work on my colleague’s computer but show #NAME?
They probably have a newer Excel version with functions your version lacks.

How do I find all errors in a sheet?
Home > Find & Select > Go To Special > Formulas > Errors selects every error cell.

Can errors break a whole workbook?
Errors flow into every formula that depends on them. Fix the first error in the chain and the rest often disappear.

What does a green triangle mean?
It is a warning, not an error, such as a number stored as text or a formula that differs from its neighbours. Check it anyway.

Conclusion

Excel errors are clues. #SPILL! means clear the way, #N/A means check the match, #VALUE! means fix the data type, #REF! means repair the reference, #DIV/0! means handle zero. Use Evaluate Formula to find the cause, fix the data or formula, and hide errors only when you know why they appear. Your reports will be more accurate and much less stressful to maintain. If a file itself will not open, see our guide on how to repair a corrupted Excel file.

Last checked: 9 November 2026.