Silent miscalculations are worse than visible errors. A good diagnosis prompt walks dependencies, isolates the bad cell, and produces a fix you can trust.
Who this is for
Anyone who has watched a board-deck spreadsheet say #REF!, ops analysts on tight cadences, indie founders managing finance in Sheets.
When not to use these prompts
Don’t use these without saving a backup first. Don’t use them when the answer is “rebuild” — sometimes a bad sheet is past saving.
Prompt anatomy / structure formula
Every prompt should carry six elements:
- Role: who AI plays — analyst, chief of staff, manager.
- Context: team / org / project / data scope.
- Goal: one deliverable — table, doc, talking points, plan.
- Constraints: word count, must-include fields, audience seniority.
- Tone: confident, neutral, factual — depends on audience.
- Examples: 1-2 samples of prior work to anchor format.
Best for
- #REF! / #N/A / #VALUE! triage
- Circular reference resolution
- Silent miscalculation hunt
- Date / number format issues
- Lookup mismatch debugging
12 copy-ready prompt templates
1. Read the error code
Cell `{cell}` shows `{error}`. Diagnose: (1) most likely cause (table of typical causes per error code), (2) first thing to check, (3) one cheap fix to try, (4) when to escalate. Don't fix yet.Variables to swap: cell, error
2. Trace dependencies
Cell `{cell}` is wrong. Walk me through tracing precedents: (1) which cells feed it, (2) which cells it feeds (dependents at risk), (3) where the chain likely broke. Use Excel's Trace Precedents naming conventions.Variables to swap: cell
3. Circular reference unwinding
My workbook has a circular reference. Walk through: (1) finding which cells, (2) identifying the logical loop, (3) breaking it (iterative calculation? intermediate cell? formula refactor?). Output a plan.4. Silent miscalculation check
A KPI cell shows a plausible but suspicious number. Walk through verification: (1) Sanity-check inputs (sum, count), (2) Compare to prior period, (3) Manually compute one row, (4) Compare to formula. Identify the discrepancy.5. Date format diagnosis
My DATE function returns wrong results. Common causes: (1) Text-formatted cells, (2) Locale (DD/MM vs MM/DD), (3) Mixed types, (4) Two-digit year. For each: how to detect, how to fix. Walk through each.6. Lookup mismatch
VLOOKUP returns #N/A but the value visibly exists. Common causes: (1) Trailing spaces, (2) Number stored as text, (3) Case sensitivity (rare), (4) Approximate match expected. Walk through fixes ordered cheapest.7. Pivot table refresh fails
My pivot table refresh errored. Possible causes: (1) Source range moved, (2) New columns inserted, (3) Calculated field formula referencing missing column, (4) Filter referencing removed field. Walk through.8. Power Query / external data error
A Power Query / external data refresh failed with: `{errorMessage}`. Walk through: source path / credentials / schema change / API limit. Output likely cause + fix.Variables to swap: errorMessage
9. Recalculation cascade
A small change in cell A caused widespread cascade of #REF! / #N/A. Walk through: (1) Was it a deleted row / column, (2) Was a named range affected, (3) Did a chart break. Output an isolation plan.10. Audit broken-sheet rebuild decision
This sheet has 12 errors and unclear logic. Decide: (a) Fix in place (≤ 2 hours), (b) Rebuild from inputs (more time but cleaner). Cite criteria: clarity, ownership, future maintenance.11. Pre-deadline triage
I have 30 minutes before sending. Help me triage: (1) Errors visible to stakeholders, (2) Hidden errors that affect totals, (3) Items I can mark "TBD" and follow up. Output prioritised plan.12. Post-mortem on a sheet failure
My sheet failed during a board meeting. Write a 200-word post-mortem: (1) What went wrong, (2) Why it wasn't caught, (3) One process change (e.g., review by peer, separate inputs from calcs).Common mistakes
- No specific context — output is generic.
- Skipping fact-check — AI invents numbers when given soft inputs.
- Vague audience — output overshoots or undershoots seniority.
- No word limit — receivers won’t read past line 5.
- Same template for every situation — readers tune out.
- No “decision needed” framing — readers don’t know what to do.
- Forgetting to attach the source data — claims without receipts.
How to push results further
- Always specify audience level (IC / Manager / VP / CEO).
- Cap length: 1-page max for tactical, 3-bullet for executive.
- Lead with the ask / decision needed. Context after.
- Attach source data link — saves a follow-up email.
- Read aloud before sending; cut every sentence > 25 words.
- Use AI to draft and audit, not to ship without review.
- Save best examples; reuse format, refresh content.
Practical depth notes
Use these prompts as starting points, not final answers. For Spreadsheet Error Diagnosis Prompts for #REF! and #N/A, the useful extra work is to replace every generic placeholder with a real constraint: audience, channel, length, brand voice, examples to imitate, and examples to avoid. Run at least two versions with different constraints, then compare the outputs side by side instead of accepting the first polished response.
A good result should pass three checks: it is specific enough that another person could reuse it, it avoids vague praise or filler, and it gives you an editable artifact rather than a broad suggestion. If the output feels generic, add one concrete reference, one forbidden pattern, and one measurable success criterion before rerunning the prompt. Before saving a prompt as reusable, test it on one realistic input and one edge case. The realistic input proves the template can produce the normal deliverable; the edge case shows whether it handles messy constraints, missing context, or an unusual audience. Keep the better output, but also keep the failed version with a note on what was missing. That small failure log is what turns a prompt collection from a list of nice sentences into a practical working library.
FAQ
- How long should this doc be?: Match audience patience. Tactical: 1 page. Executive: 3 bullets + link.
- Can AI replace the analyst?: For first drafts and templates yes; for judgment calls no.
- How often refresh?: Cadence-driven (weekly / monthly / quarterly) but adjust when audience signals fatigue.
- Should I include risks?: Always. Pretending no risk exist erodes trust on the next update.
- How to keep it fact-checked?: Attach data sources and have a peer skim numbers before sending.
- Can AI generate the data itself?: No — AI invents plausible numbers. Connect to real data sources.
Related
- Excel analysis prompts
- Process improvement prompts
- SOP drafting prompts
- Handover document prompts
- Productivity & Office Prompts hub
Tags: #Prompt #Productivity #Excel #Debugging