Copilot + Excel

How to Automate Excel Tasks with Microsoft Copilot

Q: What is the COPILOT function in Excel?

COPILOT was a cell-level formula function that called the Copilot language model directly from inside a worksheet, with the syntax =COPILOT(prompt, [context_range]). For example =COPILOT('Classify sentiment as positive, neutral, or negative:', A2) returned a one-word classification for every row when copied down. It only ever shipped as a preview in the Microsoft 365 Insider and Frontier programmes, and Microsoft retired it on 14 September 2026: cached results stay in the workbook, but any cell that recalculates now returns #NAME?. For per-row AI operations — classification, summarisation, translation, entity extraction — ask Copilot in Excel to add the results as a new column instead.

Q: What can Microsoft Copilot automate in Excel today?

Sorting and filtering, adding formula columns (with both structured references and LAMBDA-style logic), conditional formatting, insertion of pivot tables and charts, per-row AI operations such as classifying or summarising text into a new column, data validation rules, data cleanup (duplicates, trim, case, blanks), and with Agent Mode — entire multi-step recurring workflows. What it cannot do: write or edit VBA macros (use Claude AI or ChatGPT for VBA) or trigger external systems (use Power Automate).

Q: Why is Microsoft Copilot not available in my Excel?

Three common causes: (1) your Microsoft 365 plan does not include Copilot — you need Microsoft 365 Personal, Family or Premium, a Copilot Chat-eligible business or enterprise plan, or the Microsoft 365 Copilot add-on licence (£23.10/user/month, paid yearly), (2) the file is checked out in SharePoint or saved in an unsupported format such as Strict Open XML — check it in or save it as a normal .xlsx workbook, (3) your admin has restricted Copilot Chat, which also controls Copilot in Excel, or blocked it with DLP policies. If the button exists but responses fail, check Excel is up to date (File → Account → Update Options) and restart Excel.

By Sagnik Bhattacharya23 Mar 2026Updated 25 Sep 202612 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for Automating Repetitive Excel Tasks with Copilot, with automation loops, task cards, and spreadsheet workflow elements.

If you spend time every week doing the same Excel tasks — sorting data, applying conditional formatting, creating summary rows, cleaning imports, building a weekly chart — Microsoft Copilot can handle most of them from a plain-English prompt, and as of the Q1 2026 release it can now chain those steps into a multi-action workflow through Agent Mode. A March 2025 Toshiba case study reported 5.6 hours per month saved per employee after adopting it properly. This tutorial shows you exactly which prompts earn those hours, how to handle per-row AI operations now that the COPILOT() cell function has been retired, and how to set up a repeating weekly workflow in Agent Mode.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Everything here works on Microsoft 365 Copilot in Excel on Windows and Mac, and on Excel for the web. You need a plan that includes Copilot in Excel — Microsoft 365 Personal, Family or Premium, or a Copilot Chat-eligible business plan, with the Microsoft 365 Copilot licence (£23.10/user/month paid yearly, or £16.10 for Copilot Business) adding priority access and model choice — and that is the prerequisite I see people miss most often. OneDrive or SharePoint is no longer required: on Windows and Mac, Copilot also works on workbooks saved to your own computer. For the broader Excel learning path see the Excel Formulas Guide hub; for the first-time setup walk-through see Getting Started with Copilot in Excel; for exploration-focused prompts see Copilot for Data Analysis.

Prerequisites

A plan that includes Copilot in Excel. Microsoft 365 Personal, Family and Premium include it with usage limits; business and enterprise plans get it through Copilot Chat, and the Microsoft 365 Copilot licence adds priority access and model choice.
A modern workbook file. OneDrive, SharePoint and (on Windows and Mac) local .xlsx files all work, with AutoSave on or off. Files checked out in SharePoint or saved as Strict Open XML show an "Unsupported file state" message instead.
Your data as an Excel Table. Select the range and press Ctrl+T. Copilot no longer requires a Table, but it reads structure most reliably via Table metadata — plain ranges work less consistently.
Up-to-date Excel. Copilot's edit, plan and chat modes arrive through regular Microsoft 365 updates, so stay current — File → Account → Update Options → Update Now.

Sorting and Filtering

"Sort this table by Revenue, highest to lowest."
"Filter to show only rows where Region is North and Status is Active."
"Show only the top 20 customers by total orders."
"Remove all rows where the Status column is blank."
"Hide the Notes column temporarily — I'll restore it later."

Copilot applies these changes directly to your Table and confirms in chat. Every action is reversible with Ctrl+Z, and filters can be cleared from the ribbon if Copilot over-scopes.

Beginner pitfall: ambiguous column names (two columns called "Date") make Copilot guess wrong. Rename one before prompting, or reference the column by its position ("the second Date column").

Formatting

"Bold all headers and add filters."
"Highlight all cells in the Revenue column that are above average in green." — see conditional formatting tips for manual control when the AI version is too coarse.
"Add alternating row colours to this Table (banded rows)."
"Format the Date column as DD/MM/YYYY and right-align the Amount column."
"Add data bars to the Profit column — green for positive, red for negative."
"Freeze the top row and the first two columns."

Beginner pitfall: Copilot-applied conditional formatting is applied to the current range snapshot, not to the Table's structured reference. Add rows later and the rule often doesn't extend. Prompt "apply to the entire Revenue column of tblSales so it extends with new rows" to force the structured-reference version.

Adding Formulas

"Add a column that calculates the percentage of total for each row."
"Add a RANK column based on Revenue, with ties broken by Date descending."
"Calculate the running total of Sales in a new column, resetting at each Month change."
"Add a column showing the difference from the previous month for each Customer."
"Add a Days Since column using TODAY() minus the Last Order column."

Copilot writes the formula using structured references and applies it to every row of the Table. Always review the generated formula — Copilot is usually right, but misreads a column header every 20 prompts or so. See the advanced formulas guide to sanity-check what it writes. For broken formulas, Claude AI debugging pairs well.

Beginner pitfall: asking "add a total at the bottom" sometimes inserts a literal SUM formula outside the Table instead of toggling the Table's Total Row. Prompt "turn on the Total Row and set Revenue to Sum" for the Table-native version, which respects filters via SUBTOTAL(9).

Per-row AI after the COPILOT() function

The COPILOT() function ran a Copilot prompt from inside a cell formula — =COPILOT(prompt, [context]) — and evaluated once per row when copied down a column. It only ever shipped as a preview in the Microsoft 365 Insider and Frontier programmes, and Microsoft retired it on 14 September 2026. The way to apply AI classification, summarisation, or extraction to an entire dataset now is to ask Copilot in Excel for a new column:

Sentiment: "Classify each review in the Review column as Positive, Neutral, or Negative, one word per row, in a new Sentiment column."
Category extraction: "Extract the product category from each Description into a new Category column."
Summary: "Summarise each email in the Body column in one sentence, in a new column."
Entity pull: "Extract the company name mentioned in each note in the Notes column. Return only the name or 'none'."

Spot-check a sample of rows before you rely on the new column — AI classification is still a draft, exactly like a generated formula.

Beginner pitfall: inherited COPILOT() formulas. Cached results stay in old workbooks, but any cell that recalculates now returns #NAME?. Freeze them to static values before that happens: select the column → Copy → Paste Special → Values.

Agent Mode — multi-step automation from a single prompt

Agent Mode is the biggest 2026 change. Instead of prompting Copilot for each action one at a time, describe the end-to-end outcome and let Copilot plan and execute the sequence, pausing for confirmation at the steps that matter.

There is nothing to switch on any more: Agent Mode is now Copilot in Excel's default edit mode. Select the Copilot button in the corner of the sheet, check the Chat/Edit switcher above the prompt box is set to edit (or pick plan mode to review the steps before anything changes), and try a prompt like:

"Every Friday I need a regional sales pack from the tblWeeklySales Table. Remove rows where Amount is 0 or blank, extract the Month from the Date column, sort by Date descending, highlight the top 10 deals in green, and insert a bar chart of Revenue by Rep. At the end, add a summary Table of totals by Region below the chart. Pause before the chart step so I can confirm the layout."

Agent Mode plans the seven steps, shows you the plan, and runs them — stopping before the chart step as you asked. You review; you approve; it completes. To reuse it, save the steps as a custom skill (a SKILL.md file in the skills folder Copilot creates in your OneDrive), so next Friday you paste the new raw data and call it with an @mention — no re-prompting.

Beginner pitfall: Copilot in Excel can't be switched on or off separately from Copilot Chat. If it is missing for everyone in your organisation, your tenant admin needs to check the Copilot Chat settings; if it answers but never touches the workbook, the Chat/Edit switcher is set to chat only.

Data Summarisation

"Turn on the Total Row and set Revenue, Cost, and Profit to Sum, and Orders to Count."
"Create a summary pivot showing average revenue by region and month." — pivot tables do this interactively too.
"Calculate the year-over-year growth rate for each Region and show as a new Table below."
"Show monthly totals of Revenue and Profit in a new Table with a trend sparkline column."
"Insert a KPI card showing total Revenue, average deal size, and win rate at the top."

Worked example: weekly regional sales pack (classic mode)

Here is the same weekly pack worked out prompt-by-prompt if you are not yet on Agent Mode. A 12-person sales team, 340 weekly deals, 4 regions. Total wall-clock time: under 3 minutes of prompts against what used to be 30 minutes of manual work.

Open the raw weekly CSV, select the data, press Ctrl+T to create a Table. Rename it tblWeeklySales via Table Design → Table Name.
"Remove rows where Amount is 0 or blank." → Copilot deletes 14 empty rows.
"Add a Month column extracted from the Date column, formatted as MMM YYYY." → 340 rows populated.
"Sort by Date descending." → two keystrokes replaced.
"Add a Category column that extracts the product category from each Description." → 340 AI classifications in one pass.
"Highlight the top 10 rows by Amount in green." → applied as a Top-10 conditional-formatting rule.
"Insert a bar chart showing total Revenue by Rep, sorted by value." → chart appears below the Table.
"Insert a summary Table below the chart with totals by Region and Month." → grouped summary rendered.
Ctrl+S to save. The weekly pack is ready.

Next week: replace the raw data, re-run the same prompts (or save the above as a custom skill per the previous section).

Limitations and what to use instead

Cannot write VBA macros. For macro-based automation that runs on a button click, use Claude AI to generate VBA.
Cannot trigger external systems (email sends, Teams posts, CRM updates). Chain Excel Copilot with Microsoft Power Automate for email/schedule/file-change triggers.
Cannot edit in Manual calculation mode. Copilot editing is only supported when Calculation Options are set to Automatic — switch back before prompting.
Struggles with free-text ambiguity. Duplicate column names, inconsistent category strings ("EMEA" vs "Emea" vs "Europe"), and merged cells cause silent mis-routing. Clean the data first — see how to clean messy data.
Usage-limited. Personal and Family plans come with usage limits, and business users without the Microsoft 365 Copilot licence get standard rather than priority access — split very large row-by-row jobs into batches.

Copilot + Power Automate for true hands-off runs

For end-to-end automation where no human touches Excel at all, chain Copilot with Power Automate. Typical flow:

Trigger: new file in a SharePoint folder, new email with attachment, or scheduled run every Monday 8am.
Excel steps: use the "Run script" or "Run Office Script" action to kick off a pre-recorded Excel script generated by Copilot (Automate tab → New Script → prompt Copilot to write it).
Post steps: send a Teams message, email the result, update a Planner task.

For pure data-transformation automation without Power Automate, Power Query remains the right tool — refresh-on-open gives you scheduled-style behaviour without a licensed flow.

Common mistakes

Prompting a plain range instead of an Excel Table. Copilot's accuracy on ranges is maybe 70% of its Table accuracy.
Using Copilot for one-off exploration when a pivot would be faster to build and cheaper on quota.
Leaving inherited COPILOT() formulas live. The function was retired on 14 September 2026, so any recalc turns cached results into #NAME? — freeze them to values.
Relying on Agent Mode plans for workflows that change weekly. Agent shines on stable patterns; changing data shapes confuse it.
Skipping the review step on generated formulas, especially for financial columns. Copilot misreads column headers roughly 1 in 20 prompts — a human eye catches this in seconds.

Troubleshooting

"Unsupported file state." The file is checked out in SharePoint or saved in an unsupported format such as Strict Open XML. Check it back in (or open it in Excel for the web), or save it as a normal .xlsx workbook, and reopen.
Copilot button is missing or greyed out. Three causes in order of likelihood: (1) your plan doesn't include Copilot, (2) Excel is out of date, (3) admin has restricted Copilot Chat or DLP-blocked Copilot for this site. Open File → Account to confirm the licence, then work through 2-3.
Copilot answers but never edits the workbook. The Chat/Edit switcher above the prompt box is set to chat only, or Calculation Options are set to Manual — Copilot editing needs Automatic.
Old COPILOT() cells now show #NAME?. The function was retired on 14 September 2026, so recalculating cells can no longer resolve it. Undo, freeze the cached results with Paste Special → Values, and use Copilot in Excel for new per-row work.
Agent plan pauses and never resumes. A previous step produced an unexpected result (usually column name drift). Select the Stop button in the chat input, fix the upstream column, and re-run. Agent Mode does not retry automatically — that is by design, to prevent runaway edits.

Frequently asked questions

How do I automate Excel tasks with Microsoft Copilot?

Open the Copilot pane (select the Copilot button in the corner of the sheet) and type a plain-English instruction like "Add a column that calculates year-over-year growth". Copilot reads your Excel Table structure and applies the change directly. For multi-step recurring workflows, stay in the default edit mode (what launched as Agent Mode in the Q1 2026 release) and describe the outcome instead of each step. The file can be on OneDrive, SharePoint or your own computer, and an Excel Table still gives the most reliable results.

What is Agent Mode in Excel Copilot?

Agent Mode, released in the Q1 2026 Microsoft 365 Copilot update, turns one-shot prompts into multi-step workflows. Describe the outcome and Copilot plans and executes the steps — import, clean, summarise, chart, insert totals, format — pausing for confirmation at key branches. It is now Copilot in Excel's default edit mode: open Copilot from the button in the corner of the sheet and check the Chat/Edit switcher, or use plan mode to review the steps first.

What is the COPILOT function in Excel?

COPILOT was a preview cell-level formula function, offered only through the Microsoft 365 Insider and Frontier programmes. The syntax was =COPILOT(prompt, [context_range]). For example =COPILOT("Classify sentiment:", A2) returned a classification per row when copied down. Microsoft retired it on 14 September 2026 — recalculating cells now return #NAME?, so freeze old results to values and ask Copilot in Excel for per-row classification, summarisation, translation or entity extraction instead.

What can Microsoft Copilot automate in Excel today?

Sorting and filtering, adding formula columns, conditional formatting, pivot tables and charts, per-row AI into a new column, data validation, cleanup (duplicates, trim, blanks), and with Agent Mode entire multi-step recurring workflows. Cannot: write VBA macros (use Claude AI) or trigger external systems (use Power Automate).

Why is Microsoft Copilot not available in my Excel?

Three causes: (1) your Microsoft 365 plan does not include Copilot — you need Personal, Family or Premium, a Copilot Chat-eligible business plan, or the Microsoft 365 Copilot licence (£23.10/user/month, paid yearly), (2) the file is checked out in SharePoint or saved as Strict Open XML, (3) your admin has restricted Copilot Chat or applied a DLP policy. If the button exists but responses fail, update Excel via File → Account → Update Options and restart.