Excel Formula Errors: Find the Cause Before Hiding It

To fix an Excel formula error, start with the exact error value, then check the inputs and cell references that produced it. A missing lookup key, a deleted cell, and a blocked spill range need different fixes. Wrapping the formula in IFERROR can hide the message, but it doesn't repair any of those causes.

A number on screen isn't proof that a formula is right, either. A total can return 70 when you meant 60, with no error message at all. We'll work through both kinds of problem using separate input and result cells.

What does the error value tell you?

These six errors give you a useful place to start. The first check is a clue, not a complete diagnosis.

These aren't all the errors Excel can return. Microsoft's guide to detecting formula errors covers additional errors and checking tools.

A circular reference is a different problem: a formula depends on its own result, directly or through other cells. If Excel warns about a loop, follow the steps to find and fix circular references.

Set up separate inputs and corrections

Create a sheet called Inputs and enter this small product list in A1:C4:

In Inputs!E1, enter Selected amounts. Put 10, 20, 30, and 10 in E2:E5. The last 10, in E5, is unrelated to the three amounts we want to total.

Create a second sheet called Cases, with these headers in A1:E1:

Use rows 2–7 for the six errors, leave row 8 available for the spill result, and use row 9 for the incorrect total. Format column A as Text before entering the error labels. Keep the examples in column C as text: type an apostrophe before the equals sign when entering them. That records the problem without asking Excel to calculate it. Enter the corrected formulas normally in column D.

Download the corrected examples workbook to follow along. It keeps the bad formulas as text in column C and calculates the corrected examples in column D, with expected results beside them in column E.

The screenshots use a separate demonstration copy: column D shows the failing calculation where an error is being illustrated. Column E holds expected results for comparison.

The formulas below use English function names and commas between arguments. Your regional settings may require semicolons instead. The spill example needs an Excel version that supports FILTER and dynamic arrays.

#N/A: check whether the lookup key exists

Enter #N/A as the case label in Cases!A2 and P-199 in B2. Store this bad-formula example as text in C2:

=VLOOKUP(B2,Inputs!$A$2:$C$4,3,FALSE)

If you calculate that formula, it returns #N/A. The final argument, FALSE, asks for an exact match. P-199 isn't in Inputs!A2:A4, so there's no price to return.

Check the key against the source list before changing the formula. A mistyped SKU should be corrected; a genuinely missing product needs a decision about the source data. Don't substitute an unrelated SKU just to make the error disappear.

To check that the lookup itself works, enter this known-key test in Cases!D2:

=VLOOKUP(Inputs!A2,Inputs!$A$2:$C$4,3,FALSE)

It looks up P-101 and returns 4, its price from the third column. Put 4 in E2 as the expected result. This confirms the lookup works for an existing key; it doesn't supply a missing price for P-199.

The VLOOKUP reference explains the exact-match argument and column numbering. If your workbook uses XLOOKUP, the focused guide to common XLOOKUP errors covers its lookup and return ranges.

#VALUE!: inspect the stored input

Put #VALUE! in Cases!A3 and the word ten in B3. Store this example as text in C3:

=B3+Inputs!C2

The formula tries to add the word ten to the number 4. Excel can't use that word as a number in this calculation.

Select the input and inspect what is actually stored. Changing its display format to Number won't turn ten into 10. Correct the value from the source record, or point the calculation to the intended numeric input.

For this arithmetic demonstration, enter the following in Cases!D3:

=Inputs!B2+Inputs!C2

The result is 14, because the formula now adds numeric 10 and numeric 4. Enter 14 in E3. This is a small input-type check; adding units to price is not an order-total calculation.

You can also repair the input itself. If B3 is the intended source, replace the word ten with numeric 10 and keep =B3+Inputs!C2 unchanged. The correction screenshot later in this article shows that approach. The corrected example formula above takes the other route: it reads the existing numeric value from Inputs!B2.

#REF!: restore the missing reference

Put #REF! in Cases!A4. Store this broken-reference example as text in C4:

=Inputs!#REF!+Inputs!C2

The #REF! inside the formula marks a reference Excel can no longer use. This can happen after a source cell or sheet is deleted.

If the deletion was accidental and recent, undo it. Otherwise, identify the intended source from the worksheet layout or an earlier copy before editing the formula. Replacing #REF! with a convenient cell may remove the error and still give you the wrong answer.

Here the intended first input is Inputs!B2. Enter this correction in Cases!D4:

=Inputs!B2+Inputs!C2

The result is 14; enter 14 in E4. The correction restores the intended reference. Hiding the error would leave that reference broken.

#DIV/0!: check the denominator

Put #DIV/0! in Cases!A5 and denominator was zero in B5. Store this example as text in C5:

=Inputs!B2/0

Excel can't divide 10 by zero. Inspect the value after the division sign, including any formula that produces it. A denominator that points to an empty cell can cause the same error.

Suppose this example is meant to divide the quantity by itself. The denominator should be Inputs!B2, which contains 10. Enter this in Cases!D5:

=Inputs!B2/Inputs!B2

The result is 1; enter 1 in E5. Don't replace every zero with 1 to silence the error. Use the intended denominator. If zero is a valid business case, decide what the report should show when the ratio can't be calculated, such as a clear message or an intentionally blank result.

#NAME?: check the spelling before changing the logic

Put #NAME? in Cases!A6. Store this example as text in C6:

=SUMM(Inputs!E2:E4)

SUMM is an intentional misspelling. Excel's addition function is SUM. Correct the name in Cases!D6:

=SUM(Inputs!E2:E4)

The result is 60: 10 + 20 + 30. Enter 60 in E6.

If the function spelling is right, inspect any named ranges used by the formula. Check that each name exists and refers to the intended cells. The error points to an unrecognized name; it doesn't tell you which replacement makes sense for your worksheet.

#SPILL!: make room for the results

Put #SPILL! in Cases!A7 and a cell blocks the intended spill range in B7. Store this formula as text in C7, then enter the same formula normally in D7:

=FILTER(Inputs!A2:A4,Inputs!B2:B4>4)

FILTER returns the SKUs whose units are greater than 4. Two rows qualify, so the result needs two cells: Cases!D7 shows P-101 and D8 shows P-102. Keep D8 empty before entering the formula; don't type the second result yourself. For comparison, enter the expected values in E7:E8.

If D8 already contains an entry, the formula can't place its second result there and returns #SPILL!.

Select D7 to inspect the intended spill area. Where available, the error alert's Select Obstructing Cells command identifies the obstruction. Move the entry if you need to keep it, or clear it if it is disposable. The formula can then fill both cells without a change to its logic.

The next screenshot shows the restored spill alongside the input correction from the earlier example. B3 now contains numeric 10, so the unchanged =B3+Inputs!C2 in D3 returns 14. The missing-key lookup in D2 still shows #N/A; correcting the arithmetic and clearing the spill area don't resolve that separate problem.

Blocked cells are one cause of this error. Merged cells, a formula inside an Excel table, or output extending beyond the worksheet can also prevent a spill. Microsoft's #SPILL! troubleshooting guide explains those cases.

No error, wrong answer: check the range boundaries

Put Wrong total in Cases!A9. Store this example as text in C9:

=SUM(Inputs!E2:E5)

If calculated, it returns 70. Excel has added all four numbers correctly, including the unrelated 10 in Inputs!E5. The mistake is in the selection of cells, so there is no Excel error value to report.

Our intended total includes only E2:E4. Enter this in Cases!D9:

=SUM(Inputs!E2:E4)

The result is 60; enter 60 in E9. Compare the formula's first and last references with the rows you actually intend to include. A valid formula can answer the wrong question, and IFERROR won't detect that.

Use Excel's checking tools to follow the calculation

Select the problem cell and inspect the formula bar first. Look for the exact references, the function arguments, and any error already present in an input cell.

On desktop versions that provide them, look on the Formulas tab for Error Checking and Evaluate Formula. Error Checking helps you inspect a flagged cell. Evaluate Formula lets you follow parts of a calculation to see where a value or error enters it. Availability and menu layout vary by platform and build.

If those commands aren't available, inspect each referenced cell directly. For a long formula, work through one part at a time in unused cells on a copy of the workbook. Keep the original formula available for comparison.

Add IFERROR only after you understand the failure

IFERROR returns a fallback when the expression it wraps evaluates to an error. It doesn't identify the cause, repair a reference, or confirm that the result is correct.

After checking the lookup, you could use this formula in an unused cell such as Cases!D11:

=IFERROR(VLOOKUP(Inputs!A2,Inputs!$A$2:$C$4,3,FALSE),"Check SKU")

The known SKU P-101 returns 4, so the fallback isn't used. If the wrapped lookup returns an error, the displayed result becomes Check SKU.

That message is a prompt to investigate, not a diagnosis. IFERROR catches several error types, including a broken reference as well as a missing lookup match, as Microsoft's IFERROR documentation explains. A reference problem could therefore produce the same message as a missing SKU.

Use a fallback only when its meaning is clear. Returning zero may make a missing price look like a free item; returning empty text may make an unfinished calculation look complete. If a fallback appears unexpectedly, inspect the underlying formula without the wrapper in a spare cell.

Quick diagnostic checklist

• Record the exact error value and the cell containing it.
• Inspect the formula bar and the referenced input cells.
• For a lookup, confirm that the key exists in the intended range.
• For arithmetic, check the stored values and the denominator.
• Restore or repoint invalid references using the intended source.
• Check function spelling and any defined names.
• Give a spilled result enough empty space.
• Compare range boundaries with the rows you meant to include.
• Check a corrected result against a value you can calculate independently.
• Add a fallback only after deciding what an expected failure should mean to the reader.