How to Use Copilot in Excel for Data Analysis You Can Trust

What Copilot in Excel Actually Does

Copilot in Excel is an AI assistant built into the spreadsheet that edits your workbook using Excel’s own tools — formulas, tables, charts, PivotTables and formatting — rather than producing a separate document. Everything it makes stays editable, because it is native Excel underneath.

Microsoft groups its abilities into a handful of jobs:

• Editing worksheets: adding and renaming sheets, changing values and ranges, applying conditional formatting and styles
• Generating data and formulas, including calculations that span several sheets
• Creating charts, PivotTables and shapes with live links back to the source data
• Identifying insights — answering questions about your data as summaries, trends or outliers
• Highlighting, sorting and filtering what is already there

It also offers three modes: edit, where it changes the workbook directly; plan, where it proposes an approach before touching anything; and chat, where the reply stays in the pane. If you have read about “Agent Mode,” that is the multi-step editing behaviour that has since been folded into this unified experience — you may still see the old name in places. For the wider picture of what Microsoft Copilot is across the whole Microsoft 365 suite, the tool-by-tool guide covers the rest.

Check Your Licence Before Anything Else

If the Copilot button is not in your ribbon, the cause is usually licensing, not a bug. Copilot in Excel needs one of four things, and a plain Microsoft 365 subscription is not one of them.

One correction worth making loudly, because a lot of guides still get it wrong: Copilot Pro is gone. Microsoft stopped selling the standalone individual add-on, and support for existing subscribers ended in August 2026. If an article tells you to buy Copilot Pro for spreadsheet work, it was written before that happened. Individuals now go through Microsoft 365 Premium instead. Our breakdown of free versus paid Copilot walks through which tier earns its cost for which kind of work.

Inside the app, Microsoft labels your experience so you can tell what you have. A Premium label means the full add-on with priority access; a Basic label means standard access without it. You can confirm the current requirements in Microsoft’s licence requirements for Copilot in Excel, which is the page I check before believing anything else on this subject.

The Grid-First Method Explained

Grid-First means every number Copilot gives you has to arrive on the sheet as a formula or object that Excel calculated and you can trace — never as a figure typed into the chat pane. That single rule removes most of the anxiety about AI and spreadsheets, because it moves the arithmetic back to the calculation engine that has been getting it right since 1985.

It is four moves, and it is the approach we teach at PromptPeakAI for any spreadsheet work with an AI assistant.

The difference this makes is not subtle, and it shows up the moment anyone questions your figure.

Shape Your Data Before You Prompt

Copilot reads structure before it reads your request, so the first real step in how to use Copilot in Excel is not a prompt at all. Ten minutes spent on shape saves an hour of arguing with it later.

Select any cell in your data and press Ctrl+T to convert the range into a proper Excel table, then tick the box confirming your first row is headers. Microsoft has relaxed this over time — a plain range can work if it is tidy enough — but a named table is still the version that behaves predictably, and naming it gives you something to point at in prompts.

Unmerge every merged cell in the data, especially in headers. Merged cells break the column mapping Copilot relies on, and the failure is quiet: it does not error, it just silently ignores columns. Give every column a short, distinct, human-readable name. “Q3_Rev” beats “Column F” and beats “Revenue (net of returns, excl. VAT)” too.

In the Formulas tab, confirm Calculation Options is set to Automatic. Copilot’s editing only works when it is — a workbook left on Manual will refuse edits with a message that does not explain itself.

How to Use Copilot in Excel for Analysis

Everything in how to use Copilot in Excel well comes down to one habit: name the furniture. Copilot cannot see what you are looking at the way a colleague leaning over your shoulder can, so every table name and column name you supply removes a guess it would otherwise make.

Start with orientation rather than an answer. This prompt makes Copilot tell you what it can see, which is how you catch a misread table before it becomes a wrong number.

You are a data analyst helping me review a spreadsheet I own.

The table is named [table name]. The columns that matter are [column A], [column B] and [column C].

Do this:
1. Summarise what this table contains, in three sentences.
2. List the five questions this data can actually answer.
3. Flag any column where the values look inconsistent or incomplete.

Do not calculate any totals in your reply. If a number is needed, tell me which formula would produce it.

That picker in the top-right corner is worth a moment. Copilot in Excel supports the latest models from both Anthropic and OpenAI, with Auto chosen by default. Two things people trip on: the picker needs either a commercial Microsoft 365 Copilot subscription or Microsoft 365 Premium, and in enterprise tenants an admin has to enable Anthropic as an approved provider first. Your choice also lasts only for that session — close Excel and it reverts to the default. Honestly, leave it on Auto until you have a specific reason not to.

Once Copilot has described your table back to you correctly, ask it to build. The structure below is the Logic System — role, goal, context, format applied to a spreadsheet, and it works the same way here as anywhere else.

Add a calculated column to the table named [table name].

Call the column "[new column name]".

The rule: if [column name] is greater than [value], return "[result A]"; if it is between [value] and [value], return "[result B]"; otherwise return "[result C]".

Write this as a native Excel formula in the column. Show me the formula and explain what each part does before you insert it.

That last line — show me the formula before you insert it — is the whole Grid-First idea in one sentence. Microsoft’s own advice runs in the same direction: be specific, name your columns and ranges, and refine rather than restart. Their prompting guidance for Copilot in Excel is short and worth the four minutes.

Five Workflows Worth Stealing

These are the tasks where the time saving is real rather than theoretical, because each one replaces fiddly work rather than thinking work.

Standardise a messy export

CRM and payroll exports arrive with the same value spelled four ways. Copilot is genuinely good at pattern work like this — the trick is forbidding it from guessing.

The table named [table name] has inconsistent values in [column name].

Add a new column called "[clean column name]" that standardises every entry to [target format, e.g. full country name in title case].

Rules:
- Leave the original column untouched.
- Where a value is too ambiguous to standardise confidently, return "CHECK" rather than guessing.
- Show me the list of distinct values you found before you write anything.

The same principles apply outside Excel too, if you work across tools — our guide to cleaning messy data with AI covers the tool-agnostic version.

Build the summary table

This is the one that converts sceptics. A PivotTable that would take you fifteen minutes of dragging arrives in about twenty seconds, and because it is a real PivotTable, it refreshes.

Using the table named [table name], build a PivotTable on a new worksheet.

Rows: [dimension, e.g. Region].
Values: total [measure], average [measure], and share of total [measure].
Sort: highest total first.

Then add a column chart of the same summary beside it, titled "[chart title]".

Copilot will pick a chart type for you, and its default is not always the right one. If you are unsure whether a bar, line or stacked chart tells your story, choosing the right chart for your data is the faster fix than iterating blind.

Flag the outliers

Rather than asking “what looks unusual,” which invites a vague answer, make the comparison explicit and put it in a column.

In the table named [table name], find rows where [column name] is unusually high compared with the rest of its [grouping column] group.

Do this by writing a formula column called "Flag" that compares each row against its group average. Show me the formula first.

Then apply conditional formatting to highlight the flagged rows, and tell me how many rows were flagged.

Get the formula you cannot remember

Nested conditionals and lookups are where most people lose an afternoon to a misplaced bracket. Describe the business logic in plain English and let Copilot write the syntax — then read it back before trusting it. If you do not have a Copilot licence, writing Excel formulas with ChatGPT gets you most of the way there by copy-paste.

Write the executive summary

Once the numbers are on the grid and verified, drafting the narrative is a legitimate use of AI — you are asking it to write prose about figures it can read, not to invent the figures.

You are writing for [audience, e.g. my finance director], who has ninety seconds.

Read the summary table on [worksheet name] and write four sentences covering:
- the headline number and how it moved,
- the single biggest driver of that movement,
- one thing that looks wrong or worth checking,
- what decision this supports.

Use plain English. Do not use any adjective that is not supported by a number in the table.

For the fuller craft of that final step, turning raw numbers into a data narrative goes deeper than a prompt can.

Excel, Chat, or Analyst Agent

Three different Copilot surfaces can touch your data, and picking the wrong one is a common source of “it did not work.” The short rule: use Copilot in Excel when you want the workbook changed, and Copilot Chat when you want an answer.

The Analyst agent is reached through Copilot Chat rather than the Excel ribbon, and availability depends on what your organisation has switched on. If it is not there for you, that is a tenant decision, not a fault.

When Copilot in Excel Goes Wrong

Almost every complaint about how to use Copilot in Excel when it misbehaves falls into one of six buckets, and none of them require reinstalling Excel.

One thing worth knowing before you experiment on a shared file: Copilot’s edits go straight into the workbook, and once saved, anyone with access sees them, including co-authors working at the same time. Version history is your undo. Turning AutoSave off is a reasonable way to test changes without committing them — contrary to a lot of older guidance, Copilot runs happily either way.

Your Data and Who Sees It

The question underneath most hesitation is simple: does putting company data in front of Copilot expose it? The honest answer depends on one thing, and it is not your price tier.

The boundary that matters is which account you are signed in with — a work or school account, or a personal Microsoft account. Those two sit under different agreements and different published privacy commitments. Signing into a personal account to work on a company file is the mistake that actually creates risk, and it is a surprisingly easy one to make on a home laptop.

For the wider version of this — what the tool can reach across your mailbox, files and calendar, and who in your organisation can see it — our guide to what Copilot can see in your work data is the deeper read.

What Copilot Still Cannot Do

Every guide should have this section, and most do not. Microsoft’s own documentation is refreshingly blunt here: Copilot can misinterpret information and produce inaccurate results, and they advise against relying on it for decisions in sensitive areas including finance, legal and medical matters. In a finance-heavy tool, that is worth reading twice.

One feature you cannot use yet

You may have read about a COPILOT worksheet function that runs AI prompts inside individual cells. It is real, but it is not generally available — it sits in Microsoft’s Frontier and Insider programmes, with broad release still ahead, and it carries hard limits on how many calls can run and which sensitivity labels it will work under. If it is not in your Excel, nothing is broken. Treat it as something to revisit later rather than a workflow to plan around.

Learning how to use Copilot in Excel well is mostly learning where to stop trusting it. Shape the data, name the columns, insist the answer lands on the grid as something Excel calculated, and check one row by hand before you send anything onward. Do that consistently and the tool stops being a source of numbers you cannot explain, and starts being the fastest analyst on your team — the one who still needs your review.