Copilot in Excel handles formula generation in three distinct ways, and understanding which one to use for which task is what separates people who get real value out of it from people who end up frustrated. This guide covers all three, with practical examples for each.

Ad placeholder — in-article responsive unit

Before you start: requirements

You need a Microsoft 365 plan with Copilot licensed (Business Standard, Business Premium, or Enterprise with the Copilot add-on). For the =COPILOT() function specifically, you need Excel for Windows or Mac on a recent build — it's currently rolling out to Microsoft 365 Copilot subscribers. For most other Copilot features, Excel on the web also works. Your data should be formatted as a proper Excel Table (Insert > Table) for best results — Copilot reads column headers as context and gives better suggestions when structure is clean.

Method 1: Ask Copilot to suggest a formula column

This is the most straightforward method and works directly from the Copilot pane in Excel. Open the Copilot pane from the ribbon (Home tab), then type what you want in plain English. You don't need to know the formula name — just describe the result you want.

Example prompts that work well

Copilot will suggest a formula and explain how it works. You can ask it to show the explanation if it's not immediately visible, then click Insert to add it. Always review the formula before applying it to your full dataset — Copilot gets the logic right most of the time but can occasionally misread column relationships.

Tip: the more specific your prompt, the better the result. "Calculate profit" is less useful than "Calculate profit margin as a percentage using the Revenue and Cost columns." Copilot uses your column headers as context, so the cleaner your headers, the more accurately it maps your intent to the right formula.

Method 2: Formula completion as you type

When you type = in a cell, Copilot analyzes your data context — the column headers, nearby values, and table structure — and proactively suggests a formula. This works well when you already know roughly what you want but don't know the exact function syntax.

For example, if you're in a column labelled "YoY%" and type =, Copilot may automatically suggest the year-over-year growth formula based on the adjacent columns without you needing to describe it. You can accept, modify, or ignore the suggestion — it doesn't insert anything until you confirm.

Method 3: The =COPILOT() function

This is the newest and most powerful method — a native Excel function that brings AI directly into individual cells. Rather than using a chat interface, you write a prompt as a formula argument, and Copilot outputs the result into the cell (and into adjacent cells if the output spans multiple rows or columns).

Basic syntax

=COPILOT("your prompt here") — or with a data reference:

=COPILOT("your prompt", A1:A100)

The first argument is your plain-language instruction. The optional second argument is a cell or range that gives Copilot the data to work with.

=COPILOT() formula examples

Important: the =COPILOT() function is well-suited to text analysis tasks — classifying, summarizing, ranking, extracting. It's not a replacement for standard Excel formulas for numerical calculations (use SUM, XLOOKUP, IF, and so on for those — they're faster, more reliable, and don't require a Copilot license to run). Think of =COPILOT() as Excel's way of handling the tasks that previously had no formula at all.

Method 4: Agent Mode for complex multi-step tasks

Agent Mode is Copilot's most autonomous capability — rather than suggesting a single formula, it can execute a multi-step plan: building PivotTables, creating charts, writing multiple formulas, and assembling a dashboard, all from a single natural language instruction.

When to use it

Use Agent Mode when you want a complete output, not just a formula. For example:

Agent Mode works by breaking your request into steps, executing each one, evaluating the result, and iterating — you don't have to manage the steps yourself. You can keep prompting to refine the output after it finishes. Note that Agent Mode availability varies by Microsoft 365 plan and rollout stage — if you don't see it, check for updates or confirm your license tier.

Prompt patterns that consistently work

Across all three methods, a few simple prompt habits make a significant difference in result quality:

Always verify before trusting

Copilot's formula suggestions are accurate most of the time, but not always — especially for formulas with multiple conditions, nested logic, or formulas that reference your specific column structure. Test on a small sample of rows before applying to a full dataset. For financial, HR, or compliance spreadsheets, treat Copilot-generated formulas as a first draft to review, not a final answer. Microsoft explicitly recommends not using =COPILOT() in high-stakes scenarios without human verification.

The bottom line

For standard formula generation, ask Copilot in the pane — it handles most XLOOKUP, SUMIF, IF, and calculation tasks well via plain English. For text analysis tasks that previously had no formula solution, the =COPILOT() function is genuinely new and useful. For anything that needs a full dashboard or multi-step analysis, Agent Mode is the right tool. The key in all three cases: be specific, name your columns, and always verify the output before relying on it.