Tracing one broken formula should take a few minutes, not an afternoon. Yet a single #REF! or #VALUE! can send you down a rabbit hole of clicking through ranges, checking sheet names, and undoing changes that made it worse. These five Claude prompts cut that time down: fill in the table, paste the error, and get a diagnosis you can act on.
Before you start: the variables
| [Variable] | What to fill in |
|---|---|
| [error message] | The exact error the cell shows, such as #REF!, #VALUE!, or #DIV/0!. |
| [formula] | The full formula from the problem cell. |
| [sheet name] | Which sheet the formula sits on, and any sheets it references. |
| [data range] | The range the formula reads from. |
| [app] | The tool you use, such as Excel, Google Sheets, or LibreOffice. |
The prompts
1. Decode the error message
This cell shows the error [error message] and contains this formula: [formula]. In [app], explain what this error actually means, then list the three most likely causes in order. Tell me how to confirm which one it is.What it does: Translates a cryptic error code into plain causes you can check in order.
How to use: replace [error message], [formula], and [app]; keep the rest as is.
2. Trace a broken reference
I get [error message] from this formula on the sheet [sheet name]: [formula]. It reads from [data range]. Walk me through the reference chain step by step and tell me where it breaks. Do not just hand me a fix — show the path first.What it does: Shows you where a reference goes wrong instead of only replacing the formula.
How to use: replace [error message], [sheet name], [formula], and [data range]; keep the rest as is.
3. Check for a circular reference
I suspect a circular reference on the sheet [sheet name]. Here is the formula: [formula]. It sits in [target cell]. Explain how to confirm the loop, and give me a version that breaks the cycle without changing the intended result.What it does: Finds the self-referencing loop that makes a cell refuse to calculate.
How to use: replace [sheet name], [formula], and [target cell]; keep the rest as is.
4. Repair a range that broke after an edit
After I edited the sheet, this formula stopped working: [formula]. It references [data range] on the sheet [sheet name]. Tell me what the edit most likely broke, then give a corrected formula that is less fragile and point out what to lock down.What it does: Fixes the damage from inserted or deleted rows and stops it from happening again.
How to use: replace [formula], [data range], and [sheet name]; keep the rest as is.
5. Replace a fragile formula
This formula keeps breaking in [app]: [formula]. It should produce the value in [target cell] from [data range]. Rewrite it so it survives sorting, new rows, and renamed [sheet name], and explain in one line why the new version is sturdier.What it does: Swaps a brittle formula for one that keeps working as the sheet changes.
How to use: replace [app], [formula], [target cell], [data range], and [sheet name]; keep the rest as is.
A quick worked example
Take prompt 1. Suppose [error message] is #VALUE!, [formula] is =A2+B2, and [app] is Excel. The model explains that #VALUE! appears when a math operator meets text, then lists the likely causes: a number stored as text, a stray space, or a cell that looks empty but holds a hidden character.
To confirm, it suggests wrapping the check in ISNUMBER(B2) and, if that returns FALSE, using =A2+VALUE(B2) or cleaning the cell with TRIM. You now know exactly which cell to inspect instead of guessing.
More free prompts for Excel & Spreadsheets are waiting in the GuPrompt library.
Keep going
- Explore more Excel & Spreadsheets prompts.
- Building formulas from scratch? Try ChatGPT prompts for Excel formulas.
- Testing what-if scenarios? See Gemini prompts for Goal Seek.
- Auditing a workbook before you ship it? Read Claude prompts for spreadsheet audits.