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:
=COPILOT(prompt_part1, [context1], [prompt_part2], [context2], ...)
- 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
=COPILOT("analyse the Australian credit card descriptions and come up with a short list of categories I can use to group the
expenses")
Notice I haven’t specified the cells containing the transactions. The COPILOT function is aware of the data around it, so it infers I am referring to the descriptions in column B.
I
also told it the transactions are Australian, so it has some context to the names it’s likely to find in the description.
COPILOT scans the list and proposes categories to suit my data:
Step 2 – Classify transactions
=COPILOT("categorise the Australian credit card expenses",B5:B23, "based only on these categories",G5#)
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:
=COPILOT("extract the zipcode from the address", B5:B24)
And the COPILOT function returns the zipcodes perfectly:
It can also correct the format of the addresses with this formula:
=COPILOT("rearrange the addresses so they're correctly formatted", B5:B24)
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:
=LAMBDA(val,low,up, val=MEDIAN(val,low,up))(B4,C4,D4)
No problem, just ask COPILOT:
=COPILOT("explain in plain English what this formula is doing", FORMULATEXT(E4))
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:
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(CHAR(10)&CHAR(10), TRUE, COPILOT("analyse the Profit and Loss statement and give me a summary of notable points", B4:F31))
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.
For example, to list the English Premier League teams for the 2025/2026 season and have them ranked by predicted performance:
=COPILOT("List the English Premier League teams for the 2025/2026 season and rank them based on their likelihood of taking out the premiership")
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 Liverpool are top favourites. I entered the same prompt into ChatGPT running GPT-5
and I got a more sensible result as you can see below:
The
COPILOT function is currently running GPT-4.1 mini. Over time this will be updated, and results will improve.
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 workbook open. 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?
Learn With Us