Spreadsheet formula from a description
The formula, what each part does, and what breaks it. Written for any model, free to copy, with every placeholder explained below.
The prompt
ROLE
You write spreadsheet formulas and explain them so they can be maintained.
CONTEXT
Application: {{application}}
Sheet layout: {{layout}}
Data quirks: {{quirks}}
TASK
Write a formula that: {{what_you_want}}
CONSTRAINTS
1. Use only functions available in the stated application and version.
2. Explain what each part of the formula does, in order.
3. State what breaks it: empty cells, text where numbers are expected,
duplicates, or rows added below the range.
4. If a helper column would make it substantially simpler, say so and show both.
5. Do not use array formulas unless they are necessary; say why if you do.
OUTPUT FORMAT
1. The formula, on its own line.
2. What each part does.
3. What breaks it.
Now write the formula.Open this in the prompt builder to adapt it, score it, and re-render it for a different model.
What to replace
| Placeholder | What to put there |
|---|---|
| {{application}} | Excel 365, Excel 2019, Google Sheets — the functions differ. |
| {{layout}} | Which columns hold what, and where the data starts. |
| {{quirks}} | Blanks, text-formatted numbers, merged cells. |
| {{what_you_want}} | The calculation in plain English. |
Why this prompt works
- Version named
- XLOOKUP and LET do not exist in older Excel. Naming the version prevents a formula that cannot run.
- "What breaks it" required
- Spreadsheet formulas fail silently on empty cells and text-formatted numbers, producing a wrong number rather than an error.
- Helper column offered
- A readable two-step beats an unmaintainable one-liner, but only if the model is invited to suggest it.
More prompts
- Extract structured data from messy text — JSON that parses, with nulls where the source is silent instead of invented values.
- Classify items with a stated reason — Categorisation you can audit, with an explicit "unclear" option instead of forced guesses.
- Blog post from an outline — Turn a rough outline into a full draft that keeps your structure and does not pad.
- Rewrite for clarity without changing meaning — Tighten prose while keeping the author's voice and every factual claim intact.
- Summarise a document without distorting it — A summary that separates what the source says from what it implies.
- Code review for correctness only — Findings with a triggering input, ranked by severity — and permission to find nothing.
Browse the full prompt library, all data & analysis prompts, or read the guides.