What AI Can and Cannot Do in Excel: Copilot, ChatGPT and Claude Compared

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

Updated on 5 September 2026 · Works with Excel 365, 2021 and 2019 with any AI chat tool; Copilot features need Microsoft 365 with a Copilot licence.

AI in Excel means using a language model to write, explain, debug and improve worksheet formulas, and to analyse data in plain English. Today that covers Copilot inside Excel, the COPILOT worksheet function, Analyze Data, and chat tools such as ChatGPT, Claude and Gemini. The one rule that never changes: AI drafts the formula, you verify the result.

What AI does well with Excel formulas

Used properly, an AI assistant removes the slowest part of formula writing: remembering syntax. It is reliably good at these jobs.

  • Plain English to formula. “Total sales for East in July” becomes a working SUMIFS in seconds, argument order included.
  • Explaining a formula you inherited. Paste a nested IF or an INDEX MATCH and ask what each part does. This is where AI is most accurate, because it is reading, not inventing.
  • Refactoring. Turning a 200-character nested IF into IFS or SWITCH, or wrapping repeated calculations in LET.
  • Suggesting modern functions. If you are still writing VLOOKUP with IFERROR, AI will point you to XLOOKUP and show the equivalent.
  • Regular expressions. Patterns for REGEXEXTRACT and REGEXREPLACE are hard for humans and easy for AI.
  • Generating test data. Ask for 20 rows of realistic orders with some blanks and duplicates, and you have a test sheet for your formula.

What AI gets wrong in Excel

Language models predict plausible text. A plausible formula is not always a correct one, and these failures come up constantly.

  • Hallucinated functions and arguments. REGEXMATCH is Google Sheets, not Excel. XLOOKUP has no “case sensitive” argument. AI will confidently produce both.
  • Google Sheets syntax. ARRAYFORMULA and QUERY appear in answers because so much training text is about Sheets.
  • Wrong references. Relative where you needed absolute, or a range that stops one row short.
  • Locale separators. A formula with commas fails in a locale that uses semicolons, and the other way round.
  • Silent logic errors. The formula calculates without an error but counts the wrong thing. Nothing warns you.
  • It cannot see your sheet. ChatGPT, Claude and Gemini only know what you paste or upload. Only Copilot in Excel and add-ins read the workbook directly.
  • No guarantee of reproducibility. The same prompt can return a different formula tomorrow. That is why AI output belongs in a tested formula, not a live one.

AI tools for Excel compared

Every option has a different place to run, a different view of your data and a different price. This table is accurate as of September 2026; check your own build and licence.

Tool Where it runs Sees your workbook? Writes formulas? Cost / licence Best for
Copilot in Excel (pane) Inside Excel, Home tab Yes, the sheet it is on Yes, inserts formula columns and explains them Microsoft 365 Copilot licence or a Personal/Family plan with Copilot credits Formula columns on Tables, pivots, charts, summaries
COPILOT function In a cell as =COPILOT() Only the ranges you pass in No, returns text or values, not formulas Copilot licence; Insiders Beta Channel first Classifying, summarising or extracting text in bulk
Analyze Data Inside Excel, Home tab Yes, the selected range No, builds pivots and charts Included in Microsoft 365, no Copilot licence Quick questions about a range
ChatGPT Browser or app Only what you paste or upload Yes Free and paid tiers Drafting, explaining and refactoring formulas
Claude (and Claude for Excel preview add-in) Browser, app, or add-in in Excel Chat: no. Add-in: yes Yes Free and paid; add-in is a preview on paid plans Long formulas, LET and LAMBDA, careful explanations
Gemini Browser or app (built into Google Sheets, not Excel) Only what you paste Yes Free and paid Quick formulas; watch for Sheets syntax
Python in Excel In a cell as =PY(), runs in the Microsoft Cloud Yes, the ranges you reference No, runs Python instead Microsoft 365; paid add-on for premium compute; not in Excel 2019/2021/2024 Statistics, forecasting, heavy data shaping

Which AI tool to use when

  1. You have a Copilot licence and clean Table data: use the Copilot pane. It sees the real headers, so it rarely gets references wrong.
  2. No Copilot, you need a formula: paste headers and a few rows into ChatGPT or Claude, state your Excel version and locale, and ask for the formula only.
  3. You need to understand an existing formula: any chat tool. Explanation is the safest task for AI.
  4. You need a quick pivot or chart from a range: Analyze Data, free in Microsoft 365.
  5. You need to categorise or summarise text in hundreds of rows: the COPILOT function, if it has reached your channel.
  6. You need statistics or forecasting beyond formulas: Python in Excel, with Copilot’s Advanced Analysis writing the code.

Worked example: one request, two tools

The request is the same: total sales for the East region in July. Here is the sample data in A1:C7.

Region Month Sales
East July 1200
West July 950
East June 800
East July 650
North July 700
East August 900

Copilot in Excel. Format the range as a Table (Insert > Table), open the Copilot pane and type:

Add a column that shows the Sales value only when Region is East and Month is July, otherwise 0.

Copilot proposes a column formula such as =IF(AND([@Region]="East",[@Month]="July"),[@Sales],0), explains it, and inserts it when you click Insert column. Sum the new column to get 1850. Copilot works row by row on a Table; it does not usually write a single summary cell for you.

ChatGPT or Claude. Paste the headers and rows, then send this prompt:

Goal: total Sales where Region is East and Month is July.
Data: Sheet1, A1:C7 with headers Region, Month, Sales; data in rows 2 to 7.
Rules: exact text match, no Table, no helper column.
Excel 365, English (UK) locale, comma separators.
Give the formula only, then one sentence explaining it.

Expected answer:

=SUMIFS(C2:C7,A2:A7,"East",B2:B7,"July")

Result: 1850 (1200 + 650). Verify it by filtering the table to East and July and checking the status bar sum. If the two numbers differ, the formula is wrong, however confident the answer sounded.

Tips and common mistakes

  • Always state your Excel version. Without it, AI may return XLOOKUP or TEXTSPLIT to an Excel 2019 user.
  • Always state your locale. Comma or semicolon separators, and the date format, change the formula.
  • Paste headers, not just a description. Column names remove most reference errors.
  • Ask for the formula only. Long answers hide the actual formula and invite copy-paste mistakes.
  • Test against a known total. Use a filter, a pivot table or a manual sum on five rows before trusting the formula on five thousand.
  • Do not paste confidential data into consumer chat tools. Use dummy rows with the same layout; chapter 15 covers this in detail.
  • Never treat a working formula as a correct one. No error is not the same as the right answer.

Practice exercise

  1. Type the six-row table above into a blank sheet and confirm the SUMIFS formula returns 1850.
  2. Ask any chat tool for the same total in a Google Sheets style, then ask it to convert the answer to Excel. Note what changed.
  3. Ask for the formula again without stating your Excel version. Check whether it used a function your version lacks.
  4. Paste the formula =IF(AND(A2="East",B2="July"),C2,0) into a chat tool and ask it to explain each argument. Compare the explanation with the formula’s behaviour on row 3.
  5. If you have Copilot, format the table as a Table and ask for a formula column. Compare its formula with the ChatGPT answer.

Key takeaways

  • AI is excellent at translating, explaining and refactoring formulas, and poor at knowing which Excel you have or what your data really contains.
  • Only Copilot in Excel and add-ins see the workbook; chat tools see only what you paste.
  • Copilot needs a licence; Analyze Data is free with Microsoft 365; ChatGPT, Claude and Gemini have free tiers.
  • Give every prompt a version, a locale, real headers and an output format.
  • Verify every AI formula against a total you already trust.

Related lessons

Frequently asked questions

Can ChatGPT write Excel formulas?

Yes. ChatGPT writes SUMIFS, XLOOKUP, IFS, LET and most other Excel formulas from a plain English description, and it can read a workbook you upload. It cannot see an open Excel file, so paste your headers and a few rows, state your Excel version and locale, and test the result before you rely on it.

Is Copilot in Excel free?

No. The Copilot pane needs a Microsoft 365 Copilot licence for business accounts, or a Microsoft 365 Personal or Family plan that includes Copilot with monthly AI credits. Analyze Data on the Home tab is free with any Microsoft 365 subscription, and it builds pivot tables and charts from questions, though it does not write formulas.

Which is better for Excel, ChatGPT or Copilot?

Copilot is better when your data is a Table in Microsoft 365, because it reads the real headers and inserts the formula column for you. ChatGPT is better for explaining, refactoring and drafting complex formulas, and it works with any Excel version. Many analysts use both: Copilot inside the workbook, ChatGPT for thinking.

Does AI make mistakes in Excel formulas?

Yes, often. Typical errors are invented functions, Google Sheets syntax, wrong absolute references, ranges one row short, comma and semicolon mix-ups, and logic that runs without an error but counts the wrong rows. Treat every AI formula as a draft and check it against a total you already know.

Can AI see my spreadsheet?

Only some tools can. Copilot in Excel reads the sheet it is open on, the COPILOT function reads the ranges you pass to it, and add-ins such as the Claude for Excel preview read the workbook. ChatGPT, Claude and Gemini in a browser see only the text you paste or the file you upload.

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

All lessonsExcel Formulas with AINext →How to Write a Prompt for an Excel Formula: The 5-Part Template