A silent miscalculation is worse than a visible error. #REF! at least shouts. A formula that returns a plausible-but-wrong total sails straight into the board deck. A good diagnosis prompt does what you would do by hand: it reads the error code, walks the dependency chain, isolates the one bad cell, and proposes a fix you can verify before you trust it.
These 12 prompts are model-agnostic — paste them into ChatGPT, Claude, or Gemini, or into an in-sheet assistant. As of June 2026 the three assistants that actually live inside the grid are Claude for Excel (add-in, included with Claude Pro at $20/month, runs Claude Opus 4.7, gives cell-level citations and preserves every dependency), Microsoft Copilot in Excel (best at explaining and debugging an existing formula), and Google Gemini in Sheets (free, a sidebar that now explains why a formula failed, not just how to write one). Pick whichever sits next to your data; the prompts below work in any of them.
TL;DR
- Read the error code first —
#REF!,#N/A,#VALUE!,#SPILL!, and#CALC!each have a distinct root cause and a distinct fix (table below). - Never let the AI “just fix it.” Ask it to diagnose, propose, and tell you what to check — then verify against the source data yourself.
- Pair the prompt with Excel’s built-in auditing: Trace Precedents (
Ctrl+[), Trace Dependents (Ctrl+]), and Evaluate Formula under Formulas → Formula Auditing. - Save a backup copy before any cascade fix. A
#REF!storm can be one bad undo away from worse. - The single most common real cause of
#N/Ais not a missing value — it is a trailing space or a number stored as text.
Error codes at a glance
| Error | What it means | Most common real cause | First fix to try |
|---|---|---|---|
#REF! | Reference no longer valid | A row, column, or sheet feeding the formula was deleted | Undo, or rebuild the reference from Trace Precedents |
#N/A | Value not found | Trailing space, or a number stored as text in the lookup key | TRIM() the keys; coerce types with VALUE() / *1 |
#VALUE! | Wrong data type in an argument | Text where a number is expected, or a stray space | Check each argument’s type; use ISTEXT() to find the culprit |
#SPILL! | Dynamic array can’t spill | Something is blocking the spill range, or it overlaps the input | Clear the blocking cells, or move the formula off its own input range |
#CALC! | Array calculation failed | Empty array result or unsupported nested array | Add a fallback value; simplify the nested array |
#DIV/0! | Division by zero or blank | Denominator cell is empty during a refresh | Wrap in IFERROR() only after confirming the blank is expected |
#NAME? | Excel doesn’t recognize a name | Misspelled function or a deleted named range | Check spelling; Formulas → Name Manager |
#NUM! | Invalid numeric result | Too-large result or impossible math (negative SQRT) | Bound the input range |
(Sources: Microsoft Support, Exceljet.)
Best for
#REF!/#N/A/#VALUE!/#SPILL!triage- Circular reference resolution
- Silent miscalculation hunts before a deadline
- Date and number-format mismatches
- VLOOKUP / XLOOKUP key mismatches
12 copy-ready prompt templates
Swap the bracketed [placeholders] for your real values. Keep the instruction “Don’t fix yet” on diagnostic prompts so the model explains before it edits — that is where it earns your trust.
1. Read the error code
Cell [cell, e.g. F12] shows [error, e.g. #N/A]. Diagnose:
(1) the most likely cause for THIS error code,
(2) the first thing I should check,
(3) one cheap fix to try,
(4) when to escalate to a rebuild.
Don't fix yet — explain first.Swap: cell, error code
2. Trace dependencies
Cell [cell] is wrong. Walk me through tracing it in Excel:
(1) which cells feed it (precedents — I'll use Ctrl+[ ),
(2) which cells depend on it (dependents at risk — Ctrl+] ),
(3) where in the chain the value most likely broke.
Tell me what each Trace Precedents arrow color means.Swap: cell
3. Circular reference unwinding
My workbook reports a circular reference. Walk through:
(1) finding the cells (Formulas > Error Checking > Circular References),
(2) describing the logical loop in plain English,
(3) breaking it — when to use iterative calculation
(File > Options > Formulas) vs. an intermediate helper cell
vs. refactoring the formula. Output a step-by-step plan.4. Silent miscalculation check
A KPI cell [cell] shows a plausible but suspicious number.
Walk me through verification:
(1) sanity-check the inputs (SUM, COUNT, MIN, MAX),
(2) compare to the prior period,
(3) manually compute one row by hand,
(4) compare that to the formula's output.
Name the exact discrepancy and where it enters.Swap: cell
5. Date format diagnosis
My date math returns wrong results. Diagnose these causes in order:
(1) dates stored as text (left-aligned, ISTEXT = TRUE),
(2) locale mismatch (DD/MM vs MM/DD),
(3) mixed types in the column,
(4) two-digit years rolling to the wrong century.
For each: how to detect it, and the exact fix.6. Lookup mismatch
[VLOOKUP or XLOOKUP] returns #N/A but the value is visibly in the table.
Diagnose, ordered cheapest fix first:
(1) trailing/leading spaces (test with TRIM),
(2) number stored as text vs. real number,
(3) exact vs. approximate match argument,
(4) the lookup key isn't in the FIRST column (VLOOKUP only).
Give me the corrected formula for the most likely cause.Swap: function name
7. Pivot table refresh fails
My pivot table refresh errored. Diagnose:
(1) source range moved or shrank,
(2) new columns inserted into the source,
(3) a calculated field references a renamed/missing column,
(4) a filter references a field that was removed.
Tell me how to repoint the source to a Table (Insert > Table) so this stops recurring.8. Power Query / external data error
A Power Query / external data refresh failed with: [paste the exact error message].
Walk through the likely causes in order:
source path moved, credentials expired, schema/column renamed,
data-type change, or API rate limit.
Output the single most likely cause + the exact fix step.Swap: error message
9. #SPILL! and recalculation cascade
[Cell shows #SPILL! / a small edit caused a cascade of #REF! and #N/A].
Diagnose:
(1) is the spill range blocked by existing content,
(2) does the dynamic array overlap its own input range,
(3) was a row/column deleted, or a named range broken,
(4) did a chart or pivot lose its source.
Output an isolation plan that fixes the source, not the symptoms.Swap: the bracketed scenario
10. Fix-in-place vs. rebuild decision
This sheet has [N] errors and unclear logic.
Decide for me:
(a) fix in place (estimate the hours),
(b) rebuild from clean inputs (more time, cleaner result).
Use these criteria: who owns it, how often it runs,
how readable the logic is, and the cost of it breaking again.
Recommend one with a one-line reason.Swap: error count
11. Pre-deadline triage
I have 30 minutes before I send this. Triage into three buckets:
(1) errors a stakeholder will SEE (visible #REF!, #N/A),
(2) hidden errors that corrupt the headline totals,
(3) items I can safely mark "TBD" and follow up on.
Output a prioritized fix order, totals first.12. Post-mortem on a sheet failure
My sheet showed a wrong number during a review.
Write a 200-word post-mortem:
(1) what went wrong (specific cell/formula),
(2) why the existing checks didn't catch it,
(3) ONE concrete process change
(peer review of inputs, separating inputs from calcs,
adding a reconciliation row, version control).How to get a fix you can actually trust
- Make the model diagnose before it edits. “Don’t fix yet” forces a cause, not a guess.
- Ask for the discrepancy in numbers, not adjectives — “the total is off by 4,200, traceable to row 47” beats “looks slightly off.”
- Verify against source data yourself. AI invents plausible figures when you hand it soft inputs; it cannot see your real cells unless you paste them or use an in-sheet add-in.
- Use an in-grid assistant for dependency-heavy models. Claude for Excel reads the whole workbook and cites the exact cells behind each claim, which matters when a fix in one tab silently breaks another.
- Re-run the formula auditing tools after the fix — Trace Dependents (
Ctrl+]) shows everything downstream that your change just touched.
Which AI tool for which job (June 2026)
| Tool | Where it runs | Best at | Cost |
|---|---|---|---|
| Claude for Excel | Excel add-in (Opus 4.7) | Auditing multi-tab models, cell-level citations, preserving dependencies | Included with Claude Pro, $20/mo |
| Microsoft Copilot in Excel | Inside Excel | Explaining and debugging one formula, plain-English walkthroughs | M365 Copilot add-on |
| Google Gemini in Sheets | Sidebar in Sheets | Free formula explanation + failure diagnosis, XLOOKUP/LAMBDA | Free in Google AI Pro / Sheets |
| ChatGPT / Claude (chat) | Browser | Pasting a formula + error for a quick second opinion | Free tiers available |
FAQ
- Can the AI see my actual spreadsheet?: Only an in-grid add-in (Claude for Excel, Copilot, Gemini in Sheets) reads your real cells. In a chat window you must paste the formula, the error, and a few sample rows — otherwise the model is guessing.
- What is the #1 cause of #N/A when the value is clearly there?: A trailing space or a number stored as text in the lookup key. Wrap the keys in
TRIM()and coerce types before you suspect anything fancier. - How do I stop #REF! from coming back after I fix it?: Convert your data to a Table (
Ctrl+T) and use structured references. Deleting rows then no longer poisons the formulas that point at the range. - Should I just wrap everything in IFERROR?: No.
IFERROR()hides the symptom, not the cause, and can mask a real miscalculation. Use it only after you have confirmed the error is expected (e.g., a blank denominator that should read 0). - Is Claude for Excel worth $20/month over free Gemini?: For one-off formula help, free Gemini in Sheets is plenty. For auditing multi-tab financial models where dependencies matter, Claude for Excel’s whole-workbook reading and cell-level citations earn the price.
- Can AI generate the underlying data itself?: No. It will invent plausible numbers if asked. Connect it to your real source data and treat every figure it produces as a draft to verify.
Related
- Excel analysis prompts
- Process improvement prompts
- SOP drafting prompts
- Handover document prompts
- Productivity & Office Prompts hub
Tags: #Prompt #Productivity #Excel #Debugging