Excel COPILOT Function

Excel is now a whole lot smarter.

Imagine typing a plain-English question right into a formula: “Classify these transactions into spending categories,” “Summarise this feedback,” or “Extract the email addresses from this column”, and having Excel instantly deliver the result.

Now you can with the new Excel COPILOT function, a built-in AI assistant that reads your spreadsheet data, understands your prompt, and returns exactly what you asked for.

Whether you’re cleaning data, pulling insights, or creating summaries, COPILOT turns your words into answers - no complex formulas required.

Note: The COPILOT function is currently rolling out to the Microsoft 365 beta channel and requires a Microsoft 365 Copilot license.

Watch the COPILOT Function Video

Get the Example Excel File

What Is the COPILOT Function?

The COPILOT function is designed to bridge the gap between AI and your Excel data. You simply write a prompt in plain language, reference the data you want it to consider, and let Excel do the rest.

Syntax:

• prompt_part1  The main question or instruction for COPILOT (required).
• context1, context2, ...  Optional cell ranges, tables, or named ranges containing data for COPILOT to process.
• You can alternate between prompts and contexts to build more complex requests.
• COPILOT combines everything into a single query before generating a response.

Why COPILOT Is Different

Traditional Excel formulas require you to know the exact syntax and function combinations to get results. Power Query lets you clean and transform data, but it needs clear rules.

COPILOT is different because:

• It understands natural language, not just formulas.
• It can handle fuzzy, unstructured tasks.
• It works dynamically with the data in your spreadsheet without complex setup.
• You can iterate - changing your wording can produce different results.

Example 1. Classifying Bank Transactions

Let’s say you have a table of bank transactions, and you want to assign each one to a spending category, but descriptions on bank statements are often inconsistent and ambiguous, just like the list below:

This requires you to go through each transaction manually but now you can use the COPILOT function to do it for you.

Step 1 – Ask for categories

Notice I haven’t specified the cells containing the transactions. It appears that the COPILOT function is aware of the data around it, but in fact it is using its general knowledge to come up with a list of typical categories:

For more accurate results, you should reference the data you want it to classify in the context argument.

Step 2 – Classify transactions

COPILOT returns a classification for each row, no manual tagging required:

Example 2. Cleaning Messy Addresses

If your address data is inconsistent: missing commas, different formats, or extra spaces, COPILOT can help. For example, let’s take this data and extract zip codes:

And the COPILOT function returns the zipcodes perfectly:

It can also correct the format of the addresses with this formula:

And it returns a column of addresses in a useable format:

Example 3. Explaining Complex Formulas

Ever opened a spreadsheet and found a formula you can’t decipher? COPILOT can translate it into plain English. Maybe you’ve inherited an Excel file that contains a LAMBDA formula in cell E4 like the one below and you have no idea where to start with it:

No problem, just ask COPILOT:

Note that I use the FORMULATEXT function to pass COPILOT the underlying formula in cell E4 as a text string, rather than the result of the formula.

And just like that, you have your own formula expert explaining things in a way you can understand:

However, as with many things, AI can hallucinate; it gets the first part of this formula right, but then it gets confused towards the end and omits the logical test that returns TRUE or FALSE.

Example 4. Analysing a Profit & Loss Statement

Instead of manually scanning rows of figures, you can have COPILOT point out trends, anomalies, and notable changes.

I pointed it to this profit and loss statement:

In cell O4 I entered my formula – notice I nested it inside the TEXTJOIN function so that each line of analysis returned by the COPILOT formula could be joined together into one cell:

TEXTJOIN combines multiple lines of COPILOT’s output into a single cell, with double line breaks using CHAR(10) for readability. I then linked the output to a shape, so it was easier to read:

Example 5. Generating Lists

COPILOT can create lists based on current knowledge, predictions, or specific criteria you give it, perfect for brainstorming or quick reference.

In the example below I asked it, to list the English Premier League teams for the 2025/2026 season and have them ranked by predicted performance:

This returns a ranked list of teams directly into your spreadsheet, ready to use for analysis, visualisation, or just friendly office debates.

However, if you’ve been keeping up with the Premier League, you’ll know that Leicester City and Sheffield United are not in the 2025/2026 Premier League and Sunderland and Leeds should be.

The COPILOT function is currently running GPT-4.1 mini but it doesn't have access to the web or your corporate data which is why you may get incorrect data, as I did in this case.

Tips for Getting the Best Results

• Be specific: instead of “Summarise this,” try “Summarise in three bullet points focusing on sales trends.”
• Use structured data: Tables and named ranges make it easier for COPILOT to understand your context but be careful entering a COPILOT formula in a table as it will be prevented from spilling.
• Start small: test on a small range before scaling up. Use FILTER to narrow context while testing and developing.
• Iterate: if the first result isn’t perfect, tweak your wording.

Limitations to Keep in Mind

• Initially you are capped to a maximum of 100 COPILOT calcs per 10 minutes and 300 per hour.
• COPILOT functions recalculate on changes to the data being referenced by the function or changes to the prompt. This can quickly eat into your allowance – and of course, return different results with each recalculation.
• Requires a Microsoft 365 Copilot license and internet access.
• COPILOT’s answers can vary depending on how you phrase the prompt.
• It’s best suited for interpretation, classification, and summarisation, not precise mathematical calculations.
• It doesn’t have awareness of its location in the worksheet and can’t return results to specific cells other than the cell you enter it in.
• Always review outputs before making critical decisions.

The Bottom Line

The COPILOT function opens the door to a new way of working in Excel. Whether you’re classifying transactions, analysing survey feedback, or cleaning up messy text, COPILOT turns your words into answers.

Now it’s your turn: What will you ask COPILOT to do first?