How to Write a Prompt for an Excel Formula: The 5-Part Template

Part of the free Module 13: Excel Formulas with AI · Lesson 2 of 9 · Full Excel course

Updated on 5 September 2026 · The prompt template works with any AI tool and any Excel version. The Excel version you state inside the prompt is what decides which functions the AI is allowed to use.

A good prompt for an Excel formula tells the AI five things: the goal, the exact data layout, the rules and edge cases, your Excel version and locale, and the output format you want back. Give it all five and ChatGPT, Claude, Gemini or Copilot usually returns a formula that works first time, with the right ranges and the right absolute references.

Why most AI formula prompts fail

Almost every bad answer comes from the same four gaps. The prompt is vague about the goal, it never describes the sheet, it never says which Excel version is in use, and it never says what the reply should look like. The model then guesses. It guesses your column letters, it guesses that you have Microsoft 365, and it wraps the formula in three paragraphs of explanation you have to dig through.

Compare these two requests for the same job.

Vague prompt:
write an excel formula to sum sales for north

A typical reply is =SUMIF(B:B,"North",C:C). It might be right. More often the columns are wrong, it ignores the second condition you had in mind, and it uses whole-column references that slow the file down. Now the same request with the five parts filled in.

Specific prompt:
Goal: total the Amount column for one region and one product.
Data: sheet "Sales", headers in row 1, data in rows 2 to 9.
A = Date, B = Region, C = Product, D = Amount.
The region I want is typed in G2 and the product in H2.
Rules: exact text match, not case sensitive; if nothing matches, show 0.
Version: Excel 2019, English (UK), commas as argument separators.
Output: the formula only for cell I2, then one sentence explaining it.

That prompt has one possible answer, and the model gets it right because there is nothing left to guess.

The five-part prompt template

Use the same five parts every time, in the same order. Once the shape is a habit you can type it in under a minute, and you can reuse it for the whole course.

Part What to write Example line
1. Goal One sentence saying what the formula must produce, in business language. Goal: total the Amount column for one region and one product.
2. Data layout Sheet name, every column letter with its header, the first and last data row, the Table name if the range is a Table, and one or two sample values. Data: sheet “Sales”, rows 2 to 9. A = Date, B = Region, C = Product, D = Amount. Sample row: 05/01/2026, North, Widget, 1200.
3. Rules and edge cases Blanks, duplicates, what to show when nothing is found, case sensitivity, partial matches, text stored as numbers. Rules: ignore blank rows; if the ID is not found show “Not found”; matching is not case sensitive.
4. Excel version and locale 365, 2024, 2021, 2019 or 2016, and whether your Excel separates arguments with commas or semicolons. Version: Excel 2019, English (UK), commas as argument separators.
5. Output format Which cell the formula goes in, and how much explanation you want. Output: the formula only for cell I2, then one sentence explaining it.

Here is the whole thing as one block you can copy, keep in a notes file and refill for every request.

Goal: [what the formula must produce, in one sentence]
Data: sheet "[sheet name]", headers in row 1, data in rows [first] to [last].
[Column letter] = [header], [Column letter] = [header], ...
Table name (if any): [tblName]
Sample row: [a real row of values]
Inputs: [which cells hold the criteria the user types]
Rules: [blanks, duplicates, not found, case, partial match]
Version: Excel [365 / 2024 / 2021 / 2019 / 2016], [comma or semicolon] separators.
Output: give the formula only for cell [X], then one sentence explaining it.

Part 4 matters more than people expect. If you say Excel 2016, the model must avoid XLOOKUP, TEXTJOIN, IFS, FILTER and LET. If you say Microsoft 365, it is free to use the modern functions and the formula gets shorter. State it, and you stop getting formulas your copy of Excel cannot run.

How to describe your sheet so the references come out right

Part 2 is where most of the accuracy comes from, so it is worth being mechanical about it. Five details do the work.

  1. Sheet name. Give it exactly as it appears on the tab. If it contains a space the AI should quote it, as in 'Sales Data'!A2:A100.
  2. Column letters with headers. Write A = Date, B = Region, C = Product, D = Amount. Never say “the sales column”; say which letter it is.
  3. First and last data row. “Rows 2 to 9” stops both whole-column references and off-by-one ranges. If the list grows, say “data starts in row 2 and grows over time” so the AI uses a Table or a large fixed range.
  4. Table name if the range is a Table. If you pressed Ctrl + T and the Table Design tab shows the name tblSales, say so. You then get structured references such as SUMIFS(tblSales[Amount], tblSales[Region], G2), which expand automatically as rows are added.
  5. Which cell the formula goes in, and whether you will copy it. Say “the formula goes in I2 and is copied down to I200”. That single sentence is what makes the AI lock the lookup range with dollar signs, as in $A$2:$A$200, while leaving the row reference relative.

Two extra sentences are worth adding when they apply: whether your dates are real dates or text, and whether values have trailing spaces. Both are common causes of formulas that look correct and return nothing.

Four ready-to-copy prompts and the formulas they return

Each prompt below is written to the template. Under each one is the formula a well-behaved AI should give you, a small sample table and the answer you should see. If your AI returns something different, the follow-up prompts in the next section usually fix it in one turn.

1. A two-criteria total with SUMIFS

Goal: total the Amount column for one region and one product.
Data: sheet "Sales", headers in row 1, data in rows 2 to 9.
A = Date, B = Region, C = Product, D = Amount.
Sample row: 05/01/2026, North, Widget, 1200.
Inputs: the region is typed in G2, the product in H2.
Rules: exact text match, not case sensitive; blank rows are ignored; show 0 if nothing matches.
Version: Excel 2019, English (UK), commas as argument separators.
Output: give the formula only for cell I2, then one sentence explaining it.

Expected formula:

=SUMIFS(Sales!$D$2:$D$9, Sales!$B$2:$B$9, $G$2, Sales!$C$2:$C$9, $H$2)
Row A: Date B: Region C: Product D: Amount
2 05/01/2026 North Widget 1200
3 06/01/2026 South Widget 800
4 08/01/2026 North Gadget 450
5 12/01/2026 North Widget 300
6 02/02/2026 East Widget 900
7 04/02/2026 North Widget 550
8 09/02/2026 South Gadget 700
9 15/02/2026 North Gadget 250

With North in G2 and Widget in H2 the result is 2050, from rows 2, 5 and 7. SUMIFS returns 0 rather than an error when nothing matches, so the “show 0” rule needs no extra wrapper.

2. A lookup that says Not found instead of an error

Goal: return an employee's department from their ID.
Data: sheet "Staff", headers in row 1, data in rows 2 to 7.
A = Employee ID (text such as E101), B = Name, C = Department.
Inputs: the ID to look up is typed in F2.
Rules: exact match only; if the ID is not in the list, return the text Not found; IDs are unique.
Version: Microsoft 365. Also give me a version that works in Excel 2016.
Output: give the formula only for cell G2, then one sentence explaining it.

Expected formulas:

Microsoft 365 and Excel 2021:
=XLOOKUP($F$2, Staff!$A$2:$A$7, Staff!$C$2:$C$7, "Not found")

Excel 2019 and 2016:
=IFERROR(VLOOKUP($F$2, Staff!$A$2:$C$7, 3, FALSE), "Not found")
Row A: Employee ID B: Name C: Department
2 E101 Asha Rao Finance
3 E102 Ben Carter Sales
4 E103 Chen Wei Finance
5 E104 Dita Roy Operations
6 E105 Elena Marks Sales
7 E106 Farid Khan Operations

With E104 in F2 both formulas return Operations. With E999 in F2 both return Not found. Asking for the older version in the same prompt is cheap and saves a second round trip when you have to share the file with a 2016 user.

3. A grade from a score with nested IF

Goal: turn a test score into a letter grade.
Data: sheet "Results", headers in row 1, data in rows 2 to 7. A = Student, B = Score (a number from 0 to 100).
Rules: 90 and above = A, 80 to 89 = B, 70 to 79 = C, 60 to 69 = D, below 60 = F.
If the score cell is blank, return a blank, not F.
Version: Excel 2016, commas as argument separators. Do not use IFS or SWITCH.
Output: give the formula only for cell C2, copied down to C7, then one sentence explaining it.

Expected formula:

=IF(B2="","",IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F")))))
Row A: Student B: Score C: Grade
2 Asha 94 A
3 Ben 88 B
4 Chen 71 C
5 Dita 60 D
6 Elena 45 F
7 Farid (blank) (blank)

The blank rule is the part people forget. Without IF(B2="","",...) an empty cell is treated as zero and row 7 comes back as F, which quietly fails a whole class list. On Excel 2019 or later you can drop the version restriction and ask for the shorter =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F").

4. Building a full name with TEXTJOIN

Goal: build a full name from three columns, with single spaces and no gap when there is no middle name.
Data: sheet "People", headers in row 1, data in rows 2 to 5.
A = First name, B = Middle name (often blank), C = Last name.
Rules: skip empty cells; exactly one space between parts; no leading or trailing space.
Version: Excel 2019, commas as argument separators. Also give an Excel 2016 alternative.
Output: give the formula only for cell D2, copied down to D5, then one sentence explaining it.

Expected formulas:

Excel 2019 and later:
=TEXTJOIN(" ", TRUE, A2, B2, C2)

Excel 2016:
=TRIM(A2&" "&B2&" "&C2)
Row A: First B: Middle C: Last D: Full name
2 Asha (blank) Rao Asha Rao
3 Ben James Carter Ben James Carter
4 Chen (blank) Wei Chen Wei
5 Dita Anne Roy Dita Anne Roy

The second argument of TEXTJOIN is ignore_empty. Setting it to TRUE is what removes the double space in rows 2 and 4. The 2016 alternative works because TRIM collapses runs of spaces inside the text as well as trimming the ends.

Follow-up prompts that fix the first answer

The first formula is a draft. Four short follow-ups turn it into something you can trust, and they work in any chat tool because the model still has the layout you gave it.

  • Explain each argument of that formula in a table. The fastest way to check the AI understood your columns. If its description of argument two does not match your sheet, the ranges are wrong.
  • Rewrite it for Excel 2016 without XLOOKUP, IFS or TEXTJOIN. Use this whenever the file will be opened by someone on a perpetual licence.
  • Make it handle blanks and text that is not a number, and return an empty string instead of an error. This is the edge-case pass you should run on every formula that goes into a shared workbook.
  • Make it shorter, and tell me what I lose. Models happily nest six IFERRORs. Asking for the trade-off keeps you from accepting a shorter formula that silently hides real errors.

Two more are worth keeping in the same list: ask for three test rows that would break the formula, and ask it to convert the argument separators to semicolons for your locale.

Prompt variants for Copilot in Excel versus ChatGPT, Claude and Gemini

Copilot in Excel sits inside the workbook and can already see the Table you are standing on, so part 2 of the template shrinks to a Table name. Chat tools see nothing, so part 2 has to carry the whole layout. The rest of the template is identical.

Template part Copilot in Excel (pane) ChatGPT, Claude or Gemini
1. Goal Same, one sentence. Same, one sentence.
2. Data layout Usually just the Table name and the column headers you want used. Copilot reads the rest. Full layout: sheet name, every column letter and header, first and last row, sample values.
3. Rules and edge cases Same, and still essential. Same, and still essential.
4. Version and locale Not needed; it is your own Excel. Required, or you get Microsoft 365 functions by default.
5. Output format Ask it to add a column called Grade, rather than to give a formula for C2. Ask for the formula only for cell C2, then one sentence.

A Copilot pane prompt for the grade example is simply: Add a column called Grade to the Results table. A is 90 and above, B is 80 to 89, C is 70 to 79, D is 60 to 69, F is below 60, and leave it blank when Score is blank. Copilot inserts the column with a formula and shows the formula it used, so you can still read and check it. There is a fuller walkthrough of the pane in the Copilot lesson of this course.

Worked example: from a vague request to a working formula

Take the Staff table from prompt 2 and suppose you actually need the word Sales for people in the Sales department and the word Other for everyone else.

  1. Write the goal in business words. Show Sales for anyone in the Sales department and Other for everyone else, looked up by employee ID.
  2. Add the layout. Sheet Staff, rows 2 to 7, A = Employee ID, B = Name, C = Department, ID typed in F2.
  3. Add the rules. If the ID is missing, return Not found. Matching is not case sensitive. IDs are unique.
  4. Add version and output. Microsoft 365, commas, formula only for G2 plus one sentence.
  5. Read the reply before pasting it. The expected answer is 
    =LET(d, XLOOKUP($F$2, Staff!$A$2:$A$7, Staff!$C$2:$C$7, "Not found"), IF(d="Not found", d, IF(d="Sales", "Sales", "Other")))

    With E102 in F2 the result is Sales. With E101 it is Other. With E999 it is Not found.

  6. Test the three cases yourself before the formula goes anywhere near a report. Two minutes of testing is the whole difference between using AI well and trusting it blindly.

Tips and common mistakes

  • Never say my data. Column letters and row numbers cost you six words and remove the biggest source of wrong answers.
  • Paste headers, not data. The AI needs the structure, not your customer list. One anonymised sample row is enough, and it keeps confidential values out of a chat tool.
  • Always state the version. Without it you get Microsoft 365 functions, and Excel 2019 answers them with #NAME?.
  • Say where the formula goes and whether you copy it. That is what produces correct absolute references such as $A$2:$A$200.
  • Ask for the formula first, explanation second. Formula only, then one sentence, gives you something you can paste without scrolling.
  • Give the not-found behaviour up front. Deciding between an error, a blank and the words Not found in the prompt saves an entire rewrite.
  • Test before you trust. Check one row you know the answer to, one empty row and one row that should fail. If all three behave, the formula is probably sound.

Errors and how to fix them

What you see Likely cause in the prompt Fix
Excel refuses the formula and highlights a comma Your locale uses semicolons as argument separators; the AI wrote commas Add to part 4 that your Excel uses semicolons as argument separators, or swap the commas yourself.
#NAME? on a function you have never seen Google Sheets syntax such as ARRAYFORMULA, QUERY or REGEXMATCH, or an invented function Reply that the function does not exist in Excel and ask for a rewrite using standard Excel functions for your version.
#NAME? on XLOOKUP, TEXTJOIN, LET or FILTER Version missing from the prompt, so the AI assumed Microsoft 365 State your version and ask for a rewrite without modern functions.
#REF! Sheet name or row range in the prompt did not match the real sheet Give the exact tab name and the first and last data row, then ask it to correct the ranges.
Correct in the first row, wrong when copied down You never said the formula would be copied, so ranges were left relative Ask it to lock the lookup ranges with absolute references and keep the row reference relative.
#N/A on a value you can see in the list Trailing spaces, or an ID stored as text in one place and a number in the other Say so in part 3 and ask for TRIM, or for a match that handles text and numbers.
The formula uses a Table name you do not have You mentioned a table in ordinary English but never named one Say either the exact Table name or that the data is a plain range, not a Table.

Practice exercise

  1. Rebuild the Sales table from prompt 1 in a blank workbook. Write the five-part prompt yourself, without copying, and check the AI returns 2050 for North and Widget.
  2. Change part 4 to Excel 2016 and ask the same lookup question from prompt 2. Confirm you get a VLOOKUP with IFERROR, not XLOOKUP.
  3. Take the grade prompt and remove the blank rule. See what the AI returns for an empty score cell, then add the rule back and compare.
  4. Press Ctrl + T on the Sales range, name the Table tblSales, and rewrite prompt 1 with the Table name instead of the row range. You should get structured references.
  5. Ask a follow-up for three test rows that would break the formula. Add those rows to your sheet and see whether the formula survives them.

Key takeaways

  • A reliable prompt for an Excel formula has five parts: goal, data layout, rules and edge cases, version and locale, output format.
  • The data layout part carries most of the accuracy: sheet name, column letters with headers, first and last row, Table name, and the cell the formula goes in.
  • Stating your Excel version is what stops the AI handing you XLOOKUP, TEXTJOIN or LET when your copy of Excel cannot run them.
  • Ask for the formula first and one sentence of explanation, then use short follow-ups to explain, downgrade, harden against blanks or shorten it.
  • Copilot in Excel needs only the Table name for part 2, because it can already see your sheet; chat tools need the full layout.
  • Always test the first row, an empty row and a failing row before the formula reaches a real report.

Related lessons

Frequently asked questions

How do I write a prompt for an Excel formula?

Write five parts in order: the goal in one sentence, the data layout with sheet name and column letters and row range, the rules for blanks and not-found cases, your Excel version and separator style, and the output format such as formula only for cell C2. Filling all five removes the guesswork that causes wrong formulas.

Why does ChatGPT give me the wrong Excel formula?

Usually because the prompt did not describe the sheet. Without column letters and a row range the model invents them, and without a version it assumes Microsoft 365. Other frequent causes are Google Sheets syntax such as ARRAYFORMULA, missing absolute references when you copy the formula down, and edge cases you never mentioned.

Do I need to tell the AI which Excel version I use?

Yes, for anything below Microsoft 365. XLOOKUP, FILTER, SORT, UNIQUE and LET need Excel 2021 or later; LAMBDA and TEXTSPLIT need Microsoft 365 or Excel 2024; IFS, SWITCH and TEXTJOIN need 2019 or later. On Excel 2016 the AI must stay with VLOOKUP, nested IF and ampersand concatenation, and it will only do that if you say so.

Should I paste my real data into ChatGPT to get a formula?

You rarely need to. Headers, column letters, the row range and one anonymised sample row give the AI everything it needs. Keep customer names, salaries and account numbers out of consumer chat tools, and check your employer’s policy before uploading a workbook. Copilot in Excel works inside your tenant and is the safer route for live company data.

Is the prompt template different for Copilot in Excel?

Only part 2 changes. Copilot can see the Table you are working in, so naming the Table and the relevant columns is usually enough, and you can skip the version because it is your own Excel. Keep the goal, the rules and the output request, and ask it to add a named column rather than return a formula for one cell.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.

← PreviousWhat AI Can and Cannot Do in Excel: Copilot, ChatGPT and Claude ComparedAll lessonsExcel Formulas with AINext →Copilot in Excel: Formula Suggestions, Column Formulas and Analyze Data