How to use AI in Excel with Copilot and Analyze Data
Three ways to use AI in Excel: paid Copilot writes formulas and analyzes data, free Analyze Data finds trends, and ChatGPT explains any formula.
You can bring AI into Excel three ways, and only one of them costs extra. Microsoft 365 Copilot writes formulas from plain English, summarizes data, and builds charts and PivotTables for you, but it needs a paid license. The built-in Analyze Data tool gives you AI-style insights for free, and a general chatbot like ChatGPT or Claude will write and explain any formula you paste back into the sheet. Pick the route that matches what you already have, and don’t pay for Copilot until you know the free tools can’t do the job.
Copilot in Excel: the paid AI built into the ribbon
Copilot is the deepest option because it reads your open workbook and can change it. Open the file, select the Copilot button on the Home tab (or the Copilot icon in the lower-right corner of the window), and a chat pane opens on the right. From there you type requests in plain English and Copilot answers against whatever tables it can see.
Two setup rules matter before it will work. First, per Microsoft’s FAQ on Copilot in Microsoft 365 subscriptions, the file must have AutoSave on and live on OneDrive or SharePoint — Copilot does not run on an unsaved local file. Second, it works best when your data is a real Excel table, so select your range and press Ctrl + T first.
Once it can see clean data, Copilot will:
- Write formulas from a description. Ask something like “add a column that gives each row a 10% bonus on Sales,” and it returns the formula, explains it, and offers to insert the column for you.
- Explain and troubleshoot existing formulas. Paste or point at a cell and ask what it does or why it errors.
- Summarize and surface insights — totals, trends, and outliers described in sentences.
- Highlight, sort, and filter to focus on the rows that matter.
- Suggest and build charts and PivotTables, with links back to the source data.
Microsoft’s Get started with Copilot in Excel page covers the full skill list. The honest caveat is cost: Copilot is included only for the owner of a qualifying Microsoft 365 Personal, Family, or Premium subscription (and draws on a monthly pool of AI credits), while business users buy the Microsoft 365 Copilot add-on separately. If Excel keeps hanging when you turn it on, that is a performance problem, not an AI one — see how to fix Excel not responding.
Analyze Data: free AI insights with no Copilot license
Analyze Data, once called Ideas, is the AI feature almost everyone already owns. It ships with Microsoft 365 Excel on Windows, Mac, and the web, and needs no Copilot subscription. Select any cell inside your data, then on the Home tab choose Analyze Data. A pane opens with charts, ranked summaries, and trend callouts it generated automatically.
The useful part is the question box at the top of that pane. Type a plain-English question such as Total sales by region or Which month had the most orders, press Enter, and it answers with a chart or a small table you can insert into the sheet with one click. Microsoft’s Analyze Data in Excel reference lists what it looks for: Rank (what dominates), Trend (steady change over time), Outlier (anomalies), and Majority (one factor driving a total).
It is fussier about data than Copilot. Give it a single row of unique, non-blank headers, no merged cells, and one data type per column, and format the range as a table so it reads the shape correctly. It caps out at 1.5 million cells and needs an internet connection because the analysis runs in the cloud. When a suggestion is what you wanted, Analyze Data can drop a ready-made PivotTable straight into a new sheet — handy if you don’t yet know how to create a PivotTable in Excel by hand.
Write and explain formulas with ChatGPT or Claude
If you have no paid Copilot license, a general chatbot is the strongest free way to get formulas. It never touches your spreadsheet — you describe the problem, it returns a formula, and you paste that formula in. Be specific about your layout: name the columns, say which cell should hold the result, and mention your Excel version if a function might be new.
A prompt like “In Excel, column A has names and column B has scores. Write a formula in D2 that looks up a name in D1 and returns its score” gets you a working XLOOKUP or VLOOKUP in seconds, usually with a line-by-line explanation. This is also the fastest way to decode an inherited monster formula: paste it and ask what it does, or ask why it returns #N/A. It pairs well with our plain-English guide to how to use VLOOKUP in Excel when you want to understand the result rather than just copy it.
The trade-offs are real. The bot can’t see your live data, so it may guess at ranges or assume a function your build doesn’t have — always test on a copy first. And privacy is on you: don’t paste confidential rows, customer records, or anything you couldn’t email externally, since the text leaves your machine. For turning a document into numbers you can chart, the same skills apply as in how to summarize a PDF with AI — extract first, then paste the clean table.
What each tool can do
Match the tool to the task before you reach for a subscription. Analyze Data and a chatbot together cover most everyday needs for free; Copilot earns its price mainly when you want the AI to edit the workbook itself.
| Task | Copilot in Excel (paid) | Analyze Data (free) | ChatGPT / Claude (external) |
|---|---|---|---|
| Write a formula from plain English | Yes, and inserts it | No | Yes, you paste it in |
| Explain what a formula does | Yes | No | Yes |
| Summaries, trends, and insights | Yes | Yes | Only from pasted data |
| Suggest charts | Yes | Yes | Describes, you build it |
| Suggest or build PivotTables | Yes | Yes | Gives you the steps |
| Change the sheet directly | Yes | Inserts chosen result | No |
| Reads your live workbook | Yes | Yes | No, paste only |
| Needs a paid Copilot license | Yes | No | No (free tiers exist) |
| Works with no internet | No | No | No |
Licenses, privacy, and where AI still gets it wrong
AI in Excel is fast, not infallible. It can invent a plausible-looking formula that quietly sums the wrong column, so treat every result as a draft and check the numbers against a total you already trust. Keep the AI to new columns and copies until you are sure, and clean your data first — duplicate rows skew every summary, which is why it pays to know how to remove duplicates in Excel before you ask any tool to analyze it.
One moving target worth flagging: Microsoft is retiring the experimental =COPILOT() worksheet function in September 2026 because it could return different answers from the same inputs, and is steering people to the Copilot side pane instead, as Refonte Learning reports. If a tutorial tells you to type =COPILOT(...) into a cell, it is out of date — use the chat pane described above.
| Problem | Likely cause | Fix |
|---|---|---|
| No Copilot button appears | No qualifying license, or Office is outdated | Sign in as the subscription owner and update Excel |
| Copilot says it can’t read the data | AutoSave off or file not on OneDrive | Turn on AutoSave and save to OneDrive or SharePoint |
| Analyze Data returns nothing useful | Merged cells, blank rows, or mixed data types | Fix headers and format the range with Ctrl + T |
A chatbot’s formula throws #REF! | It guessed your ranges or a newer function | Give exact cell references and your Excel version |
Start free: run Analyze Data on a clean table, and lean on ChatGPT or Claude for any formula it can’t write, pasting the result into a copy and testing it before you trust it. If those two consistently fall short of what you need — especially editing large workbooks in place — that is the point where a Microsoft 365 Copilot license starts to pay for itself, and not a step before.