COPILOT Function

Microsoft Excel’s new COPILOT function brings the power of large language models (LLMs) directly into the worksheet. Unlike the Copilot chat pane, the COPILOT function allows us to perform natural language tasks right in the grid, just like any native Excel function. In this post, we’ll walk through how to use the COPILOT function to clean data, categorize items, analyze sentiment in reviews, and summarize insights. This functionality allows us to streamline tasks that typically require lookup tables, Power Query, or even VBA.

Video

What Is the Excel COPILOT Function?

The COPILOT function leverages generative AI using prompts we define, returning intelligent, contextual outputs directly into cell ranges. It empowers us to:

• Clean and structure messy data
• Categorize free-text entries
• Analyze sentiment in reviews
• Extract insights and suggest follow-up actions

By typing a simple formula like =COPILOT("categorize these items", A2:A20), we unlock machine learning capabilities without leaving our spreadsheet. The results are returned directly to the grid, so we don’t need to copy/paste from a different system. It works great with text-related tasks. We provide the function with both the prompt AND the context (range of cells). Now let’s take a look at the function syntax and arguments.

COPILOT Function Signature

=COPILOT(prompt_part1, [context1], [prompt_part2], [context2], …)

• prompt_part1 (required): The natural language instruction or task you want the AI model to perform (e.g., "Summarize this feedback").
• context1, context2, … (optional): Cell references (single cell, range, or named ranges) that provide data or context for Copilot to process.

Here’s a concise note you can drop straight into your article:

Note on Availability
At the time of this writing, the COPILOT function is currently rolling out in preview to Excel for Microsoft 365 users. Availability may vary depending on your subscription, region, and update channel. If you don’t see the function yet, make sure your Office apps are up to date and watch for future updates as Microsoft continues the rollout.

Step-by-Step Walkthrough

Exercise 1: Clean Messy Data

Imagine we’ve received a dataset with inconsistent formatting, jumbled order, incorrect casing, and missing fields like this:

We want to specify which value we extract, so we create some column labels, like this:

In C9, we can use a formula like this (note the two prompts along with two context arguments):

=COPILOT("clean this messy data",B9:B19,
"extract the values based on the column labels",C8:F8)

We hit Enter and bam:

Exercise 2: Categorize Items

Suppose we have a list of items and want to categorize them based on our category list.

We can write the following formula:

=COPILOT("return the category of each item",B8:B22,
"based on these categories",B26:B30)

We hit Enter, and bam:

Exercise 3: Review Sentiment Analysis

For user-generated content like product reviews, we can ask Excel to determine their sentiment.

=COPILOT("Give the sentiment of each review", B9:B23)

This will return values such as positive and negative:

Need a higher-level summary of themes?

=COPILOT("provide the themes of these reviews", B9:B23)

And there you have it … the ability to analyze qualitative feedback at scale, directly within Excel.

Summary

The COPILOT function introduces natural language processing to your Excel worksheets. Whether we’re organizing unstructured data, identifying trends, or getting actionable insights, this function allows us to utilize an LLM and have the results returned directly to our worksheet cells. We can now combine Excel’s precision with the dynamic reasoning of AI, without copy-pasting into (or from) an external chat or tool.

We hope this walkthrough empowers you to explore Excel’s newest AI capabilities. Keep experimenting, keep learning, and remember, we’re combining the best of both worlds: rigorous Excel logic with creative AI insight.

Download the Practice File

To try these examples yourself, download our example workbook here:

Frequently Asked Questions (FAQ)

1. What is the COPILOT function in Excel?

The COPILOT function is a new Excel formula that allows natural language prompts directly in a cell. It uses context provided by cell ranges to perform tasks like summarization, formatting, categorization, and more right inside your worksheet.

2. How do I access the COPILOT function?

The function is rolling out through Microsoft 365’s update channels. Ensure that Excel is updated to the latest version and your account includes Copilot capabilities.

3. How is this different from the Copilot chat pane?

Unlike the chat interface, the COPILOT function runs directly in worksheet cells, returning outputs as cell values, just like any native Excel formula.

4. Can I use structured prompts in the COPILOT function?

Yes. You can refine prompts and even pass context in multiple arguments to guide the LLM’s behavior more precisely.

5. Is the function available offline?

No. The COPILOT function uses cloud-based AI models, requiring an internet connection.

6. What types of tasks is COPILOT best suited for?

Tasks like cleaning messy data, categorizing free-text entries, analyzing sentiment, summarizing feedback, and formatting text elements.

7. Can I control the output format?

You can influence it somewhat with your prompt, such as “Return in a single column” or “Separate by commas,” but the LLM decides based on context.

8. Are the results always accurate?

No AI is perfect. While results are generally good, we should always review critical outputs for accuracy before relying on them for decision-making.

9. Is it possible to use more than two arguments?

Yes. The COPILOT function accepts multiple arguments to define more detailed context and guiding prompts.

10. How can I learn more about the COPILOT function?

Stay updated with Microsoft 365 release notes, or follow us for tutorials, practice files, and in-depth explanations.