Prompt: Explain a Spreadsheet Formula
Explains what an existing Excel or Sheets formula does, one function at a time, plus the one edge case most likely to break it on real data.
The prompt
You are a spreadsheet specialist who explains formulas clearly to someone who didn't write them.
Objective: Explain what the following formula does: {{PASTE THE FORMULA}}
Context: This formula is used in {{SHEET CONTEXT — e.g. a monthly finance report, referencing an Orders table}}. The reader's spreadsheet level: {{READER LEVEL — e.g. comfortable with basic formulas like SUM and IF, new to array formulas}}.
Constraints:
- Break the formula down function by function, in the order they execute (innermost first), not just paraphrasing the whole thing in one go
- Use plain language pitched at the stated reader level; do not assume knowledge of a function not already implied by the reader level
- Do not guess at what a referenced range or named range contains beyond what's stated in the context; say so explicitly if it's unclear
- Identify exactly one edge case (e.g. a blank cell, a text value where a number is expected, a zero) most likely to make this formula error or return a wrong result
Example: for a nested IF inside a SUM, explain the innermost IF first, then how SUM aggregates its results.
Output format: Return a numbered breakdown of each function used, followed by a separate "Watch out for" section naming the one edge case.
Before finishing, check that the breakdown order matches execution order (innermost first) and that exactly one edge case has been identified, not several.
Example
Input
PASTE THE FORMULA: =IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found") SHEET CONTEXT — e.g. a monthly finance report, referencing an Orders table: an order-processing sheet, looking up a product price from a separate Products tab READER LEVEL — e.g. comfortable with basic formulas like SUM and IF, new to array formulas: comfortable with basic formulas like SUM and IF, new to VLOOKUP
Output
1. `VLOOKUP(A2, Products!A:C, 3, FALSE)` looks up the value in cell A2 (the product code on this sheet) within the first column of the range Products!A:C, and if it finds an exact match, returns the value from the third column of that same row — in this context, the product's price. 2. `IFERROR(..., "Not found")` wraps the VLOOKUP: if the lookup above returns an error (most commonly #N/A, which VLOOKUP shows when it can't find a match), the cell displays the text "Not found" instead of an error code, so the sheet stays readable. **Watch out for** If a product code has an extra space before or after it (for example "P-104 " in column A but "P-104" in the Products tab), VLOOKUP with FALSE (an exact match) treats them as different values and shows "Not found" even though the product genuinely exists — stray spaces from pasted or imported data are the most common cause of unexpected "Not found" results with this formula.
Why it works
- Breaking the formula down function by function, innermost first, matches how the formula actually executes rather than a vague paraphrase of the whole thing.
- Pitching the explanation at the stated reader level stops the answer assuming knowledge (or re-explaining basics) that don't match who's actually reading it.
- Naming exactly one concrete edge case turns a passive explanation into something the reader can proactively check for in their own data.
Why the basic version fails
“What does this formula do?” without the reader’s level or the formula’s context often returns either a single dense paraphrase that’s hard to follow function by function, or an explanation pitched at the wrong level — too basic for an experienced analyst, or assuming knowledge a newer team member doesn’t have yet.
What this prompt fixes
Breaking the explanation down by function, in execution order, mirrors how the formula actually runs rather than describing the end result in one breath. Pitching the language at the stated reader level means a newcomer gets VLOOKUP explained plainly, while an experienced analyst isn’t told what SUM does. The single named edge case turns a passive explanation into a specific, checkable thing to look for in the real data.
Tips per model
- ChatGPT is good at breaking formulas down clearly once the nesting is explicit; paste the exact formula rather than describing it from memory.
- Claude tends to be more precise about what a referenced range or named range actually contains, and will flag when that’s genuinely unclear from context.
- If the formula references a named range or another tab, mention what that range or tab contains — without it, the explanation has to guess at the data structure.