Part of the free Module 13: Excel Formulas with AI · Lesson 5 of 9 · Full Excel course
Updated on 5 September 2026 · Works with Excel 365, 2021 and 2019; the chat tools need only a browser. Functions such as XLOOKUP, FILTER and TEXTSPLIT are marked where they need a newer Excel.
Using ChatGPT for Excel formulas, or Claude or Gemini, means describing your sheet in words, receiving a formula as text, pasting it into a cell and testing it. None of the three can see an open workbook. They know only what you paste or upload, so the quality of the formula depends on the quality of your description and on the checks you run afterwards.
ChatGPT, Claude and Gemini compared for Excel work
All three write correct Excel formulas most of the time. The differences that matter are what they can read, what happens to your data and where they run. This table is accurate as of September 2026; plans and features change often, so check the current terms of the tool you use.
| Question | ChatGPT (OpenAI) | Claude (Anthropic) | Gemini (Google) |
|---|---|---|---|
| Free tier writes formulas? | Yes | Yes | Yes |
| Can you upload an .xlsx file? | Yes, on most plans; it can read headers and rows and run Python on them | Yes, on most plans; reads the sheet contents | Yes, in the web app; Google Sheets integration is tighter |
| Works inside Excel? | No first-party add-in from Microsoft; third-party add-ins exist | Claude for Excel is a preview add-in on paid plans; availability depends on your plan | No; Gemini is built into Google Sheets, not Excel |
| Is your chat used for training? | Consumer plans may use it unless you turn it off under Settings then Data controls; Team and Enterprise plans are not used for training | Depends on plan and settings; business plans are not used for training | Depends on account settings and plan |
| Best at | Fast drafts, refactoring, explaining errors | Long formulas, LET and LAMBDA, careful step-by-step explanations | Quick answers; watch for Google Sheets syntax creeping in |
Whichever tool you pick, the workflow below is the same. The tool is interchangeable; the discipline is not.
The six-step workflow for AI formulas
- Describe the sheet with the five-part template. Goal, data layout with headers and row range, rules and edge cases, Excel version and locale, output format. The template is in the prompt lesson.
- Paste five to ten sample rows as a tab-separated block or a plain table, using fake values with the real structure.
- Ask for the formula only, followed by one sentence of explanation. This stops the model burying the answer in a tutorial.
- Paste the formula into the exact cell you named in the prompt. If you said “the formula goes in E2”, put it in E2, or the relative references will be wrong.
- Test on rows where you already know the answer. Count them by hand, filter the sheet, or check the status bar sum.
- Refine with follow-up prompts such as “make it return blank when column C is empty” or “rewrite this for Excel 2019”. Keep the conversation going rather than starting again.
How to paste data into a chat tool safely
Real data rarely needs to leave your machine. The model needs the shape of your sheet, not the contents. Replace anything identifying or commercially sensitive, keep the column order, headers and data types, and include the awkward cases: a blank, a duplicate, a number stored as text.
| Real row (do not paste) | Safe row (paste this) |
|---|---|
| Rohan Mehta, rohan@client.com, Invoice 4471, 18,250.00 | Customer A, a@example.com, Invoice 1001, 18250 |
| Global Freight Ltd, contract renewal, 240,000 | Customer B, contract renewal, 24000 |
| (blank name), (blank email), Invoice 4473, 950.00 | (blank), (blank), Invoice 1003, 950 |
Paste the safe rows straight from Excel: select the range, copy, and paste into the chat box. Excel copies as tab-separated text, which every model reads correctly. Tell the model which row is the header and which cell the formula goes in.
Uploading a workbook: what changes
When you upload an .xlsx file, the model can read every sheet, list the headers, sample the rows and, in ChatGPT and Claude, run Python to inspect the data. Formulas improve because the model sees the actual column names and data types instead of your description of them. The cost is that the whole file, including hidden sheets and named ranges, leaves your machine.
Upload a copy only when the file contains nothing confidential, or when you have stripped it down to headers and dummy rows. Never upload a file with personal data, payroll, pricing or anything covered by a customer contract to a consumer plan. The privacy lesson covers the rules in detail.
Four prompts that work in ChatGPT, Claude and Gemini
Count with two conditions
Goal: count tickets where Team is Support and Status is Open.
Data: sheet Tickets, A1:C7, headers Team, Status, Days open; data in rows 2 to 7.
Rules: exact text match, ignore case.
Excel 2019, English (UK), comma separators.
Give the formula only for cell E2, then one sentence explaining it.=COUNTIFS(A2:A7,"Support",B2:B7,"Open")| Team | Status | Days open |
|---|---|---|
| Support | Open | 3 |
| Sales | Open | 1 |
| Support | Closed | 5 |
| Support | Open | 8 |
| Sales | Closed | 2 |
| Support | Open | 1 |
Expected result: 3. COUNTIFS is not case-sensitive, so “support” and “Support” both count, which matches the rule you stated.
List names per department in one cell
Goal: in E2, list every Name whose Dept matches D2, separated by a comma and a space.
Data: A1:B6, headers Name, Dept; data in rows 2 to 6. D2 holds a department name.
Rules: skip blanks; keep sheet order.
Excel 365, English (UK), comma separators.
Give the formula only, then one sentence explaining it.=TEXTJOIN(", ",TRUE,FILTER($A$2:$A$6,$B$2:$B$6=D2,""))| Name | Dept |
|---|---|
| Asha | Finance |
| Ben | HR |
| Chen | Finance |
| Dana | IT |
| Emma | Finance |
With D2 = Finance the result is Asha, Chen, Emma. FILTER needs Excel 365 or 2021. For Excel 2019 ask for the older form, =TEXTJOIN(", ",TRUE,IF($B$2:$B$6=D2,$A$2:$A$6,"")), entered with Ctrl+Shift+Enter. TEXTJOIN itself needs Excel 2019 or later.
Convert a nested IF into IFS
Rewrite this Excel formula using IFS so it is easier to read. Keep the same results.
=IF(C2>=90,"A",IF(C2>=75,"B",IF(C2>=60,"C","D")))
Excel 2019 or later. Give the formula only.=IFS(C2>=90,"A",C2>=75,"B",C2>=60,"C",TRUE,"D")Test scores 92, 75, 60 and 59 return A, B, C and D in both versions. The final TRUE,"D" pair is the catch-all; without it IFS returns #N/A for anything below 60, which is the most common thing AI forgets.
Convert a Google Sheets formula to Excel
Convert this Google Sheets formula to Excel 365. Explain any function that does not exist in Excel.
=ARRAYFORMULA(IF(A2:A10="","",A2:A10*1.18))=IF(A2:A10="","",A2:A10*1.18)Excel 365 spills the result over nine cells without ARRAYFORMULA. For Excel 2019 the answer is =IF(A2="","",A2*1.18) copied down. If the Sheets formula used REGEXMATCH, a good answer offers REGEXTEST for Excel 365 and ISNUMBER(SEARCH()) for older versions.
Worked example: sales for one rep in one month
The Orders sheet has dates in A, sales reps in B and amounts in C. D2 holds a rep name and E2 the first day of a month. You want the rep’s total for that month in F2.
| Date | Rep | Amount |
|---|---|---|
| 01-Jul-2026 | Priya | 500 |
| 05-Jul-2026 | Rahul | 300 |
| 12-Jul-2026 | Priya | 250 |
| 28-Jul-2026 | Priya | 400 |
| 02-Aug-2026 | Priya | 900 |
| 15-Jul-2026 | Rahul | 150 |
- Copy A1:C7 from Excel and paste it into the chat, then add this prompt:
Goal: total Amount for the Rep in D2 during the calendar month that starts on the date in E2. Data: sheet Orders, A1:C7 pasted above; Date is a real Excel date. Rules: include the first and last day of the month; ignore other reps. Excel 2019, English (UK), comma separators. Give the formula only for F2, then one sentence explaining it. - Expected formula:
=SUMIFS($C$2:$C$7,$B$2:$B$7,D2,$A$2:$A$7,">="&E2,$A$2:$A$7,"<="&EOMONTH(E2,0)) - Paste it into F2 with D2 = Priya and E2 = 01-Jul-2026. Result: 1150 (500 + 250 + 400). The August row is correctly excluded.
- Check it: filter column B to Priya and column A to July, and read the sum in the status bar. It should also say 1150.
- Follow up: “Return blank instead of 0 when D2 is empty.” A good answer wraps the formula in
IF(D2="","", ...).
Tips and common mistakes
- Name the cell the formula goes in. Relative references only make sense from a known starting cell.
- Say which row holds the headers. Models often assume row 1 is data and start ranges one row too high.
- State the version every time. A fresh chat forgets that you use Excel 2019, and XLOOKUP will reappear.
- Paste, do not describe, when the layout is unusual. Merged headers, two header rows or units in a separate row confuse descriptions.
- Keep one conversation per sheet. Follow-up prompts inherit the context, so corrections are shorter and more accurate.
- Ask the model to list its assumptions. “Before the formula, list any assumptions you made about my data” catches wrong guesses early.
- Do not paste passwords, keys or personal data. If a column contains it, replace it before you copy.
Errors and how to fix them
| What you see | Likely cause | Fix |
|---|---|---|
| Formula contains ARRAYFORMULA, QUERY or REGEXMATCH | The model answered in Google Sheets syntax | Reply: “This is Excel, not Google Sheets; rewrite for Excel 2019/365” |
| #NAME? after pasting | A function your version lacks, or an invented function | Check the function exists on Microsoft’s function list; ask for a classic-function version |
| Excel says the formula has an error and highlights a semicolon | Locale mismatch; the model used ; and your Excel expects , (or the reverse) | State your locale in the prompt, or replace the separators |
| Result is right in row 2 and wrong when copied down | Missing $ anchors on the lookup or criteria ranges | Ask for absolute references on every range that must not move |
| Result ignores the last row | Range ends one row short, often because the model counted the header as data | Give the exact last row or convert the data to a Table and use structured references |
| Text numbers are not counted or summed | Column contains numbers stored as text | Convert with VALUE or Text to Columns, or ask for a formula that handles text numbers |
Practice exercise
- Paste the Tickets table into any chat tool and ask for the count of Sales tickets that are Closed. Confirm the answer is 1.
- Ask the same tool for the average Days open for Support tickets that are Open. Check it against a manual average of 3, 8 and 1.
- Ask for the TEXTJOIN formula for Excel 2019 and enter it with Ctrl+Shift+Enter. Compare the output with the FILTER version.
- Paste a formula you use at work, with the cell references but no data, and ask each of the three tools to explain it. Note where their explanations differ.
- Start a new chat without stating your Excel version and ask for a lookup formula. Record which function it chose.
Key takeaways
- ChatGPT, Claude and Gemini all write Excel formulas well; none of them sees your open workbook.
- Describe the sheet with the five-part template, paste safe sample rows and ask for the formula only.
- Paste the formula into the exact cell you named, then test it on rows whose answer you already know.
- Replace names, amounts and identifiers with placeholders before pasting; keep the structure and the edge cases.
- Refine in the same conversation with short follow-ups rather than starting a new prompt.
Related lessons
- Excel Formulas with AI: course hub
- How to Write a Prompt for an Excel Formula: The 5-Part Template
- Verifying AI Formulas: Test Cases, Edge Cases and Hallucinations
- AI and Company Data in Excel: Privacy, Security and Best Practices
- COUNTIFS function and TEXTJOIN function in the formulas library
- How ChatGPT can help write complex Excel functions, VBA and DAX (blog post)
- Microsoft Support: Overview of formulas in Excel
Frequently asked questions
Is ChatGPT free for writing Excel formulas?
Yes. The free tier of ChatGPT writes SUMIFS, COUNTIFS, IFS, XLOOKUP and most other formulas from a description. Paid plans add higher usage limits, better models and more reliable file upload. Claude and Gemini also have free tiers that handle everyday formula requests well.
Can ChatGPT read my Excel file?
Only if you upload it. ChatGPT cannot see a workbook that is open on your computer. When you upload an .xlsx file it can read the sheets, headers and rows and even run Python on the data, which makes its formulas more accurate, but the whole file leaves your machine, so upload copies with dummy data only.
Which AI is best for Excel formulas?
For most people the difference is small. ChatGPT is quick and good at fixing errors, Claude is strong on long LET and LAMBDA formulas and careful explanations, and Gemini is fine for short formulas but sometimes answers in Google Sheets syntax. Use whichever you have, and apply the same testing routine.
Is it safe to paste company data into ChatGPT?
Not by default on a consumer plan, where conversations may be used for training unless you opt out. Paste the structure with fake values instead, or use a business plan that excludes training. Never paste personal data, passwords, pricing or anything covered by a customer contract into a chat tool.
Why does ChatGPT give me Google Sheets formulas?
Because a large share of spreadsheet text on the web is about Google Sheets, models sometimes default to ARRAYFORMULA, QUERY or REGEXMATCH. Say “Excel 365” or “Excel 2019” in every prompt and, if a Sheets function appears, reply that you need the Excel equivalent.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.