Excel Formula Error Manual: Fix #SPILL!, #VALUE!, XLOOKUP, Circular References, and Broken Links
Microsoft ExcelformulasXLOOKUPSPILL errorsspreadsheet troubleshootingMicrosoft 365

Excel Formula Error Manual: Fix #SPILL!, #VALUE!, XLOOKUP, Circular References, and Broken Links

MManuals Top Editorial Team
2026-11-15
7 min read

A practical Excel troubleshooting checklist for fixing #SPILL!, #VALUE!, XLOOKUP, circular references, broken links, and calculation errors safely.

Excel formula errors are usually symptoms of a specific problem: a blocked spill range, mismatched data type, missing lookup value, deleted reference, circular dependency, or outdated external link. This manual gives you a reusable, low-risk workflow for finding the cause, testing a repair, and confirming that the rest of the workbook still calculates correctly.

Overview

Before changing a formula, save a copy of the workbook with a new name. If the file is shared, confirm whether you are editing the source workbook or a downloaded copy. Record the affected sheet, cell, formula, and the first point at which the error appeared. These simple steps make it possible to undo a failed repair and distinguish a formula problem from an input or calculation problem.

Excel's error value is a clue, not a complete diagnosis. Start with the displayed error, then inspect the formula's inputs and references. A formula that returns #N/A may be working as written but unable to find a match. A formula that returns #VALUE! may contain text where a number or date is expected. A #REF! result usually means a referenced cell, row, column, or sheet was removed or replaced.

For a detailed walkthrough of lookup syntax and reusable examples, see the Excel XLOOKUP and Lookup Formulas Manual. Keep the original formula visible while testing a proposed change in a blank cell or a duplicate worksheet.

Checklist by scenario

Error or symptomLikely causeChecks and safe fixes
#SPILL!A dynamic-array formula cannot place all returned values in the intended range.Click the warning indicator and inspect the highlighted spill area. Remove values, formulas, merged cells, or other obstructions only after confirming they are not needed. A dynamic-array formula cannot spill into a structured Excel Table in the same way it spills into an ordinary range; consider placing the formula outside the table or redesigning the result. Also check whether the formula is returning an unexpectedly large result.
#VALUE!Incompatible data types, malformed arguments, hidden characters, or an operation performed on text.Inspect each input separately. Test numeric-looking text with VALUE where appropriate, remove unwanted spaces with TRIM, and check nonprinting characters with CLEAN. Confirm that dates are real Excel date values rather than text that merely looks like a date. Do not convert an entire column blindly; test a small sample first.
#N/A or XLOOKUP not workingThe lookup value is absent, formatted differently, or not exactly equal to the searched value.Compare the lookup value and lookup range character by character. Check leading or trailing spaces, numbers stored as text, inconsistent capitalization where relevant, and duplicate keys. For XLOOKUP, verify that the lookup and return arrays have compatible dimensions and that the match mode is intentional. Add a clear if_not_found message only after confirming that a missing match is an acceptable outcome.
#REF!A reference was deleted, moved in a way the formula could not preserve, or broken during an import or paste.Use the formula bar to locate each #REF! fragment. Restore the deleted range if possible, or rebuild the reference from a known-good source. Search the workbook for related formulas before replacing anything, because several reports may depend on the same damaged range.
#NAME?A misspelled function, named range, sheet reference, or unsupported function.Check spelling and punctuation. Confirm that text criteria are enclosed in quotation marks and sheet names containing spaces use single quotation marks. Review compatibility when a workbook moves between Microsoft 365, desktop Excel, Excel for the web, or an older version. Functions that rely on newer dynamic-array behavior may not calculate as expected in older installations.
Circular referenceA formula directly or indirectly refers back to its own result.Look for Excel's circular-reference notice and use Formulas > Error Checking > Circular References where available. Trace the dependency chain and separate the input calculation from the output calculation. Do not enable iterative calculation merely to hide the warning unless the workbook was deliberately designed for that method and the iteration settings are documented.
External-link warning or stale valuesA formula refers to another workbook that has moved, been renamed, or is unavailable.Use Data > Queries & Connections or the workbook's link-management controls, depending on your Excel version, to identify the source. Open the source only if it is trusted and expected. Choose whether to update, repoint, or permanently remove the link after checking the business purpose of the data. Never replace an external link with pasted values without recording the date and source.
Formula appears unchangedCalculation is set to manual, formulas are displayed, or the workbook has not recalculated.Check Formulas > Calculation Options and confirm whether Automatic is appropriate. Press F9 to recalculate, or use the full-recalculation commands when a workbook has dependencies that need rebuilding. If formulas are visible in every cell, turn off Show Formulas from the Formulas tab.

Step-by-step debugging workflow

  1. Preserve the evidence. Save a copy, note the original formula, and identify whether the error affects one cell, a row, a column, or an entire report.
  2. Inspect the inputs. Click each referenced cell and check its displayed value, underlying type, blank status, and formatting. Formatting can make a number look like a date or currency without changing the stored value.
  3. Test in a blank cell. Copy the formula or a smaller expression into an unused cell. For example, test the lookup value alone, then the lookup range, then the return range. This isolates the failing argument without altering the production report.
  4. Evaluate the formula. On desktop Excel, use Formulas > Evaluate Formula to step through nested calculations. Watch for the first intermediate value that changes into an error. If the command is unavailable in your version, simplify the formula temporarily by testing its inner functions separately.
  5. Trace dependencies. Use Trace Precedents to see which cells feed the formula and Trace Dependents to see what may be affected by a change. Remove the arrows when finished so they do not confuse later users.
  6. Make the smallest repair. Correct the source data or the narrowest broken reference rather than wrapping the entire formula in IFERROR. Error suppression can make a report look complete while hiding missing or invalid data.
  7. Recalculate and verify. Check the repaired cell, nearby formulas, totals, filters, charts, and downstream sheets. Compare a few known results with the previous version or an independent calculation.

What to double-check

Data types: A value that looks identical may not be identical to Excel. “00125” stored as text differs from 125 stored as a number. Imported dates, copied web data, nonbreaking spaces, and invisible characters are common causes of lookup and arithmetic failures.

References: Check whether a range should be relative, absolute, or mixed. A reference such as A1 changes when copied, while $A$1 remains fixed. Confirm that a copied formula still points to the intended sheet and row.

Dynamic arrays: Functions that return multiple results can change the occupied area when source data grows. Keep space available around spill formulas and avoid placing unrelated manual entries in their expected output range.

Compatibility: XLOOKUP and other newer functions may not be available or may behave differently in older Excel versions or other spreadsheet applications. When sharing a workbook, confirm the recipient's environment before replacing a formula with a workaround.

Links and queries: Imported data may refresh on a schedule or only when a user permits it. Record whether a result is live, cached, or manually pasted so a later user does not mistake an old value for a current one.

Common mistakes

  • Replacing every error with IFERROR before finding the underlying cause.
  • Deleting rows or columns to clear #SPILL! without checking what those cells contain.
  • Using approximate lookup matching when the report requires an exact key.
  • Fixing a single copied formula instead of repairing the source range or table column that generated the errors.
  • Changing calculation settings globally and forgetting to restore or document them.
  • Updating external links from an untrusted or incorrect file.
  • Testing directly in a live report without creating a backup or recording the original formula.

When to revisit

Use this checklist whenever imported data changes, a new formula is introduced, a shared workbook is reorganized, or a recurring report begins showing unexpected blanks or totals. Recheck it before seasonal planning cycles and after changes to templates, queries, named ranges, or source files. It is also worth reviewing after an Excel version change, especially when a workbook uses XLOOKUP, dynamic arrays, newer functions, or external connections.

For a practical final pass, duplicate the workbook, filter the affected report for error values, and work from the earliest dependency to the final output. Repair one issue at a time, recalculate after each change, and keep a short change log containing the original formula, the cause, the correction, and the verification performed. That habit turns an urgent Excel formula error into a repeatable troubleshooting process without risking the only copy of the workbook.

Related Topics

#Microsoft Excel#formulas#XLOOKUP#SPILL errors#spreadsheet troubleshooting#Microsoft 365
M

Manuals Top Editorial Team

Software Manuals Editor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.