Skip to content
Codextroop Prompt Library

Excel

Excel Formula and a Clean Sheet

AI Tools:
ChatGPT, Gemini, ClaudeWorks with ChatGPT, Gemini, Claude
Category: Data & Analytics

Get the formula, a sample table layout, and the error cases to watch for - in Excel or Google Sheets.

Prompt Quality - 100/100 · Strong

The Prompt

excel-formula-and-a-clean-sheet.md

Help me solve this in a spreadsheet.

Tool: {{tool}}
What I am trying to calculate: {{goal}}
Columns I have:
{{columns}}
Example rows:
{{examples}}
Constraints: {{constraints}}

Deliver:
1. Recommended table layout (headers)
2. The formula(s) with A1-style references explained
3. A worked example using my sample rows
4. What breaks it (blanks, text-as-numbers, duplicates)
5. A Pivot / QUERY alternative if that is cleaner

Prefer simple formulas over nested wizardry unless {{goal}} requires it.

Negative prompt

Included when you copy the prompt

Do not invent columns the user does not have. Do not use volatile or obscure functions when SUMIF/XLOOKUP/Pivot would do. Do not skip the worked example or the failure cases. Do not assume Excel 365-only functions when {{constraints}} say older Excel. Do not dump multiple competing approaches without recommending one. Do not invent sample numbers that contradict {{examples}}.

The PromptUTF-8

Fill in your details below - this prompt updates as you type, then copy it.

Fill in your details

0 of 5 filled. Preview updates as you type - then copy the finished prompt (negative prompt included when present).

Excel or Google Sheets.

What you want to calculate.

Existing column names.

A few example rows.

No add-ins, must work on Excel 2016, etc.

excel-formula-and-a-clean-sheet.md

Prompt preview

Help me solve this in a spreadsheet.

Tool: {{tool}}
What I am trying to calculate: {{goal}}
Columns I have:
{{columns}}
Example rows:
{{examples}}
Constraints: {{constraints}}

Deliver:
1. Recommended table layout (headers)
2. The formula(s) with A1-style references explained
3. A worked example using my sample rows
4. What breaks it (blanks, text-as-numbers, duplicates)
5. A Pivot / QUERY alternative if that is cleaner

Prefer simple formulas over nested wizardry unless {{goal}} requires it.
0 / 5 filledPreview
excelgoogle sheetsformulasspreadsheets

Examples

Example input

Tool: Excel What I am trying to calculate: Monthly commission = 8% of closed revenue per rep, only for deals with Status = Closed Won Columns I have: Rep, Deal, Amount, Status, CloseDate Example rows: Ana, ACME-12, 12000, Closed Won, 2026-08-03 Ana, Beta-9, 4000, Open, Sam, Gamma-2, 8000, Closed Won, 2026-08-11 Constraints: Must work in Excel 2016; no Power Query add-ins

What you should get

1. Layout: Keep raw deals on sheet Deals (A:E). On sheet Summary: Rep | ClosedWonRevenue | Commission. Unique reps in A2:A. 2. Formulas (Summary): B2: =SUMIFS(Deals!C:C,Deals!A:A,A2,Deals!D:D,"Closed Won") C2: =B2*0.08 3. Worked example: Ana Closed Won = 12000 → commission $960. Sam = 8000 → $640. Beta-9 excluded (Open). 4. Breaks: Amount stored as text; Status typos ("Closed won"); blank Rep; duplicate deal rows double-count. 5. Pivot alternative: Pivot on Rep, filter Status = Closed Won, sum Amount; add commission column = Amount*0.08 beside the pivot.

People also copied

Analyse a Dataset With a Question

When to use this prompt

Use it when

  • You need help with operators.
  • You need help with finance.
  • You need help with students.

Skip it when

  • You only need a one-line ask with no constraints, format, or context.
  • You cannot supply the fill-in details the template asks for.
  • A different category or tool-specific workflow fits the job better.

How to get a good result

Done when

1. Layout: Keep raw deals on sheet Deals (A:E). On sheet Summary: Rep | ClosedWonRevenue | Commission. Unique reps in A2:A. 2. Formulas (Summary): B2: =SUMIFS(Deals!C:C,Deals!A:A,A2,Deals!D:D,"Closed Won") C2: =B2*0.08 3. Worked example: Ana Closed Won = 12000 → commission $960. Sam = 8000 → $640. Beta-9 excluded (Open). 4. Breaks: Amount stored as text; Status typos ("Closed won"); blank Rep; duplicate deal rows double-count. 5. Pivot alternative: Pivot on Rep, filter Status = Closed Won, sum Amount; add commission column = Amount*0.08 beside the pivot.

Common mistakes

  • Leaving tool, goal, columns vague - the model then invents placeholders.
  • Asking ChatGPT, Gemini, or Claude for a shorter answer after a long structured prompt - that throws away the format rules.
  • Stopping when the first draft appears instead of checking it against the success criteria below.

FAQ

What do I fill in before I copy this prompt?
Replace these placeholders with your details: tool, goal, columns, examples, constraints. Required fields are marked on the page. The prompt text updates as you type.
Which AI tools is this prompt written for?
It works best with ChatGPT, Gemini, Claude. Copy the filled prompt and paste it into the tool you already use.
What should a good result look like?
1. Layout: Keep raw deals on sheet Deals (A:E). On sheet Summary: Rep | ClosedWonRevenue | Commission. Unique reps in A2:A. 2. Formulas (Summary): B2: =SUMIFS(Deals!C:C,Deals!A:A,A2,Deals!D:D,"Closed Won") C2: =B2*0.08 3. Worked example: Ana Closed Won = 12000 → commission $960. Sam = 8000 → $640. Beta-9 excluded (Open). 4. Breaks: Amount stored as text; Status typos ("Closed won"); blank Rep; duplicate deal rows double-count. 5. Pivot alternative: Pivot on Rep, filter Status = Closed Won, su…
What are common mistakes with this prompt?
Leaving tool, goal, columns vague - the model then invents placeholders. Asking ChatGPT, Gemini, or Claude for a shorter answer after a long structured prompt - that throws away the format rules. Stopping when the first draft appears instead of checking it against the success criteria below.

Best for

  • Operators
  • Finance
  • Students

More prompts like this — same topic and style.

More like this

Analyse a Dataset With a Question
SQL Query From a Plain-English Ask
KPI Review for a Weekly Meeting
One-Page Cheat Sheet