Excel Formula Prompt (Explained Step by Step)
Describe the table structure rather than the wish: get the formula, its logic and the debugging path together.
You are an Excel and Google Sheets analyst. Write a working formula and explain it. TABLE - Sheet name: [SHEET] - Column A: [A] - Column B: [B] - Column C: [C] - Data starts at row: [START] - Data ends at row: [END] WANTED [CALCULATION] RULES - One primary formula. Alternatives go at the end under "alternative". - Use the function names for my locale, with the English equivalent in brackets. - Use exactly the row numbers I gave; never invent ranges. - Explain the formula piece by piece: what each function does, what it returns. - Handle empty cells, #N/A, #VALUE and division by zero with IFERROR. - Give the formula on one line, then a two-line explanation. OUTPUT 1) FORMULA — one copyable line 2) PIECE BY PIECE — max 5 bullets 3) ERROR CASES — 3 scenarios (blank cell, text instead of number, no match) 4) TEST STEP — how to confirm it works, in one sentence 5) ALTERNATIVE — a different approach (pivot, FILTER, dynamic array) with one line on when it is better
Describe the table, not the wish. The formula is a consequence of the structure.
02 — Variables
Fill in the brackets
The highlighted fields are the places where your own details go. You do not have to fill all of them; anything left empty is completed by the model's own assumption.
[SHEET]
[START]
[END]
[CALCULATION]
03 — How to use
How to use it, step by step
- 01
Give the real column letters and the data start row; range drift is the most common Excel error.
- 02
If your locale uses semicolons as separators, say so — the formula will not run otherwise.
- 03
Test on 3-4 rows before dragging down.
04 — Variations
Same prompt, different result
One prompt does not solve every case. These variations ask for the same job in a different tone, length or format — pick the one that fits.
Google Sheets
Same formula for Sheets: English function names, comma separators, ARRAYFORMULA if needed.
Conditional formatting
Same logic as a conditional formatting rule: range, formula, colour, and the menu path.
05 — Tips
Notes that take the result up a level
- Excel formulas fail on separators and ranges, not on logic. Both are specified here for that reason.
- If the result is a #N/A, the lookup value usually has a trailing space or a type mismatch.
06 — FAQ
Frequently asked about Work and Productivity
Why does my formula return #N/A?
Usually a type mismatch (text vs number) or a trailing space. Wrap the lookup value in TRIM and check the number format of both columns.
Should I use a formula or a pivot?
A formula for a single cell that must update in place; a pivot for summarising a whole table by category.
Next
You may also find these useful
Meeting Notes Summary Prompt (Decisions + Actions)
Splits meeting notes into three clean parts: decisions, actions with owners, and what stayed open.
Beginner ChatGPTClaudeProfessional Email Prompt (Including Difficult Messages)
Decline, chase, apologise and raise prices without damaging the relationship. One-purpose emails with a clear ask.
Beginner ChatGPTClaudeBusiness Plan Prompt: One Page You Will Actually Use
Instead of a 40-page template, a one-page plan that gets used: customer, value, revenue, cost and the first 90 days.
Intermediate ChatGPTClaudePresentation Prompt: Slide-by-Slide Structure
One message per slide instead of text-filled slides, with speaker notes carrying the detail.
IntermediateThis page was prepared by the CopyPromt editorial team, first published 25 October 2026. Prompts are written to work across models; brand names are mentioned for identification only.