Topic 12

Excel Errors

Errors in Excel are not failures; they are helpful diagnostics. Excel displays a specific error code to tell you exactly what is wrong with your calculation or data. Understanding what these codes mean allows you to fix them in seconds.

10 common errors No practice file needed

The Error Catalog

Here are the 10 most common errors you will encounter in Excel formulas, what causes them, and how to resolve them.

🌊

#SPILL!

Spill Area Blocked

Occurs in dynamic array formulas (like FILTER, SORT, or UNIQUE) when the grid area where the formula needs to display its results (the "spill range") contains other data, or when the formula is placed inside an Excel Table.

Common Causes

• Cell in the output path contains text or numbers.
• Formula is written inside a structured Excel Table (Tables do not support spilling).

How to Fix

• Clear the blocked cells in the highlighted spill area.
• Move the formula outside of the structured Excel Table.

Example — spill range blocked by text in A3:

= SORT(B1:B10) ← Returns #SPILL! because cell A3 has a value blocking the array

🔢

#VALUE!

Wrong Data Type

Excel returns this error when a formula expects one type of data (e.g. a number) but receives a different type (e.g. text). It is a mismatch of values.

Common Causes

• Adding, subtracting, or multiplying text cells.
• Passing text into a math function expecting numbers.

How to Fix

• Check cell values for hidden characters or spaces.
• Use functions like SUM instead of operators (+) because SUM ignores text cells.

Example — adding text and number:

= A1 + B1 ← Returns #VALUE! if A1 is "Pending" and B1 is 150

🚫

#DIV/0!

Division by Zero

Excel displays this error when a formula attempts to divide a number by zero (0) or by an empty cell.

Common Causes

• Divider cell is 0 or completely blank.
• AVERAGE function applied to a range containing no numbers.

How to Fix

• Wrap your division formula in `IFERROR`. E.g. =IFERROR(A1/B1, 0).
• Check that divisor cell is not empty.

Example — division by blank cell:

= A1 / B1 ← Returns #DIV/0! if B1 is 0 or empty

❓

#NAME?

Unrecognized Text / Function Name

This error means Excel does not recognize something in your formula. It is usually a spelling mistake in a function name or range name, or missing quotation marks around text.

Common Causes

• Misspelling a function name (e.g. VLOOKP instead of VLOOKUP).
• Forgetting quotation marks around a text value (Excel thinks it is a named range).

How to Fix

• Double-check spelling of function names.
• Use Formula Autocomplete to let Excel fill function names.
• Enclose text strings in double quotes (e.g. "Doha").

Example — misspelled name and missing quotes:

= VLOOKP(A1, B1:C10, 2, FALSE) ← Returns #NAME? (misspelled function)
= IF(A1 = Active, 1, 0) ← Returns #NAME? (missing quotes around "Active")

♾️

#NUM!

Invalid Numeric Values

Occurs when a formula contains numeric values that are invalid, or when a mathematical calculation results in an impossible number.

Common Causes

• Performing calculations on values that are too large/small for Excel (Excel supports values between 10^-307 and 10^307).
• Impossible operations, like taking the square root of a negative number.

How to Fix

• Check formula arguments for negative values where positive ones are required.
• Verify that numerical calculations do not exceed Excel limits.

Example — square root of a negative value:

= SQRT(-16) ← Returns #NUM! (square root of a negative number is mathematically impossible)

🔍

#N/A

Value Not Available

Short for "Not Available". This is the standard lookup error. It means the lookup formula could not locate the search value in the source table.

Common Causes

• The lookup value does not exist in the referenced column.
• Extra hidden spaces or spelling discrepancies between the lookup value and source table.

How to Fix

• Double check the spelling of your lookup value.
• Wrap lookups in IFERROR or use XLOOKUP's built-in [if_not_found] argument.

Example — lookup value missing:

= VLOOKUP("Orange", A1:B10, 2, FALSE) ← Returns #N/A if "Orange" is not in range A1:A10

🔲

#NULL!

No Intersection / Missing Operator

Occurs when you use a space (the intersection operator) between two cell ranges that do not actually intersect.

Common Causes

• Forgetting a comma (,) or colon (:) between two ranges.
• A space is typed where a comma should be (e.g. A1:A5 B1:B5).

How to Fix

• Check range separators: use colons for ranges (A1:B10) and commas to separate arguments.

Example — space instead of comma:

= SUM(A1:A5 B1:B5) ← Returns #NULL! because there is no overlap and no separator

🗑️

#REF!

Reference was Deleted

One of the most common and dangerous errors. It means a cell, column, row, or worksheet referenced by your formula has been deleted, leaving Excel with nothing to point to.

Common Causes

• Deleting columns or rows containing cells used in a formula.
• Deleting a worksheet referred to by other sheets.
• Copying a formula with relative reference to a location that pushes references off the grid limits.

How to Fix

• Undo the delete immediately (Ctrl+Z) if possible.
• Rewrite the formula referencing valid, existing cells.
• Avoid deleting entire rows/columns; hide them or clear contents instead.

Example — referenced cell deleted:

= A1 + #REF! ← Originally A1+B1, but column B was deleted

⚙️

#CALC!

Calculation Error / Empty Arrays

A modern error code specific to Excel 365. It occurs when Excel's calculation engine encounters a problem that it cannot resolve, particularly when an array calculation returns an empty set.

Common Causes

• A FILTER formula returns 0 matches, resulting in an empty array.
• Calculations referencing nested arrays that Excel cannot process.

How to Fix

• Provide an optional default argument for array formulas. E.g. in FILTER, set the [if_empty] argument to "No results".

Example — FILTER with no matching results:

= FILTER(A1:B10, A1:A10 = "Unavailable") ← Returns #CALC! if "Unavailable" is never found
= FILTER(A1:B10, A1:A10 = "Unavailable", "No results") ← Returns "No results" safely

🔄

Circular Reference

Infinite Formula Loop

A Circular Reference occurs when a formula directly or indirectly refers to its own cell. This creates an infinite loop where the calculation needs its own result to compute itself.

Common Causes

• Summing a column and writing the SUM formula inside that same column range (e.g. writing =SUM(A1:A10) inside cell A10).

How to Fix

• Move the formula outside the range it calculates.
• Find the circular cell using Formulas tab → Error Checking → Circular References drop-down.

Example — writing formula inside its own sum range:

Cell A10 contains: = SUM(A1:A10) ← Excel alerts you to a circular reference and returns 0

Quick Diagnostic Summary

Use this table as a quick reference guide to identify and fix Excel errors on the fly.

Error	Diagnostic Meaning	Common Scenario	Quick Fix
#SPILL!	Array blocked by data	Dynamic range overlaps existing cell entries	Delete the cells blocking the spill range
#VALUE!	Incorrect data type	`="Hello" + 10`	Verify numbers aren't stored as text
#DIV/0!	Divided by 0 or blank	`=A1 / B1` where B1 is empty or 0	Use `IFERROR` to output 0 or blank
#NAME?	Unrecognized text	Misspelling formula or omitting double quotes	Check function spelling and add quotes to text
#NUM!	Invalid numeric value	Calculating square root of a negative value	Correct numeric inputs to valid ranges
#N/A	Value not found	Lookup value doesn't exist in source table	Fix spelling of search key or use `IFERROR`
#NULL!	Invalid range syntax	Missing comma or colon between range names	Replace space operators with commas or colons
#REF!	Reference deleted	Source rows or columns deleted	Undo deletion or adjust formulas manually
#CALC!	Array calculation failure	`FILTER` finds no matching data rows	Add the optional fallback argument to `FILTER`
Circular	Formula loops back	SUM range contains the formula cell itself	Move formula to a cell outside the range

Interactive Scenario Review

Test your knowledge! Read each scenario and type in the exact Excel error code that would result (e.g. #REF!, #N/A, #VALUE!, or Circular). The hash mark (#) is optional in your answer.

Identify the Excel Error for each scenario

Formula Scenario Description	Resulting Error
You delete column C which was directly referenced by a formula: `=B1+C1`.
You perform lookup: `=VLOOKUP("Toyota", cars, 2, FALSE)`, but "Toyota" is not in the list.
You write formula: `=SUM(A1:A5 B1:B5)` but forgot a comma between the arguments.
You attempt math on a text string: `="Overdue" + 150`.
You enter `=SUM(A1:A10)` inside cell `A5`.
You divide sales by units: `=C2/D2`, where D2 is blank.
You misspelt standard function: `=AVERGE(A1:A15)`.
A `FILTER` formula returns empty array because no data matches the criteria.
A `UNIQUE` formula has 5 output rows, but there is already text in the cells below it.
You try to find square root of negative number: `=SQRT(-100)`.

Topic 11 — Protection

Project 01 — Party Supply