"VLOOKUP isn't working." "Every week I redo the same aggregation by hand." "I can't write VBA, but I want to automate this." Almost every Excel pain point can now be solved with AI. This guide explains how to combine ChatGPT and Microsoft 365 Copilot in Excel to speed up formulas, debugging, VBA, and data cleanup, based on official information available as of May 2026. Ready-to-paste prompt templates are included.
What Changes When You Bring AI Into Excel
Adding AI to Excel work does not just make things "faster" — it changes the shape of the work itself. Three shifts in particular tend to happen.
1. Massive Time Savings: 30 Minutes Becomes 3
Take a task like "merge records across sheets, remove duplicates, and aggregate sales by year and month." What used to take 30 minutes of trial-and-error with formulas now takes a few minutes: ask in natural language, and AI returns formula suggestions and Python preprocessing code. The biggest change is that the loop of "have an idea → try it → fix it" now runs without leaving Excel.
2. Removing Key-Person Dependency: Personal Tricks Become Repeatable Procedures
Aggregation logic and formula combinations that used to live only in a veteran's head can be turned into documentation by asking AI to explain them. Because AI can immediately describe "what this formula does" and "why steps are ordered this way," handover and training costs drop sharply.
3. Lower Training Costs: A 24/7 Coworker You Can Ask Anything
Until recently, when staff got stuck on a formula or VBA, they had to ask a senior colleague or hunt through search engines. AI answers instantly, around the clock, and tailors its answers to your actual data. For onboarding new hires and reskilling non-IT teams, AI is one of the most cost-effective "first teachers" available.
ChatGPT vs. Microsoft 365 Copilot in Excel: Which Should You Use?
"Use AI to make Excel more efficient" actually splits into two very different approaches. Understanding the distinction up front saves a lot of wasted experimentation.
Aspect | ChatGPT (OpenAI) | Microsoft 365 Copilot in Excel |
|---|---|---|
Where it runs | Browser / desktop app (outside Excel) | Inside Excel (in the ribbon) |
Strengths | Generating formulas and VBA, free-form data preprocessing, explanations | Analyzing and visualizing on-sheet data directly, formula and chart suggestions, running Python |
File requirements | Upload-based | Files on OneDrive / SharePoint with AutoSave on |
Main plans | ChatGPT Free / Plus / Team / Enterprise | Copilot Pro (consumer) / Microsoft 365 Copilot (business add-on) |
Handling of business data | Team / Enterprise are not used for training by default | Stays within tenant boundary; respects Microsoft Graph permissions |
A rough rule of thumb:
- Want to write formulas or VBA, or clean up CSVs → ChatGPT
- Want to ask questions about and visualize the data in an open Excel file directly → Microsoft 365 Copilot in Excel
- Handling sensitive internal data → standardize on the business plan your company has contracted (ChatGPT Team / Enterprise, or Microsoft 365 Copilot)
ChatGPT's Data Analysis Feature (formerly Code Interpreter)
ChatGPT lets you upload Excel and CSV files for direct analysis and processing. The current name in OpenAI's official help center is "data analysis"; this is the same lineage as the feature previously known as "Advanced Data Analysis" or "Code Interpreter." Python runs in a sandboxed environment to read, clean, visualize, and even output a new Excel file.
Operational notes as of May 2026:
- Available plans: ChatGPT Plus / Team / Enterprise / Edu and other logged-in plans. Free may have partial access, but with stricter limits on uploads and runs. For business use, Team or Enterprise is recommended.
- Data training: By default, data from Team / Enterprise / Edu / Business is not used to train models (per OpenAI's "Enterprise privacy" page). On Free / Plus, you can disable training (separate chat history from training) in settings.
- Supported files: .xlsx, .xls, .csv, plus images, PDFs, text, and more.
Microsoft 365 Copilot in Excel
Per Microsoft Learn documentation, Copilot in Excel handles "formula and chart-type suggestions, and data insight generation (the Draft feature)." On top of that, Copilot in Excel with Python generates Python from natural-language requests and runs it as =PY() cells inside Excel (no local Python install required; it runs on a cloud runtime via Anaconda).
Key prerequisites:
- Files must be saved on OneDrive for Business or SharePoint with AutoSave enabled (local files are not supported).
- The consumer "Copilot Pro" plan and the business "Microsoft 365 Copilot" plan (an add-on license to Microsoft 365 E3 / E5 / Business Standard / Premium) are different. To let Copilot reference your organization's data, you need the latter.
- Microsoft 365 Copilot is designed to integrate with Microsoft Graph and respect each user's permissions (data does not leave the tenant boundary).
Four Major Use Cases and Concrete Prompts
Below are four use cases with the highest day-to-day demand, each paired with a practical prompt example. The wording is generic enough to work with both ChatGPT and Copilot.
Use Case 1: Generating Formulas
Just describe "what you want to do" in plain language and AI suggests the right combination of functions — VLOOKUP, XLOOKUP, INDEX/MATCH, FILTER, LET, LAMBDA, and so on, picking what fits your case.
Prompt:
"Sheet A column B contains product codes. Sheet B has product codes in column A and unit prices in column B. In Sheet A column C, look up and display the unit price from Sheet B. If the product code is not found in Sheet B, show 'needs review.' Please write this as an Excel formula. Also include an IFERROR + VLOOKUP version for environments where XLOOKUP is unavailable."
Specifying these four things — column structure, what you want to do, exception handling, and environment constraints (older Excel?) — usually gets you a paste-and-run formula on the first try.
Use Case 2: Debugging Formulas
When a formula "should work but doesn't," paste the error message and the formula and AI returns likely causes and a fix.
Prompt:
"The following Excel formula returns #N/A. Please tell me the cause and a corrected version. Data: column B contains product codes (text type), and column A in the lookup target also contains product codes (number type). Formula: =VLOOKUP(B2, Sheet2!A:C, 3, FALSE)"
Type mismatches, missing absolute references, array orientation, circular references — AI checks the kinds of pitfalls humans tend to miss.
Use Case 3: Writing VBA / Macros
You don't need to learn VBA from scratch — describe "what you want to do" and AI writes the code. The important inputs are target sheet name, column names, expected row count, and how often it runs.
Prompt:
"Please write Excel VBA. The active workbook has a 'Sales' sheet with dates in column A and amounts in column B. Aggregate them monthly and output to a new sheet called 'Monthly' with column A = year-month (YYYY-MM) and column B = total amount. If 'Monthly' already exists, clear its contents before writing. Please include plenty of inline comments in English."
Always test the returned code in a copy of the workbook, and verify it doesn't break on edge cases (empty cells, mixed-in text, etc.) before running on production data.
Use Case 4: Data Cleanup and Record Matching
The biggest payoff is in turning messy CSVs or Excel files into "analysis-ready" data. ChatGPT's data analysis feature, or Copilot in Excel with Python, can handle preprocessing that would be hard to express as a formula — all in plain language.
Prompt:
"Please clean the attached CSV (customer list). Requirements: (1) Normalize full-width vs. half-width characters (katakana to full-width, alphanumerics to half-width). (2) Phone numbers as 11- or 10-digit strings without hyphens. (3) Strip trailing '株式会社' or '(株)' from company names and store the legal-entity type in a separate column. (4) Deduplicate by email address, keeping the row with the latest update timestamp. Output as an .xlsx file."
How to Feed Excel / CSV Files Into ChatGPT
Here is the basic flow for ChatGPT's data analysis feature. Once you're used to it, you can start in 30 seconds.
- Open ChatGPT (Plus / Team / Enterprise) and start a new chat.
- Use the paperclip (or +) icon at the left of the input box to upload your .xlsx / .xls / .csv file.
- Start with: "First, tell me the structure of this file. List each column's meaning, type, missing-value status, and row count." This gives the AI situational awareness.
- Then add prompts for cleaning, aggregating, or visualization.
- When the result looks right, ask: "Please make the cleaned data available as an .xlsx download" and grab the file.
File sizes up to a few tens of MB per file are typically fine, but for very large row counts the safe approach is sample first → expand to full data.
Handling Business Data with Confidential Information
The single biggest stumbling block in AI x Excel is the question "Is this data OK to send outside the company?" The fundamental rule first: never paste personal information, customer/partner secrets, or non-public internal information directly into a free generative-AI service or a personal paid plan. Even if your company has not yet finalized policies, treat the following three points as the absolute minimum.
- Use a corporate tenant for business data: ChatGPT Team / Enterprise, or Microsoft 365 Copilot. Per OpenAI, Team / Enterprise / Edu / Business are not used for training by default. Microsoft 365 Copilot operates within your organization's tenant boundary.
- Do not handle confidential data on consumer plans (ChatGPT Free / Plus, Copilot Pro): keep those for personal use or testing with synthetic data.
- Make masking a habit: replace names, company names, phone numbers, and amounts with dummy values when they aren't essential to the request. Remember that what you ask AI for is "the processing logic," not "the actual data."
When formally adopting AI as a company, documenting the terms of use, privacy settings (training opt-out, retention), audit-log policy, and the list of "do-not-input" data dramatically reduces the judgment burden on the front line.
Common Stumbling Blocks and How to Handle Them
From repeated implementation work, here are the typical traps.
1. Pasting "the formula that doesn't work" and stopping there
Sending only the formula leaves the AI guessing about column layout, data types, and the expected result. The rule of thumb: send "three sample rows + what you want + the error message" together.
2. Applying AI suggestions to production data without verification
Especially for VBA or anything that deletes data, always test in a copy of the workbook first. AI sometimes returns code that looks plausible but is subtly wrong.
3. Not checking file format and storage location
Microsoft 365 Copilot in Excel only works on files saved to OneDrive / SharePoint with AutoSave enabled. The fact that "Copilot doesn't respond on locally saved files" is by design.
4. Vague prompts that produce different answers each time
"Aggregate this nicely" leaves room for interpretation that varies per AI. Get into the habit of always specifying column names, granularity (daily / monthly), output format, and exception handling — reproducibility improves dramatically.
5. Trying to delegate everything to AI
AI cannot take responsibility for the final check. Especially for anything involving amounts, dates, or names, build a workflow where a human spot-checks the output.
Prompt Templates You Can Use Tomorrow
Below are templates you can copy as-is and fill in the blanks for your situation.
Use Case | Prompt Template |
|---|---|
Generate a formula | "Please write an Excel formula. Sheet layout: column [ ] holds [ ], column [ ] holds [ ]. Goal: [ ]. Exception handling: [ ]. Provide both a version that works in Excel 2016 and a modern version (XLOOKUP / LET allowed)." |
Debug a formula | "The formula below returns [error name]. Please give three candidate causes and three corrected versions. Data types: [ ]. Column layout: [ ]. Three sample rows: [ ]. Formula: [ ]" |
Write VBA | "Please write Excel VBA. The target workbook has a [ ] sheet with [ ] columns. Perform [the desired processing] and output the result to [ ]. Approximately [ ] rows of data. Include plenty of English comments and proper error handling (On Error)." |
Clean data | "Please clean the attached file (.xlsx / .csv). Requirements: (1) [ ], (2) [ ], (3) [ ]. Output the cleaned file as .xlsx. Also include a short English summary of the cleaning logic." |
Pivot summary | "Please build a pivot summary of this data with rows = [ ], columns = [ ], and values = sum of [ ]. Fill blank cells with 0 and add subtotals and a grand total. Output to a new sheet." |
Explain a formula | "Please explain step by step, in plain English, what the Excel formula below does. Cover the meaning of each argument and common pitfalls. Formula: [ ]" |
Record matching | "Match the two attached lists (A and B) on key [ ]. Absorb notation differences (full vs. half width, '株式会社' / '(株)', presence of spaces). Output three sheets: exact match, fuzzy match, no match." |
Narrative report | "From this monthly aggregate data, summarize the top three topics (rose / fell / notable shifts) for an internal report, three lines each in English. Use only numbers from the source data and clearly separate any inference." |
The trick when using these is to specify "column names, granularity, exceptions, and output format" as a single set every time. That alone makes the output dramatically more consistent.
Related Topics
For broader thinking on using AI across the business, beyond Excel, and on designing safe ChatGPT use at work, see these companion articles.
- A Practical Guide to Using ChatGPT Safely at Work
- 10x Your Work with AI: The Big Picture and How to Get Started
How Mihata Helps
Mihata supports SMEs with "AI x Excel / business efficiency" hands-on engagements. We cover end to end: (1) on-site interviews to find automation candidates, (2) selecting between ChatGPT and Microsoft 365 Copilot and drafting internal guidelines, (3) building a prompt-template library tailored to recurring work, and (4) capturing formula and VBA recipes as internal knowledge. We welcome conversations even at the "I don't know where to start" stage.
Summary
The essence of AI x Excel isn't a flashy new tool — it's that "have an idea → try it → fix it" can now run as a continuous loop without leaving Excel. Three actions you can take today:
- Pick the single most time-consuming task among your current formulas, VBA, or data cleanup work, and try one of the templates in this article.
- Handle confidential data only inside ChatGPT Team / Enterprise or Microsoft 365 Copilot. Don't use consumer plans for business data.
- Stash the prompts that worked well in an internal wiki or shared doc, turning "individual tricks" into "team assets."
The Excel + AI combination has moved past the stage of "early movers gain an advantage" and into the stage of "organizations that don't adopt fall behind." Start with a single small task today.