Copilot + Excel

How to Automate Excel Tasks with Microsoft Copilot

Sagnik BhattacharyaBy Sagnik Bhattacharya23 Mar 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.
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 2026 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 use the new COPILOT() cell function for per-row AI operations, and how to set up a repeating weekly workflow in Agent Mode.

I teach Flutter and Excel with AI — explore my courses if you want structured learning.

Everything here works on Microsoft 365 Copilot in Excel on Windows and Mac, and on Excel for the web. You need a Microsoft 365 Copilot licence (around £24/user/month added to a Business Standard/Premium or Enterprise plan) and your workbook must be saved to OneDrive or SharePoint — the hard prerequisites I see people miss most often. 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.

Follow me on Instagram@sagnikteaches Connect on LinkedInSagnik Bhattacharya Subscribe on YouTube@codingliquids

Prerequisites

  • Microsoft 365 Copilot licence attached to a Business Standard/Premium, Enterprise, or Education plan. Personal and Family plans do not include it.
  • OneDrive or SharePoint storage. The file must be auto-saved to a cloud location. Local-only files show Copilot as greyed out.
  • Your data as an Excel Table. Select the range and press Ctrl+T. Copilot reads structure via Table metadata — plain ranges work intermittently, Tables work every time.
  • Up-to-date Excel. Agent Mode and the COPILOT() function require Excel 365 version 2403 or later — 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 with the COPILOT() function

The COPILOT() function, released in March 2026, runs a Copilot prompt from inside a cell formula. Syntax: =COPILOT(prompt, [context]). When copied down a column it evaluates once per row, which is how you apply AI classification, summarisation, or extraction to an entire dataset without copy-pasting into the Copilot pane.

  • Sentiment: =COPILOT("Classify this review as Positive, Neutral, or Negative. Reply with only one word.", [@Review])
  • Category extraction: =COPILOT("Extract the product category from this description.", [@Description])
  • Summary: =COPILOT("Summarise this email in one sentence.", [@Body])
  • Entity pull: =COPILOT("Extract the company name mentioned in this note. Return only the name or 'none'.", [@Notes])

Each cell invocation counts against the tenant's Copilot usage quota, so batch carefully — 1,000 rows of =COPILOT(...) fires 1,000 API calls. For very large datasets, Agent Mode (next section) batches intelligently and is cheaper.

Beginner pitfall: volatile COPILOT() recalc. By default it recalculates on every workbook change, which is slow and expensive. Convert results to static values once you are happy: 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.

Enable it by clicking the split-button on Home → Copilot and selecting Agent from the mode menu. The chat UI swaps from "Ask Copilot" to "Agent — describe the outcome". 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. The plan is saved with the workbook, so next Friday you paste the new raw data and repeat with "Run the weekly pack plan on the new data" — no re-prompting.

Beginner pitfall: Agent Mode's admin default is off in many Microsoft 365 tenants. If the Agent option doesn't appear in the split-button, your tenant admin needs to enable it in the Microsoft 365 admin centre under Copilot → Settings → Agents.

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.

  1. Open the raw weekly CSV, select the data, press Ctrl+T to create a Table. Rename it tblWeeklySales via Table Design → Table Name.
  2. "Remove rows where Amount is 0 or blank." → Copilot deletes 14 empty rows.
  3. "Add a Month column extracted from the Date column, formatted as MMM YYYY." → 340 rows populated.
  4. "Sort by Date descending." → two keystrokes replaced.
  5. "Add a column =COPILOT('Extract product category from this description:', [@Description])." → 340 AI classifications in one pass.
  6. "Highlight the top 10 rows by Amount in green." → applied as a Top-10 conditional-formatting rule.
  7. "Insert a bar chart showing total Revenue by Rep, sorted by value." → chart appears below the Table.
  8. "Insert a summary Table below the chart with totals by Region and Month." → grouped summary rendered.
  9. 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 an Agent Mode plan 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 work on local-only files. OneDrive or SharePoint is a hard requirement — no workaround.
  • 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.
  • Quota-bound. Enterprise tenants get a per-user monthly Copilot allowance; heavy =COPILOT() use on 10k+ row datasets hits it fast. Paste-Special-Values to freeze results once happy.

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:

  1. Trigger: new file in a SharePoint folder, new email with attachment, or scheduled run every Monday 8am.
  2. Excel steps: use the "Run script" or "Run Office Script" action to kick off a pre-recorded Excel script generated by Copilot (File → Automate → New Script → prompt Copilot to write it).
  3. 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.
  • Forgetting to freeze COPILOT() results. Volatile per-row AI on 500+ rows slows the workbook and burns quota on every recalc.
  • 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

  1. "Copilot can't be used on this file right now." The file is saved to a local drive or to a personal OneDrive in a commercial tenant. Move to OneDrive for Business or SharePoint and reopen.
  2. Copilot button is greyed out. Four causes in order of likelihood: (1) no Copilot licence on your account, (2) file is local not cloud, (3) data is not in a Table, (4) admin has DLP-blocked Copilot for this site. Open File → Account to confirm the licence, then work through 2-4.
  3. Agent Mode option missing from the Copilot split-button. Your tenant admin has not enabled Agents. The admin centre path is Copilot → Settings → Agents → turn on "Agent Mode for Excel". Also requires Excel 365 version 2403+.
  4. COPILOT() returns #BUSY! or #CALC!. #BUSY is a rate-limit signal — wait 30 seconds and recalc with F9. #CALC means the prompt string is empty or the context range resolved to an error; check the referenced cell for #N/A or #REF! upstream.
  5. Agent plan pauses and never resumes. A previous step produced an unexpected result (usually column name drift). Click the plan's "Cancel" button, 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 (Home → Copilot) 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, switch to Agent Mode (Q1 2026 release) and describe the outcome instead of each step. The file must be on OneDrive or SharePoint, and the data must be an Excel Table.

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. Enable via the Home → Copilot split-button; tenant admins may need to toggle it on in the Microsoft 365 admin centre first.

What is the COPILOT function in Excel?

COPILOT is a cell-level formula function released in March 2026. The syntax is =COPILOT(prompt, [context_range]). For example =COPILOT("Classify sentiment:", A2) returns a classification per row when copied down. Useful for per-row AI tasks — classification, summarisation, translation, entity extraction. Each call uses the tenant's Copilot quota; freeze results to values once happy.

What can Microsoft Copilot automate in Excel today?

Sorting and filtering, adding formula columns, conditional formatting, pivot tables and charts, per-row AI via COPILOT(), data validation, cleanup (duplicates, trim, blanks), and with Agent Mode entire multi-step recurring workflows. Cannot: write VBA macros (use Claude AI), trigger external systems (use Power Automate), work on local-only files.

Why is Microsoft Copilot not available in my Excel?

Four causes: (1) your Microsoft 365 plan does not include Copilot — it needs a Copilot add-on licence (~£24/user/month), (2) the file is not on OneDrive or SharePoint, (3) the data is not in an Excel Table — press Ctrl+T, (4) your admin has disabled Copilot via the admin centre or a DLP policy. If the button exists but responses fail, update Excel via File → Account → Update Options and restart.

Sources & Further Reading

Related tutorials