Three AI tools dominate the conversation around Excel: ChatGPT (OpenAI), Microsoft Copilot (built into Microsoft 365), and Google Gemini. Each promises to save you time on spreadsheets. Each has strengths and weaknesses that matter differently depending on what you are trying to do.

This article compares them across five specific Excel tasks using the same prompts. No marketing language. No speculation about future capabilities. Just practical results based on current versions as of mid-2026.

Task 1: Writing Excel Formulas

The prompt: "Write an Excel formula that sums Q4 sales (October to December) by region, excluding any rows where the product was returned. The data has columns A: Date, B: Region, C: Product, D: Sales Amount, E: Returned (Yes/No)."

ChatGPT: Returns a clean SUMIFS formula: =SUMIFS(D$2:D$1000, A$2:A$1000, ">="&DATE(2026,10,1), A$2:A$1000, "<="&DATE(2026,12,31), E$2:E$1000, "No"). It explains each argument and flags the need to adjust the year. Handles the date logic and exclusion condition correctly in one pass. Rating: 4.5/5

Microsoft Copilot: Because it operates inside Excel, Copilot can reference actual column names from your table. It suggests a structured reference version: =SUMIFS(Table1[Sales], Table1[Date], ">="&DATE(2026,10,1), Table1[Date], "<="&DATE(2026,12,31), Table1[Returned], "No"). Faster if your data is already formatted as a table. Less useful for one-off formulae or non-tabular ranges. Rating: 4/5

Google Gemini: Generates a correct but less polished formula. Returns =SUMIFS(D:D, A:A, ">=1/10/2026", A:A, "<=31/12/2026", E:E, "No"). The date format is ambiguous (depends on regional settings) and using full column references (D:D) can slow down larger sheets. Functional for small datasets but less robust. Rating: 3/5

Verdict: ChatGPT wins for complex formula generation. Copilot wins for in-context speed when your data is already structured as an Excel table.

Task 2: Data Cleaning

The prompt: "I have a column of customer names with inconsistent formatting: mixed case, extra spaces, some entries have middle initials, some have titles like 'Dr.' or 'Mr.'. Clean it into First Name, Last Name columns."

ChatGPT: Generates a multi-step approach using TEXT formulas. Suggests TRIM, PROPER, and a combination of FIND/MID/LEFT to split name parts. Also provides a Power Query alternative. The output is thorough but requires manual assembly. Rating: 4/5

Microsoft Copilot: Uses Excel's built-in Flash Fill or Power Query suggestions depending on the data context. For simple splits and standardisation, Copilot is faster because it can apply transformations directly. For complex name parsing (titles, suffixes, multi-word last names), it struggles and sometimes suggests manual editing. Rating: 3.5/5

Google Gemini: Produces a TEXTSPLIT-based approach that works well for recent Excel versions that support the function. Also includes FILTERXML suggestions for older versions. Less aware of Excel version limitations than ChatGPT. Rating: 3/5

Verdict: No AI tool handles data cleaning better than Power Query. For one-off cleaning tasks, ChatGPT provides the most thorough formula-based approach. For repeatable cleaning, Power Query remains the right tool. See our guide on /blog/using-ai-in-excel-practical-workflows for the full workflow.

Task 3: VBA / Macro Generation

The prompt: "Write a VBA macro that exports each worksheet in the active workbook to a separate PDF file, named after the sheet, saved in a folder the user selects."

ChatGPT: Produces a complete, well-commented macro with error handling. Includes a FolderPicker dialog, loops through sheets, skips empty sheets, and names PDFs correctly. The code compiles and runs with minor adjustments. Rating: 4.5/5

Microsoft Copilot: Generates a shorter macro that works for the basic case. Less comprehensive: no folder selection dialog (hardcodes the path), no error handling, no empty-sheet check. Good for a quick one-off but not production-ready. Rating: 3/5

Google Gemini: Produces a functional macro similar to ChatGPT's but with slightly more verbose comments. Handles the core logic correctly but occasionally adds unnecessary variables or redundant checks. Rating: 3.5/5

Verdict: ChatGPT is the strongest option for VBA generation. Its outputs are more complete, better commented, and include edge-case handling that the other two tools typically omit. That said, all AI-generated VBA should be tested on a copy of your workbook before deployment. For more on VBA alternatives, see /blog/excel-automation-with-power-query.

Task 4: Explaining What a Complex Formula Does

The prompt: "Explain this formula: =IFERROR(INDEX($B$2:$B$100, MATCH(0, COUNTIF($C$1:C1, $B$2:$B$100), 0)), "")"

ChatGPT: Breaks it down step by step: identifies it as a unique-value extraction pattern (an array formula predecessor to Excel's UNIQUE function). Explains the COUNTIF-INDEX-MATCH mechanics, how the expanding range works, and why IFERROR is needed. Clear and accurate. Rating: 5/5

Microsoft Copilot: Provides a competent explanation but is briefer. Identifies the purpose (extracting unique values) and explains the key components. Less detailed on the expanding range $C$1:C1 trick. Rating: 4/5

Google Gemini: Gives a solid explanation comparable to ChatGPT's. Correctly identifies the formula as a classic Excel pattern for listing unique entries. Slightly more conversational but still accurate. Rating: 4.5/5

Verdict: All three tools handle formula explanation well. ChatGPT and Gemini are more thorough. This is genuinely useful -- letting AI translate legacy formulas from inherited spreadsheets saves significant time.

Task 5: Generating Scenario / Template Structures

The prompt: "Design a financial model template for a SaaS startup that shows monthly revenue, cost of goods sold, operating expenses, and cash burn. Include a three-scenario dropdown for Base / Upside / Downside."

ChatGPT: Produces a complete model structure with row labels, recommended layout (inputs section, calculations section, outputs section), and a CHOOSE-based scenario selector. Includes instructions for data validation dropdowns and conditional formatting for variance highlights. Rating: 5/5

Microsoft Copilot: Cannot generate this type of structured template as effectively because it is designed for in-sheet operations rather than architectural design. It can suggest layout ideas but lacks the depth of a general-purpose LLM for model planning. Rating: 2.5/5

Google Gemini: Generates a solid template structure similar to ChatGPT but with less detail on the implementation steps. Provides a good starting point that an experienced Excel user can build on. Rating: 4/5

Verdict: For template design and model architecture, general-purpose LLMs (ChatGPT, Gemini) significantly outperform Copilot. They think about spreadsheet structure in ways Copilot was not designed to handle.

The Full Comparison

Note: These ratings reflect each tool's effectiveness for the specific task as of mid-2026. The gap will narrow as all three products improve.

When AI Helps and When It Does Not

AI tools are excellent at:

• Formula translation -- converting natural language into Excel syntax
• VBA scaffolding -- generating a working first draft of a macro
• Formula explanation -- deciphering spreadsheets you inherited from a (now departed) colleague
• Template brainstorming -- suggesting model structures and layouts

AI tools are unreliable at:

• Circular references -- financial models often require intentional circular logic (e.g., interest on debt that depends on cash flow, which depends on interest). AI models rarely handle this correctly. Our article about /blog/chatgpt-wont-replace-accountants covers why this is a fundamental limitation.
• Domain-specific validation -- knowing whether an Australian BAS calculation is correct, or whether a specific accounting standard applies, is outside the training scope of general LLMs
• Data reconciliation -- AI does not check. It generates. Deterministic approaches (Excel formulas, Power Query, dedicated reconciliation tools) are where this work belongs
• Audit-proof spreadsheets -- AI models do not understand internal controls, versioning, or audit trail requirements

Further Reading

For a complete overview of this topic, see the Business Automation in Excel: The Complete Guide for Australian Businesses (2026).

Frequently Asked Questions

Which AI tool is best for writing Excel formulas?

ChatGPT (GPT-4) is the most reliable for complex multi-condition formulas because it handles nesting and edge cases well. Microsoft Copilot is best for simple, natural-language-to-formula conversions directly inside Excel. Gemini is good for standard formulas but less consistent with nested logic and array operations.

Can AI tools generate working VBA macros?

Yes, but all three tools produce VBA that needs testing and refinement. ChatGPT generates the most complete macros with proper error handling. Copilot generates simple macros quickly since it is integrated into the Excel environment. Gemini produces functional but occasionally verbose code. None is perfect -- always test macros on a copy of your data first.

Is Microsoft Copilot better than ChatGPT for Excel?

It depends on the task. Copilot is faster for simple, in-context operations because it works directly inside Excel without copy-pasting. ChatGPT is more capable with complex formulas, VBA generation, and multi-step logic. For heavy analytical work, ChatGPT (or another general LLM) paired with deterministic Excel skills still produces better results.

Can AI clean messy Excel data?

AI tools handle obvious cleaning tasks well: trimming whitespace, standardising dates, fixing case inconsistencies, and splitting combined columns. However, they struggle with subtle data quality issues like near-duplicates (e.g., 'Acme Corp' vs 'Acme Corporation'), domain-specific abbreviations, and context-dependent validation. Power Query in Excel remains the better tool for repeatable, auditable data cleaning.

Do I need to know Excel to use AI for Excel?

Yes. AI tools can speed up your work, but they cannot replace Excel knowledge. You need to know enough to evaluate whether the generated formula or VBA is correct. AI makes mistakes confidently. Users who cannot review AI output risk building spreadsheets on incorrect logic.

Practical Advice

Use ChatGPT for: Complex formula writing, VBA development, template design, and any task where you need to think through spreadsheet architecture. It is the most capable general-purpose AI for Excel across the full range of tasks.

Use Copilot for: Quick formula generation inside an existing spreadsheet, simple data transformations, and any operation where staying in-context (without copy-pasting between tools) saves time. It excels at speed, not depth.

Use Gemini for: Formula explanation, research-style questions about Excel features, and template brainstorming. It is a strong secondary option when ChatGPT is unavailable.

Use deterministic Excel for: Data cleaning that you will repeat weekly or monthly, financial models requiring circular references, reconciliation work, and any spreadsheet that will be shared with auditors or regulators. Power Query, named ranges, and well-structured formulas remain more reliable than any AI alternative for these use cases.

The best workflow is not AI alone or Excel alone. It is a hybrid: use AI to draft and brainstorm, then use deterministic Excel skills to validate, refine, and productionise. The users who get the most value from AI in Excel are the ones who already know Excel well enough to spot when the AI is wrong.