AI Tools Academy
Microsoft Copilot 0/20

Phase 2 · Microsoft Copilot · Level 2 · Practitioner

Copilot in Excel: formulas, analysis and editing your data

Walkthrough · 12 minLast checked against the live product: 14 July 2026

30-second recall from earlier lessons
You want to practise summarising and drafting with Copilot, but you have no paid licence. What's the sensible approach?
An AI assistant gives you a confident, well-written answer with a specific statistic and a link to a source. You need the figure for a board paper. What's the wisest next step?

By the end, you'll be able to…

  • Format data as a proper Excel table so Copilot can work with it reliably
  • Ask Copilot for formulas, insights and a chart in plain English, and check what it returns
  • Use Edit with Copilot to change a workbook directly, and know when to switch models

Why it matters

Excel is where Copilot is at its most impressive and its most fallible on the same screen: it can write a formula, spot a trend and build a PivotTable from a sentence, but it's more hit-and-miss with numbers than with words. This lesson gets you the wins safely: how to prepare your data so Copilot behaves, how to ask for formulas and analysis, and how to check a figure before a decision rests on it.

First, the licence and the table

Two things decide whether Copilot in Excel works for you. The first is the licence: the in-app Copilot on the Excel ribbon generally needs a Microsoft 365 Copilot licence (some consumer and Premium subscriptions include a version too). No Copilot button usually means no licence, not a fault. The second, which people skip and then blame the tool for, is the table.

Copilot works far better when your data is a proper Excel table: a tidy block with a single header row, one row per record, no blank rows or merged cells, and consistent types down each column. Select your data and use Insert → Table (or Format as Table), and Copilot suddenly has clean structure to reason over. Feed it a messy range and it guesses; feed it a table and it calculates.

A quick reassurance on access: if you don't see Copilot in Excel, it's licensing, not you. The free Copilot Chat route from Level 1 still works, so you can keep building the skill even without the in-app buttons.

This matters immediately with the Fernway sales data, which is deliberately messy: the Total is missing on some rows, Units is blank on others, Region has typos ("Sotuh", "Midlnads"), the dates are in mixed formats, and one Total is clearly wrong (a Starter Plan of 10 units at £49 shows as £49, not £490). Turning it into a table doesn't fix those problems, but it lets you ask Copilot to find them, which is the first job below.

Cleaning and checking with Copilot

Before analysis, get Copilot to surface the mess. This is one of its best Excel uses.

Ask Copilot to audit the dataCopilot
This table is our April to May sales. Find data-quality problems: blank Units or Total cells, Region values that look like typos, and any row where Units × Unit Price doesn't equal Total. List them with the row so I can fix them.

Why this works: It points Copilot at specific, checkable problems (blanks, inconsistent categories, totals that don't reconcile) rather than a vague 'check this'. Because each finding is verifiable in the sheet, you can confirm or dismiss it yourself, which is the safe way to use Copilot on data.

Copilot should flag the blank totals, the "Sotuh"/"Midlnads"/"Sotuh" region typos, the blank rep and region rows, and the £49 total that should be £490. Some of these you'll then fix by hand or ask Copilot to fix, but you decide, because you can see each one. This is the pattern for all Excel work with Copilot: it proposes, you verify against the cells.

Formulas in plain English

Copilot writes formulas from a description and explains what they do. You ask in words; it returns a formula you can insert into a new column.

Get a formula and an explanationCopilot
Add a column that recalculates the correct Total as Units × Unit Price, so I can compare it against the existing Total column and spot the wrong values. Give me the formula and explain it in one line.

Why this works: It describes the goal ('recalculate Total from Units and Unit Price') rather than trying to recall the syntax, and asks for an explanation so you can judge whether the formula is right. Understanding the formula is what lets you trust, or correct, it.

A formula is easy to check: read Copilot's one-line explanation, then eyeball a couple of rows. If the recalculated column disagrees with the original Total, you've found the bad data, and you learned the formula, rather than just pasting it.

Analysis, insights and charts

Point Copilot at the table and ask for analysis (trends, totals by group, a chart, a PivotTable) in plain language.

Analyse by group and visualiseCopilot
Using the corrected totals, show total sales by Region and by Product as a PivotTable, and add a bar chart of sales by Region. Tell me which region is strongest and which product sells best.

Why this works: It names the exact cut you want, total sales by region and by product, and asks for both a table and a chart, so you get a scannable answer plus something for a slide. Naming the grouping stops Copilot picking an arbitrary one.

Copilot builds the PivotTable and chart and states the headline. Here's the trap, and it's the whole lesson in one sentence: if the underlying totals are still wrong, so is the analysis. Copilot will confidently tell you which region "won" using whatever numbers are in the sheet, blanks and errors included. That's why the clean-up came first, and why you sanity-check the headline against what you know (does "North strongest" match the raw rows?).

Edit with Copilot: edit, plan and chat modes

The more agentic feature is Edit with Copilot, where Copilot changes the workbook directly, adding columns, applying formatting, building tables and PivotTables, all from your instructions. It runs in three modes worth knowing:

  • Edit (the default) makes changes to the workbook directly.
  • Plan proposes a plan first, so you approve the approach before anything changes.
  • Chat keeps Copilot's answers in the chat pane without touching your sheet, best when you just want advice.

For a real change to real data, Plan mode is your friend: you see what Copilot intends before it acts.

Use Plan mode before changing the sheetCopilot
I want to clean this table: fix the Region typos, fill blank Totals using Units × Unit Price where possible, and add a Month column from the date. Show me your plan before making any changes.

Why this works: Asking for a plan first, on a multi-step change, lets you catch a wrong assumption before Copilot edits live data. On anything you can't easily undo, previewing the steps is far safer than letting it act and hoping.

A note on choosing the model

In Edit with Copilot you can often choose which model does the work: typically Auto, a ChatGPT model from OpenAI, or a Claude model from Anthropic. Different models have different strengths, and it's worth trying another if one's answer looks off. One durable caveat for UK users: Anthropic (Claude) models are governed by an admin setting and, for UK and EU customers, are often off by default, so if you don't see Claude as an option, your organisation may simply not have enabled it. That's an admin choice, not a bug.

Try it now

Common mistakes

  • Feeding Copilot a messy range. Blank rows, merged cells and inconsistent columns make Copilot guess. Format as a proper table first and its answers get markedly more reliable.
  • Trusting analysis built on dirty data. Copilot happily analyses whatever's in the sheet, blanks and errors included. Clean and check the data before the "which region won" question, or the answer is confidently wrong.
  • Pasting a formula you don't understand. Ask for the one-line explanation and read it. An unexplained formula you can't sanity-check is a future mystery bug.
  • Letting Edit mode loose on data you can't undo. For multi-step changes on real data, use Plan mode to preview the steps first. Acting blind on live figures is how a bad edit propagates.
  • Over-trusting a confident number. This is the big one: Copilot states totals and "insights" with total assurance, and it is more error-prone with numbers than with prose. A figure a decision rests on must be checked in the cells yourself. Asking Copilot "are you sure?" is not a check, because it can be confidently wrong twice.

Keeping current

Copilot in Excel is changing quickly: modes, model choice and the exact buttons all move. Microsoft has also settled the naming of the agent-style editor here on Edit with Copilot, the name used throughout this lesson, so if you meet an older label for the same feature in other material, treat it as the same thing. The durable habits (table first, verify every figure, plan before editing live data) will outlast any specific screen. On licensing: during 2026 Microsoft moved the in-app Copilot features inside Word, Excel, PowerPoint and OneNote behind a full Microsoft 365 Copilot licence; the free Copilot Chat continues, and Copilot in Outlook remains available. For current detail see Microsoft's Get started with Copilot in Excel and Choose your model when editing with Copilot in Excel. Accurate as of 14 July 2026.