Quick answer
The short version of how to write Excel formulas using ChatGPT: describe your sheet before your request — Excel version, header row, what each column holds, the target cell, your rule in plain English, and commas or semicolons. Then test the formula on five rows before it touches real data.
You know exactly what you want the spreadsheet to do. You just can’t remember whether it’s SUMIFS or SUMIF, whether the criteria go before or after the range, or why the version you typed returns zero. That gap between knowing the logic and knowing the syntax is the whole reason to learn how to write Excel formulas using ChatGPT.
Most guides stop at the obvious part: type what you want, copy what comes back. That works for =SUM(A1:A10). It falls apart the moment your headers start on row 4, your colleague is on Excel 2019, or your regional settings expect semicolons instead of commas.
- Generate a formula from a plain English description of your data and your rule.
- Explain a formula you inherited and can’t safely touch.
- Debug a formula that returns an error code instead of a number.
The part nobody writes about is the fourth one: describing a company spreadsheet accurately without pasting a single real number into a chatbot. That gets its own section, because in most workplaces it decides whether you can use any of this at all. And if what you actually want is analysis rather than arithmetic, analysing a spreadsheet with ChatGPT is a different workflow with different rules.
Will this work for you?
Why Formula Prompts Usually Fail
Formula prompts fail for a boring reason: the request describes the goal but not the sheet. ChatGPT can work out what “total sales for the North region” means. It cannot work out that your data starts on row 4, that your Sales column is stored as text, or that your build of Excel doesn’t have XLOOKUP.
Three jobs, one chat window — and all three run on the same brief.
Ask any Microsoft 365 trainer what they see most often and you’ll get a version of the same answer: people blame the AI for a formula that was doomed by a missing sentence. The model returned a perfectly valid formula. It was valid for a spreadsheet that wasn’t theirs.
There is a version of this problem that Microsoft solved differently. Copilot inside Excel can see your sheet, so it never has to guess the layout. If your organisation gives you that licence, use it. Most people reading this don’t, which is exactly why the plain English route matters.
The Five-Slot Formula Brief
The Five-Slot Formula Brief is a prompt that states five things before it states the request: your Excel version, your data layout, your target cell, the rule in plain English, and your separator locale. It’s the approach we teach at PromptPeakAI, and it reduces how to write Excel formulas using ChatGPT from a guessing game to a form you fill in.
The five slots, in the order they belong in your prompt.
The version slot matters most
ChatGPT defaults to modern functions. Ask for a lookup and you’ll usually get XLOOKUP, which is genuinely the better function, and which, according to Microsoft’s own XLOOKUP documentation, does not exist in Excel 2016 or Excel 2019. Paste it into either and Excel hands you #NAME? instead of a number.
If your employer runs a perpetual Office licence rather than a Microsoft 365 subscription, say so in every formula prompt. One clause saves an afternoon.
Commas, semicolons, and locale
Excel’s argument separator follows your regional settings. Much of Europe uses semicolons, so a formula written with commas arrives broken for a reader in Berlin or Madrid, and the error message doesn’t explain why. ChatGPT writes commas unless you tell it otherwise.
Act as an Excel expert. Write one formula for me. VERSION: I am using [Microsoft 365 / Excel 2021 / Excel 2019 / Excel for the web]. LAYOUT: My headers are in row [1]. Column A = [what it holds]. Column B = [what it holds]. Column C = [what it holds]. Data runs from row [2] to row [500]. TARGET CELL: The formula goes in [D2] and I will drag it down the column. THE RULE: [Describe in plain English what should be calculated, including every condition.] SEPARATOR: My regional settings use [commas / semicolons] between arguments. Return the formula on its own line, then explain in plain English what each part does. Do not use any function unavailable in my stated version. If my description is missing something you need, ask me instead of guessing.
✕ Goal only
"Write me an Excel formula to calculate commission based on region and sales."
✓ Goal plus sheet
"Microsoft 365. Headers in row 1, data rows 2 to 400. Column B = Region, Column C = Sales. Formula goes in D2 and gets dragged down. North with sales above 50,000 pays 10%; North at or below pays 5%. Every other region pays 8% and 4% on the same split. Commas as separators."
Length is not what makes the second version work. Every sentence in it closes a gap the model would otherwise fill with an assumption. That’s the same principle behind giving AI proper context and the Role, Goal, Context, Format structure we use across every workflow on this site. A formula brief is that structure with spreadsheet-shaped slots.
Save the template somewhere you can reach it in five seconds. There’s a copy in our free AI work templates library if you’d rather not retype it each time.
How to Write Excel Formulas Using ChatGPT
Here’s the full run, from empty cell to working formula. Four steps cover how to write Excel formulas using ChatGPT end to end: roughly ten minutes the first time, and well under a minute after that.
Describe your sheet, not your problem
Open the file and write down four things before you open a chat window: which row your headers sit on, what each relevant column actually contains, what type those values are, and where the answer needs to appear. Thirty seconds, and it’s the half of the job people skip.
Data type is the sneaky one. A column of figures exported from a finance system frequently arrives as text, which looks identical on screen and behaves completely differently in a SUM.
Fill in the five slots
Drop your notes into the template. Be specific about the rule, including the edge cases — what happens at exactly 50,000, what happens when a cell is blank. If you don’t decide, the model decides for you and won’t mention it.
Act as an Excel expert. Write one formula for me. VERSION: Microsoft 365. LAYOUT: Headers in row 1. Column A = Sales Rep, Column B = Region, Column C = Total Sales. Data runs from row 2 to row 400. TARGET CELL: D2, dragged down column D. THE RULE: Calculate commission. If Region is "North" and Total Sales is above 50000, pay 10% of Total Sales. If Region is "North" and Total Sales is 50000 or below, pay 5%. For every other region, pay 8% above 50000 and 4% at or below. Treat a blank Total Sales as zero. SEPARATOR: Commas. Return the formula on its own line, then explain each part in plain English. Do not use any function unavailable in Microsoft 365.
Paste it into the formula bar
Click the target cell, click into the formula bar, paste, press Enter. Keep the equals sign — ChatGPT almost always includes it, and Excel needs it. Paste a formula without it and Excel stores the whole thing as text, showing you the formula instead of the answer.
The ChatGPT reply showing a generated Excel formula in a code block, with the plain English explanation of each part underneath.
Crop to the chat response area only. Hide the sidebar, chat history list, and account name.
File: chatgpt-excel-formula-output.png · Alt: "ChatGPT returning an Excel formula with a plain English explanation of each part"Test five rows before you trust it
Copy five representative rows into a blank sheet and run the formula there. Work out two of the answers with a calculator. If the formula agrees with you on both, including the awkward row you deliberately picked, it will agree on all 400.
You should now have a formula returning a number rather than an error code or formula text, and two rows you have checked by hand.
That’s the generate path. Asking what the numbers mean rather than how to calculate them is a separate job — analysing a spreadsheet covers that one. Google Sheets users can run this identical brief with only the function names changing, though Gemini inside Google Sheets can read the sheet directly and skip the layout slot entirely.
Explain a Formula You Inherited
Paste the formula, say who you are, and ask for a plain English breakdown. This is the highest-value and least-used half of the workflow. Most operations leads will tell you that decoding a predecessor’s audit spreadsheet used to mean an afternoon of highlighting fragments and pressing F9, hoping not to break anything.
Explain this Excel formula to me in plain English. I am a non-technical manager, not an analyst. =INDEX(Data!$C$2:$C$500, MATCH(1, (Data!$A$2:$A$500=B2)*(Data!$B$2:$B$500=C2), 0)) Cover these five things, in this order: 1. What the formula returns, in one sentence. 2. What each function does, in the order Excel evaluates them. 3. Which cells, ranges, and sheets it depends on. 4. What would break it — added rows, renamed sheets, blank cells, duplicate matches. 5. One sentence I could paste into a handover note for whoever inherits this next. Do not rewrite or improve the formula. I only want to understand it.
Items four and five are the ones that pay for themselves. “What would break it” turns a black box into a documented risk you can actually raise in a meeting. The handover sentence goes straight into the file’s notes tab so the next person doesn’t repeat your afternoon.
Modernise an old formula safely
Same idea, different ask. When a legacy VLOOKUP keeps breaking because somebody inserted a column, request the rewrite — but pin your version first, or you’ll be handed a function your Excel has never heard of.
I am using [Microsoft 365 / Excel 2021 / Excel 2019]. Rewrite this formula so it stops breaking when columns are inserted: [paste your formula here] Rules: - Use only functions available in my stated version. - The result must be identical to what the original returns. - Show the old and new formulas one after the other. - Tell me in one sentence what changed and why the new version is harder to break.
Debug Broken Formulas and Errors
An error code is information, not a failure. Excel is telling you which of four things went wrong, and each one maps to a different fix. Hand ChatGPT the code, the formula, and two rows of sample data, and the diagnosis takes seconds rather than an afternoon.
Four error codes, what each one usually means, and the fastest thing to ask.
This Excel formula is returning an error. Diagnose it. FORMULA: [paste the full formula] ERROR SHOWN: [#N/A / #VALUE! / #REF! / #NAME? / a wrong number] VERSION: [Microsoft 365 / Excel 2021 / Excel 2019] SAMPLE DATA: [paste two or three rows that trigger the error, using dummy values] WHAT IT SHOULD RETURN: [the correct answer for those specific rows] Give me the root cause first, in one sentence. Then the corrected formula. Do not wrap it in IFERROR unless hiding the error is genuinely the right fix here — I would rather understand the problem than paper over it.
An Excel worksheet cell displaying a #N/A error, with the formula bar above showing the XLOOKUP formula that produced it.
Crop to the worksheet grid and formula bar. Hide sheet tabs containing company names and any file path in the title bar.
File: excel-na-error-formula-bar.png · Alt: "Excel cell showing an N A error with the formula visible in the formula bar"Most failures aren’t really Excel’s fault, though. They trace back to a slot you left out of the brief.
| What went wrong | Why it happens | What to change |
|---|---|---|
| Totals come out wrong | Ranges start at the wrong row | State your header row explicitly |
| Excel rejects the formula | Separator mismatch with your locale | Name commas or semicolons in the brief |
| #NAME? on a lookup | Function missing from your Excel | Pin your version in every prompt |
| Numbers behave like text | Values imported from another system | Say the column is stored as text |
| Circular reference warning | The model guessed your target cell | Name the exact target cell |
If the corrected formula still misses, don’t open a fresh chat. Stay in the same conversation and describe what actually happened — the same diagnostic approach to refining AI output works here, and the model already has your layout in front of it.
Protect Company Data While Prompting
You don’t need to show ChatGPT real data to get a working formula. Formula logic depends on structure — which column, which row, which rule — and structure survives anonymisation completely intact. Replace the values, keep the shape, and the formula that comes back is identical.
✕ Real records pasted in
"Column A: Priya Sharma, Column B: Northern Region, Column C: 84,500. Column A: Daniel Okafor, Column B: Southern Region, Column C: 61,200."
✓ Structure only
"Column A holds employee names as text. Column B holds one of four region labels. Column C holds sales figures as numbers, roughly 20,000 to 120,000."
The right-hand version gives the model everything it needs and your employer nothing to worry about. Notice it’s arguably the more useful prompt too, because it states the data type and the range, which the raw rows only imply.
LAYOUT: Headers in row 1, data rows 2 to 900. Column A = employee identifier (text — no real names needed). Column B = department label, one of six possible values. Column C = monthly figure (number, roughly 1,000 to 40,000). Column D = date the record was entered. Column E = status, either "Approved" or "Pending". Treat every value above as a stand-in example. The formula must work on real data with this exact structure.
Column letters, header names, data types, your rule in plain English.
Client and staff names, real figures, internal project codes.
Payroll records, personal data, unreleased financials, login credentials.
The paid tiers change the privacy answer more than they change the formula answer. Every technique in this guide runs on the free plan.
| Plan | Price | What it adds for formulas |
|---|---|---|
| Free | $0 | Everything in this guide, at lower message limits |
| Go | $8 per month | Roughly ten times the free limits |
| Plus | $20 per month | File uploads and higher reasoning settings |
| Business | $20 to $25 per user | No training on your workspace data |
That last row is the one that matters in a regulated workplace. OpenAI’s published business plan terms state that workspace content is not used to train its models, and price the tier at $20 per user per month billed annually, or $25 billed monthly, with a two-seat minimum. Consumer plans work differently, and the free and Go tiers now carry ads in the US.
Three things to settle before you paste anything from a work file:
- Check your employer's AI policy first. A personal account used on company data is the most common way people breach a rule they didn't know existed.
- Check your own data controls. Consumer plans include a setting that governs whether your conversations are used to improve the models. Find it before you need it, not after.
- Anonymise anyway. It costs thirty seconds and it removes the question entirely, which is worth more than being technically in the clear.
The wider version of this question — what your employer can see, what OpenAI retains, and where the real lines sit — is covered in our guide to using ChatGPT safely at work.
What ChatGPT Gets Wrong Here
Four failure modes are worth knowing before you let any of this reach a spreadsheet a director will open.
Where this genuinely falls short:
- Invented functions. The model will occasionally produce a confident, plausible, non-existent function name. Excel replies with #NAME?, which is at least an honest failure.
- Silently wrong answers. A formula that runs and returns a number is not the same as a formula that returns the right number. This is the dangerous one, because nothing flags it.
- No sight of your sheet. Unless you upload the file, the model works entirely from your description. A wrong description produces a correct formula for the wrong spreadsheet.
- Judgment stays yours. Whether commission rounds up, whether a blank counts as zero, whether last quarter's outlier belongs in the total — the model will pick something, and it will not tell you it picked.
The first one has a name and a built-in safety net. AI hallucination is easy to catch in a formula context, because Excel catches it for you the moment you press Enter. The second failure mode has no such net, which is why the last step of the workflow isn’t optional.
Two minutes of checking, before the formula touches anything that matters.
Why five rows is enough
Pick five rows that between them cover your awkward cases, not five convenient ones:
- One blank cell in a column your rule depends on.
- One duplicate, if the formula does any kind of lookup.
- One value sitting exactly on a threshold — the 50,000 that decides which rate applies.
- Two ordinary rows, as a check that the normal case still works.
A formula that survives those five will survive the other 395, because spreadsheet logic doesn’t get harder with volume. It gets harder at edges. Two minutes of checking, set against a commission run that goes out wrong or a board figure a finance director queries in the room, is the cheapest two minutes in this guide.
Start with the version slot and the layout slot — those two alone fix most of what goes wrong, and you’ll feel the difference on the very first formula you ask for. Once the pattern becomes habit it carries into everything else you hand to an AI tool, which is really the point. You’re not learning prompts. You’re learning to describe your own work precisely enough that a competent stranger could pick it up and finish it.
Run the five-slot brief on your next formula
- Note your Excel version, your header row, and what each column holds. Write your rule as conditions, including what happens at the edges. State whether your regional settings use commas or semicolons. Paste the brief into ChatGPT, then copy the formula into the formula bar. Test it on five sample rows and check two answers by hand.