How to Use Copilot in Excel to Write and Fix Formulas

New to the assistant built into Office? Our free short course AI in the Tools You Already Have shows where Copilot sits in Outlook, Word, Excel and Teams, and its Excel lesson is about reading the formula Copilot wrote before trusting the number.

Which plans include Copilot in Excel

These are the US list prices on Microsoft's pricing pages when we checked them on 23 September 2026. Copilot inside Excel comes with a Microsoft 365 subscription that includes it, not with the free Copilot chat app.

Our pages on Microsoft Copilot for home users and Microsoft 365 Copilot for businesses list every plan, including Business Premium with Copilot at $32.00 per user a month paid yearly.

A paid plan is not the only condition. Microsoft's Get started with Copilot in Excel page warns that Copilot may be missing because your subscription does not include it or your organisation has turned it off. The device matters too: Copilot can edit your sheet on Windows, Mac and the web, while on iPad and iPhone editing was still rolling out and Android has chat only.

Step 1: Turn the data into a table

Click anywhere in your data and press Ctrl+T, then confirm the header row. Each column now has a name Copilot can use in its formulas, and a formula column added to the table extends to every row. Keep one header row, one record per row and no blank rows in the middle. If the data arrived as a messy export with duplicates and run-together names, tidy it before asking for formulas; our guide to cleaning a messy CSV and building a PivotTable with AI covers that part.

We will use a small invoice log for every step. Column A is Invoice, B is Client, C is Amount, D is Due date and E is Paid date, which stays empty until the client pays:

Work out the answers you expect from these four rows before asking for anything. You will need them in Step 3.

Step 2: Open Copilot and choose the mode

Microsoft's instructions now place the Copilot icon in the lower-right corner of the Excel window. On older builds it sits on the Home tab, and on Windows you can right-click the floating icon and choose Move to Ribbon to put it back there.

Copilot in Excel has modes. Chat mode reads the workbook and answers without changing anything, which suits questions about the data. To have Copilot type the formula into the sheet, use edit mode. If a request goes wrong partway through, the Stop button ends it.

Step 3: Describe a formula column and read what comes back

Name the columns, the new column's name and the edge cases. For the invoice log:

In this table, add a column called Days late. If Paid date is empty,
leave the cell blank. Otherwise show how many days after Due date the
invoice was paid, and show 0 if it was paid on or before the due date.

Copilot proposes a formula along these lines and explains each part:

=IF([@[Paid date]]="", "", MAX(0, [@[Paid date]]-[@[Due date]]))

[@[Paid date]] means "the Paid date in this row", which is why the same formula works all the way down. Before you keep it, compare the new column with your own answers: INV-101 should show 0 (paid two days early), INV-102 should show 8, INV-103 should be blank and INV-104 should show 0. Two rows do most of the checking. If INV-101 shows -2, the MAX part was dropped. If INV-103 shows 0, the blank rule was lost, and an unpaid invoice now looks as if it was paid on time.

Step 4: Ask for a single-cell formula

For one figure rather than a column, say which cell gets it. Microsoft's support page describes this case: Copilot suggests a formula for a precise calculation in one cell and explains how it works.

In cell J2, total the Amount of invoices that have no Paid date.

A sound answer is =SUMIFS(Invoices[Amount], Invoices[Paid date], ""), where Invoices is the table's name and the empty quotes match blank cells. By hand, only INV-103 is unpaid, so J2 should read 400. Ask for the result in a cell rather than as a sentence in the chat pane: a number in a reply has no formula behind it for you to inspect.

Step 5: Pull a value from another sheet

Copilot can also build a lookup column. Microsoft says it suggests a formula such as XLOOKUP for this. Suppose a second sheet, Clients, holds a table of the same name with Client in column A and Terms (payment terms in days) in column B: Harbor Print in row 2, Linden Cafe in row 3 and Oak & Vine in row 4.

Add a column called Terms that looks up each client's payment terms
from the Clients table.

You get something like =XLOOKUP([@Client], Clients[Client], Clients[Terms]). Harbor Print and Oak & Vine fill in. Linden Cafe returns #N/A, which takes us to fixing.

Step 6: Ask Copilot to explain and fix a formula

Select the cell with the error and ask Copilot to explain the formula in the selected cell, or name the cell in your prompt so you do not have to click it. Then add what you see and what you expected:

Explain the formula in cell G3. It returns #N/A, but Linden Cafe is
on the Clients sheet with 14-day terms. What is wrong?

The likely diagnosis is a stray space. XLOOKUP looks for an exact match unless told otherwise, and "Linden Cafe " with a trailing space is a different value. You can confirm it yourself: =LEN(Clients!A3) returns 12 for a name that should have 11 characters. Fix the source cell rather than the formula, then check that G3 shows 14.

Be wary when the fix Copilot offers wraps the formula in IFERROR with 0. That hides the missing match, and an unknown client then shows terms of 0 days. If you want a fallback, XLOOKUP's fourth argument can show text such as "Check client" instead, so the problem stays visible. This explain-first habit works on any formula, including a long one you inherited from a colleague.

Check the formula, not only the number

Microsoft's own advice is to review, edit and verify anything Copilot creates. In practice, click a cell in the third row or lower and read the formula bar. Our course lesson on Copilot in Excel explains why: a reference anchored to the first row gives a correct answer in row two and wrong answers below it, with no error to warn you.

If you do not have a Copilot plan, the same describe-then-test method works in a chat window. Our guide to using ChatGPT to write and fix an Excel formula covers what to paste and how to test the reply, since a chat assistant does not see the workbook you have open.

If Copilot is missing or greyed out

Check the plan table first, then the account: in Microsoft 365 Family only the subscription owner gets Copilot. At work, ask your IT team whether Copilot is enabled for you. If it is greyed out on a workbook saved on your computer, save the file to OneDrive or SharePoint and switch AutoSave on. Several third-party guides list that as a requirement for the desktop app, though Microsoft's current Get started page does not mention it.

Start with one column

Pick a column you would otherwise search the syntax for, describe it to Copilot, and check row three before you trust it. To get more from the assistants already in your work tools, start learning with our AI courses for work, where every lesson is free.