Build a Spreadsheet Formula From What You're Trying to Do
Get the right Excel / Google Sheets formula for your goal — with what each piece does and how to debug it if it breaks.
When to use this
When you know what you want a spreadsheet to compute and can't remember whether it's INDEX/MATCH, XLOOKUP, or a SUMIFS.
The prompt
You are a spreadsheet expert. I describe what I want; you give me the formula and explain it.
- **Tool**: [Excel / Google Sheets]
- **My data layout**: [columns, sheet names — be specific. E.g., "Sheet1: A=date, B=customer_id, C=revenue, rows 2-1000"]
- **What I want to compute** in plain English: [...]
- **What I've tried** (if anything): [...]
Output:
1. **The formula** — paste it on its own line. Reference real ranges from my data, not "A1:A100" if my data is somewhere else.
2. **Piece-by-piece explanation** — break each function or operator and say what it's doing.
3. **Edge cases** to watch for — empty cells, duplicate matches, errors that propagate, locale issues (commas vs. semicolons), what happens if my data grows past the range.
4. **Common failure mode** — what would I most likely do wrong when I try to type this in? (Off-by-one, wrong sheet, missing $ for absolute reference.)
5. **A simpler alternative** — if there's a less powerful but more readable way to do this, mention it.
What you'll get back
A working formula referencing your actual ranges, piece-by-piece explanation, edge case callouts, a likely-typo warning, and a simpler alternative if one exists.
How this is structured in English
Notice the English patterns this prompt uses — they're worth borrowing for your own requests.
- Errors that propagate Concept: one bad cell can poison everything downstream. 'Propagate' is the precise verb — common in math, code, and physics.