All categories/Office and documents
Level Starter · Excel Formula Sheets
Spreadsheet formulas: structure first, formula second
You want the sheet to compute itself but cannot describe where the columns are, so any formula is a guess.
Prompt
I want the sheet to do this: {{goal}}
Sheet layout (paste the headers, mark columns A/B/C, say which row is the first data row):
{{layout}}
Data quirks: {{quirks}} (merged cells, blank rows, numbers stored as text, date formats)
Requirements:
1. Restate the layout and goal as you understand them, and flag what you might be getting wrong.
2. Give the formula and explain each function in it.
3. Give a 'if it errors, check these three things first' list.
4. If a formula is the wrong tool here, say so and give the pivot-table or text-to-columns steps instead.What to state before it starts
Leave these unsaid and the model fills them with an assumption. Most 'wrong answers' start here.
- {{goal}}: e.g. 'sum last month by customer, counting each name once'
- {{layout}}: column letters and first data row are mandatory
- {{quirks}}: merged cells and text-numbers break formulas most often
Go through each of these
Where this kind of work most often goes wrong. Already in the prompt, so you do not have to remember it.
- Without column letters it invents ranges like A1:A100.
- 'Restate first' catches most misaligned formulas.