In every Excel-with-AI workshop I run, the same thing happens. Two people ask the same AI tool for the same thing. One gets a formula that pastes straight in and works. The other gets a generic example that references columns they don't have. Same tool, same model, completely different result. The difference is never the model — it's the prompt.
This tutorial is not a list of prompts to copy. If you want that, I keep a separate library of 60 AI prompts for Excel (and a matching set of 60 prompts for Google Sheets) organised by task. This guide is the skill underneath that library: how to write your own Excel prompts, why each part of a prompt matters, and why Microsoft Copilot, Claude, and ChatGPT each need a slightly different approach. Learn this once and you stop depending on anyone else's prompt list.
Why prompting for Excel is its own skill
General chat is forgiving. If you ask an AI to "write something about marketing," any reasonable paragraph counts as a win. Excel is the opposite — it is unforgiving in a useful way. A formula either returns the number you expected, or it returns the wrong number, or it errors. There is no "close enough." That precision is exactly why prompting for Excel rewards a bit of structure.
Three things make Excel prompting different from ordinary AI chat, and each one becomes a rule later in this guide:
- The answer has to be exact. A vague request produces a formula that works on the AI's imaginary columns, not your real ones.
- The tool usually cannot see your data. A browser chat with Claude or ChatGPT has no idea what is in your workbook. Copilot, built into Excel, is the exception — and that single difference changes how you prompt.
- Your Excel version decides which functions exist.
TEXTSPLIT,FILTER, andGROUPBYare Microsoft 365 functions. Ask without saying which version you run and you may get a formula your copy of Excel has never heard of.
The one distinction that changes everything: can the tool see your workbook?
Before any prompt template, internalise this. AI tools for Excel fall into two camps, and they need opposite prompting styles.
Tools that see your workbook. Microsoft Copilot runs inside Excel. It can read the active sheet and your selected table, so it already knows your headers, your data types, and roughly how many rows you have. You do not retype any of that. Instead, you tell it the outcome you want in plain business language.
Tools that are blind to your workbook. Claude and ChatGPT in a browser tab see only the words you type. They have no access to your file unless you paste data in or upload it. With these tools, the layout description is the prompt — leave it out and the tool guesses.
| Question | Microsoft Copilot (in Excel) | Claude / ChatGPT (browser) |
|---|---|---|
| Can it see my data? | Yes — the active table and selection | No — only what you type or upload |
| Do I describe my columns? | No, but format data as a Table first | Yes, always — letters, headers, ranges |
| What do I lead with? | The outcome, in business terms | The layout, then the outcome |
| Best for | In-context analysis, charts, summaries | Complex formulas, VBA, debugging, explanations |
If you want the full feature-by-feature comparison of the four big assistants, I cover it in ChatGPT vs Claude vs Copilot vs Gemini for Excel. Here we stay focused on how to talk to each one.
The anatomy of an Excel prompt that works
Whether the tool can see your data or not, a strong Excel prompt is built from the same parts. For Copilot you can drop the layout part because it reads the sheet; for Claude and ChatGPT you need all of them. Here is each component and why it earns its place.
1. The data layout — your columns, in letters
This is the single biggest lever. "Column A has invoice dates, column B has region, column C has revenue, data runs from row 2 to 500" gives the model a real grid to write against. Without it, the AI invents columns, and you spend ten minutes mapping its imaginary D2 onto your real Revenue column. Name the columns by what they actually contain, not by type — "invoice dates" beats "dates."
2. Your Excel version
Function availability is not cosmetic. On Microsoft 365 the model will happily reach for FILTER, SORT, UNIQUE, TEXTSPLIT, and LET. On Excel 2019 or 2016 none of those exist, and the model needs to fall back to INDEX/MATCH and nested SUBSTITUTE. State "I'm on Microsoft 365" or "Excel 2019" in every formula prompt and the failure rate drops immediately.
3. The goal, not the method
Tell the AI what you are trying to achieve, not the function you think you need. "I want to find customers who haven't ordered in 90 days" is a far better prompt than "I need a date subtraction formula." When you specify the method, you cap the answer at your own knowledge. When you specify the goal, the model can suggest a cleaner approach you didn't know existed.
4. Constraints
Say what is off-limits. "No VBA — it has to be a worksheet formula." "It must keep working when I add rows." "Avoid volatile functions because the workbook is already slow." Constraints stop the model from handing you a macro when your company blocks macros, or a hardcoded range that breaks next month.
5. The output you want
Ask for the shape of the answer. "Give me one formula I can paste into F2" is different from "walk me through the steps" which is different from "explain what each part does." If you don't say, you get a wall of prose when you wanted a formula, or a bare formula when you wanted to learn. I almost always add "and explain each part in one line" — that's how you stop being permanently dependent on the AI.
Weak prompt vs strong prompt
Here is the same request, badly and well. The weak version:
"Write a formula to get top products by revenue."
The strong version:
"I'm on Microsoft 365. My data has Region in column A, Product in column B, and Revenue in column C, from row 2 to row 500. I want one formula in cell F2 that returns the top 3 products by total revenue for the region typed in cell E2. No VBA, and it should still work if I add rows. Give me the formula plus a one-line explanation of each function used."
The weak prompt gets a generic SUMIF example on invented columns. The strong prompt gets a TAKE/SORT/GROUPBY or SUMIFS-driven answer that drops into F2 and runs. Same model, same minute — the only variable was the prompt.
How to prompt Microsoft Copilot in Excel
Copilot is the easiest to prompt and the easiest to misuse. Because it reads your sheet, you skip the layout description — but it has one hard requirement that trips up most first-timers.
Format your data as a Table or a clean range first. Copilot reasons over structured data. If your sheet has merged cells, blank header rows, multiple tables stacked on one sheet, or values dumped in random cells, Copilot either refuses or quietly analyses the wrong block. Select your data and press Ctrl+T to make it an Excel Table before you start. I go deep on this in formatting data for Copilot in Excel, because it is the number-one reason Copilot prompts fail.
Once your data is clean, prompt with the outcome in business language:
"Add a column that flags any order where revenue is more than 20% below the average for its region."
"Summarise total revenue by region and show the three regions growing fastest month over month."
"Create a column chart of monthly revenue and highlight the best and worst months."
Notice there are no column letters. You name the concepts in your data — revenue, region, month — and let Copilot map them to your actual columns. If a request spans several steps or needs to act across the workbook, that is the territory of Agent Mode; for a quick read on which Copilot surface fits a task, see Analyst vs Agent Mode vs Copilot Chat. For setup and licensing, start with setting up Copilot in Excel, and for heavier number work, Copilot for data analysis.
How to prompt Claude for Excel
Claude cannot see your workbook in a normal browser session, so you become its eyes. That sounds like a drawback, but it is also why Claude is excellent for the hard cases — long nested formulas, VBA macros, and debugging — because you control exactly what context it gets.
The winning pattern with Claude is layout plus sample rows. Describe the columns, then paste three to five real (or realistic) rows. Sample data lets Claude account for the messy reality of your sheet — blanks, numbers stored as text, trailing spaces, mixed date formats — that a layout description alone hides.
"I'm on Microsoft 365. Here are my columns and five sample rows:
A (Date) | B (Region) | C (Product) | D (Revenue)
2026-01-04 | East | Widget A | 1200
2026-01-04 | West | Widget B | 980
… (three more rows)
Write one formula in F2 that returns total revenue for the region in E2, and explain each part in a line."
Claude is my default for two jobs specifically. For multi-step formula logic it tends to produce clean, readable LET-based formulas — see writing Excel formulas with Claude. For automation, it writes solid VBA from plain English; the workflow is in generating Excel macros with Claude. And when something is broken, paste the formula plus a failing row and ask "why" — the approach is in fixing Excel formula errors with Claude.
How to prompt ChatGPT for Excel
In a plain chat, ChatGPT is blind to your file just like Claude, so the same rule applies: describe the layout and paste sample rows. ChatGPT has one extra option worth knowing — you can upload the actual spreadsheet and let it read the file directly. When you do that, the prompt shifts back towards Copilot's style: lead with the outcome, because the tool can now see the data.
"I've uploaded my sales file. Using the 'Orders' sheet, give me one Excel formula (Microsoft 365) that returns the top 3 products by total revenue for a region I'll type in a cell, and tell me which cell to put it in."
One caution: when ChatGPT reads an uploaded file it often answers by running its own analysis rather than handing you a worksheet formula. If you want a formula to live in your sheet, say so explicitly — "I want a formula in the workbook, not a calculated answer." My full walkthrough of ChatGPT's formula patterns and best follow-up questions is in using ChatGPT to write Excel formulas. The same prompting logic carries over to Google Gemini if that is your tool of choice.
One task, three prompts
To make the "why" concrete, here is a single real task — get the top 3 products by revenue for a chosen region — prompted the right way for each tool. The data is Region in A, Product in B, Revenue in C, rows 2 to 500, on Microsoft 365.
Copilot (it sees the table, so lead with the outcome):
"For my table, show the top 3 products by total revenue for each region, as a summary table I can refresh."
Claude (blind — describe layout, add samples, ask for explanation):
"Microsoft 365. Columns: A Region, B Product, C Revenue, rows 2–500. Sample: East/Widget A/1200, West/Widget B/980, East/Widget C/1500. Write one spilling formula in F2 that lists the top 3 products by total revenue for the region typed in E2. Explain each function in one line."
ChatGPT (uploaded file — outcome first, but pin it to a formula):
"Using the uploaded file's Orders sheet, give me a Microsoft 365 worksheet formula — not a calculated answer — that returns the top 3 products by total revenue for the region in cell E2, and tell me where to paste it."
Three different prompts, one identical result, because each prompt respects what its tool can and cannot see.
Why your prompts fail — and the fix
When an Excel prompt produces something useless, it is almost always one of these. Match the symptom to the fix:
- The formula uses functions you don't have. You didn't state your version. Add "I'm on Excel 2019" (or 365) so the model stays inside your toolset.
- The formula references the wrong columns. You didn't describe the layout, so the model invented one. Give column letters and headers.
- It works on some rows and breaks on others. The model never saw your messy data. Paste three to five sample rows, including the ugly ones with blanks or text-stored numbers.
- You got an essay when you wanted a formula. You didn't specify the output. Ask for "one formula I can paste into F2."
- The answer is technically right but misses the point. You prompted the method, not the goal. Restate what you're actually trying to achieve.
- One huge prompt produced a tangled mess. You bundled a five-step task into one ask. Break it into steps and prompt them in sequence in the same thread.
How to iterate when the first answer is wrong
This is the habit that separates people who "tried AI for Excel and it didn't work" from people who get value every day. Do not start a new chat when the first answer misses. Stay in the same thread and correct it with evidence:
"Close, but it returns 0 for the West region even though there are West rows. Here are two West rows where it fails: [paste them]. What's causing that, and fix the formula."
The model already holds the full context of your earlier messages, so a follow-up with a concrete failing case converges on the right answer far faster than re-explaining everything in a fresh conversation. Pasting the row where it breaks does more than any amount of rephrasing.
Always verify before you trust it
A confident formula is not a correct formula. AI tools will hand you something that looks right and quietly returns the wrong total — a SUMIFS that silently drops rows, a lookup that grabs the first match instead of the latest. Spot-check the AI's output against a small range you can total by hand, and sanity-check the result against what you expect. I wrote a full verification routine in how to review AI-generated Excel formulas before you trust them — run it on anything that feeds a decision or a report.
A reusable Excel prompt template
Keep this somewhere handy. For Claude and ChatGPT in a browser, fill in every line; for Copilot, skip the layout line because it reads your sheet.
"I'm on [Microsoft 365 / Excel 2021 / Excel 2019].
My layout: column A has [what it contains], column B has [what it contains], … data from row [2] to [N].
Sample rows: [paste 3–5 real rows].
Goal: I want to [describe the outcome, not the method].
Constraints: [no VBA / must survive new rows / avoid volatile functions / …].
Output: give me [one formula for cell __ / step-by-step instructions], and explain each part in one line."
Once this structure is muscle memory, you'll write better prompts than most prebuilt prompt packs — and you'll understand the formulas you get back, which is the whole point.
Frequently asked questions
How do I write a good AI prompt for Excel?
Include five things: your data layout in column letters, your Excel version, the goal rather than the method, any constraints, and the output shape you want. Adding "explain each part in one line" turns every prompt into a small lesson so you build skill instead of dependency.
Should I prompt Copilot differently from ChatGPT or Claude?
Yes. Copilot runs inside Excel and reads your active table, so you lead with the outcome in plain language and don't retype columns — but your data must be a clean Table. Claude and ChatGPT in a browser can't see your file, so you must describe the layout and paste sample rows, or upload the file where supported.
Why does the AI keep giving me formulas that don't work?
Usually a missing version, a missing layout, or no sample data. Name your Excel version so it doesn't suggest functions you don't have, describe your columns so it stops inventing them, and paste a few real rows so it accounts for the messy bits. Then verify the result before you rely on it.
Do I still need a prompt library if I can write my own prompts?
A library is a great shortcut for common tasks and a good way to see the structure in action — that's why I keep 60 AI prompts for Excel. But knowing how to build a prompt from scratch means you're never stuck when your task isn't on anyone's list.
Further reading
- 60 AI Prompts for Excel That Actually Work — the copy-paste library this skill lets you outgrow.
- ChatGPT vs Claude vs Copilot vs Gemini for Excel — which assistant to reach for.
- How to Set Up and Use Microsoft Copilot in Excel — first-day setup and licensing.
- Format Data for Copilot in Excel — the table rule that makes Copilot prompts work.
- How to Use ChatGPT to Write Excel Formulas — ChatGPT's formula conversation patterns.
- How to Use Claude AI to Write Excel Formulas Instantly — Claude's formula workflow end to end.
- How to Fix Excel Formula Errors with Claude AI — debugging prompts that diagnose, not just patch.
- How to Review AI-Generated Excel Formulas — verify before you trust.
- Excel Tables Best Practices — the structured-data foundation clean prompting depends on.
- VLOOKUP vs XLOOKUP — the lookup decision you'll most often ask AI to make.
- LET and LAMBDA in Excel — turn the long formulas AI writes into readable, reusable logic.
- GROUPBY in Excel — formula-based summaries AI reaches for instead of a pivot table.
- 15 Excel Formulas That Save Hours of Manual Work — the high-value functions worth prompting for by name.
- Complete Excel Formulas Guide and AI Tools and AI Development Guide — the two hubs this tutorial belongs to.