Wondering if you could use the mighty Copilot AI from Microsoft in your Excel workbooks to think and work faster? I’ve got you covered! Read on to learn how to use the COPILOT function in Microsoft Excel effortlessly.

In your daily work with Microsoft Excel, you often deal with large datasets, complex data analysis problems, and intricate formulas to gain insights, as well as repetitive analysis tasks. As a result, you might find yourself spending too much time writing formulas or fixing calculation errors.

The COPILOT function in Microsoft Excel changes that by letting you describe what you need in plain language, then instantly generating the right formulas, charts, or summaries to match your request.

In this article, you’ll learn how to use this function to automate analysis, create accurate summaries, and save time on every Excel data analysis project. By the end, you’ll know exactly how to make your analytical work in Excel faster, easier, and more efficient with COPILOT.

The COPILOT Function Syntax & the Basics

The Excel COPILOT function uses artificial intelligence models from Microsoft to help you work faster in Excel. What you need to do is type a prompt or instruction for the COPILOT function in natural language, like English (currently only compatible with English).

COPILOT then analyzes the data and the prompt text to generate the right formulas, data summaries, and insights based on the supplied data table.

Ultimately, it works like a master of Excel who knows the app inside out and can create almost all possible solutions in Excel in seconds.

The function comes with a simple syntax, so you don’t have to remember complicated arguments as in VLOOKUP, INDEX-MATCH, SUMIFS, OFFSET, XLOOKUP, and so on. You just need to select the prompt in a cell and then select the dataset in another cell range. That’s it! Let us explore the syntax in detail below:

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

Here’s an explanation of the formula syntax:

• prompt_part: It’s a required argument. This is the text that describes the task or question for the AI model. You can use multiple prompt_parts in sequence, combined with their contexts, to form a single complete instruction.

• context: This is an optional argument. This is a cell or range from your worksheet that provides additional data or context for the AI. It helps COPILOT generate more accurate and relevant results. The reference dataset goes into the context part of the formula syntax.

If you don’t provide context, the AI will analyze all the datasets in the active workbook.

It won’t recalculate the workbook if you press F2 + Enter or F9. It’ll recalculate the workbook if the prompt changes or if you press the Ctrl + Shift + Alt + F9 buttons at the same time.

The Availability of the COPILOT Function

Though it’s fun to use the COPILOT function to analyze your data in Excel, the unfortunate part is that the function isn’t available widely, as you can use SUM, VLOOKUP, CONCAT, etc. The COPILOT function is available to a limited Microsoft Excel users.

Firstly, you’ll need a paid license to use the premium Microsoft Copilot AI tool in your Microsoft 365 subscription. You need to buy this license separately from the Microsoft 365 subscription. Here are the plans you can sign up for:

• For Business and Enterprise Usage: Flexible Copilot plans for every organization

• For Home and Family Usage: Copilot in Microsoft 365 for personal use

Secondly, the function is in its testing phase. So, it’s only available through the Microsoft Excel Beta Channel. If you have the required license and wish to use the COPILOT function, you have to sign up for the Excel Beta Channel program by accessing the Microsoft 365 Insider program.

To activate this program, click on the File menu on the Excel workbook and choose Account from the Excel backstage view screen.

You should now see the Account screen. There, click on the Microsoft 365 Insider button on the right side, below the large Microsoft logo.

Follow the onscreen instructions to complete the Excel Beta Channel update process.

Before you perform the above steps and access the Excel Beta Channel program, you must know that the Beta version of the app might be unstable, may contain bugs, and could occasionally crash or behave unexpectedly. It’s recommended to use it on non-critical files and keep regular backups of your work to prevent data loss.

Here are the limitations of the COPILOT function in Excel:

• The function is available for calculating up to 100 calculations in a 10-minute window. In an hour, you can use the function up to 300 times.

• If your workbook is labeled Confidential, Highly Confidential, etc., the function won’t work and will show the #BLOCKED! error.

• The workbook must be uploaded to OneDrive, and the AutoSave button must be turned on for the desktop Excel app.

What Type of Tasks Is It Best Suited For?

The COPILOT function works best when you want to leverage natural language to quickly process or create information on Excel. Typical scenarios include:

• Summarizing information: Quickly condense long lists or text entries into key points.

=COPILOT("Summarize these meeting notes", C3:C25)

• Generating sample data: Produce placeholder entries for testing models, dashboards, or prototypes.

Example: =COPILOT("Create a list of five new product names")

• Tagging or categorizing content: Automatically assign labels, classifications, or sentiment to text.

=COPILOT("Label customer feedback as positive, neutral, or negative", D2:D50)

• Creating written content: Generate short descriptions or explanations based on existing data.

=COPILOT("Write a brief summary of this project plan", E5:E15)

What Type of Tasks Is It Not Suitable For?

Because COPILOT relies on AI, its results are not always fully reliable. To ensure accuracy and responsible use, avoid applying COPILOT to tasks where precision is critical:

• Numeric or financial calculations: For exact results, stick with Excel’s built-in formulas like SUM, AVERAGE, or IF.

• Data outside your provided context: COPILOT can only work with the text or ranges you explicitly reference. It cannot access other sheets, external files, or corporate databases.

• Lookups and table references: For retrieving specific data from your workbook, functions like XLOOKUP or INDEX MATCH remain the dependable choice.

• Compliance, legal, or financial reporting: Avoid relying on AI-generated outputs for regulatory filings, contracts, or financial statements.

• Real-time or very recent data: The model’s knowledge is fixed as of June 2024, and results may vary on recalculation, so it’s not suited for tasks requiring up-to-date or deterministic outputs.

Entering a Prompt From a Cell Reference

The easiest way to use the function is to reference a cell that has a prompt. The prompt could be a simple text or a combination of text and context, which contains dataset references. Here’s a step-by-step guide:

Type a prompt for the COPILOT function in B3, like the following:

List of 10 biggest cities by population include population estimate in ()

Select B4 and enter the following syntax into the cell:

=COPILOT(B3)

Hit Enter, and Excel will generate a list of the 10 biggest cities with their estimated population in brackets as an array data table.

You can also try the following prompt in D3:

Top 10 states by population, return only the state

And this prompt in E3:

Give me the capital city for each state

Now, enter the following formulas in D4 and E4, respectively:

=COPILOT(D3)

=COPILOT(E3,D4#)

You’ll get the data table shown above in seconds.

Review Text Sentiment Analysis

Let’s consider that you created a table of user reviews for your app, website, or Google Business listing.

Now, you wish to perform a sentiment analysis without using any complex formula. The COPILOT function should be your first choice.

Create the Sentiment column on the right side of the existing reviews data table.

Now, write the prompt in another cell, like B4. You can use the following prompt:

Tell me if the review is negative, nuetral, or positive. Retunr this as an emoji.

Select C7 and type in the following formula into the cell:

=COPILOT(B4,B7:B16)

Hit Enter. Excel will generate a sentiment analysis for the entire data table and represent the findings in emojis.

Categorize Text Data

Often, you find yourself matching text with categories, for example, How to unprotect a sheet in Excel without a password, with Sheets, and so on. Doing this manually for 100s of entries can be a painstaking job. In this scenario, you can use the COPILOT function to categorize text in a flash for thousands of entries. Find below the simple steps:

Firstly, write the following two prompts in two different cells, like D3 and D4.

Categorize these Excel topics

Based on these categories

Secondly, list the categories in another table, like the Categories column from B6:B23. Refer to the image attached above.

Now, go to the data table that contains the text entries and create a column next to it. You can name it Category.

Select the first cell below the Category column header and enter the following formula:

=COPILOT(D3,D7:D16,D4,B7:B23)

In the above formula syntax, D3 is the primary prompt, D7:D16 is the primary context, D4 is the secondary prompt, and B7:B23 is the secondary context. Ensure you change the cell range references according to your own dataset.

Hit Enter, and Excel will instantly categorize the text entries based on the given categories.

AI-Based Restaurant Recommendations

Another cool utilization of the COPILOT function is using it to fetch recommendations for hotels, restaurants, shops, services, businesses, websites, apps, and so on.

Suppose you’re looking for the best dinner places in Miami. Instead of searching the web manually, you can simply create an instant list referring to online data using the COPILOT function in Excel. Here’s how:

Type the city name in C3, like Miami, and meal type in C4, like Dinner. Then, enter the following prompt in C5:

Give me a list of the best 10 restaurants for "dinner "&C4&" in "&C3&". Return these values for each restaurant.

Create a table with the following column headers:

• Name of restaurant
• Type of cuisine
• Neighbourhood name
• Budget from $ to $$$
• Star rating from ⭐ to ⭐⭐⭐⭐⭐
• Must try dish name + (price)

Now, use the following formula in B9:

=COPILOT(C5,B8:G8)

Hit Enter. Excel will quickly create a table containing the best suggestions based on online research and reviews.

Movies & TV Show Suggestions

Here’s how you can get quick movie and TV show suggestions using the COPILOT formula:

In C5, type TV Shows or Movie (Type), and in C6, type I want to watch something funny with aliens. Now, write the following prompt in C8:

=C6&" Give me a list of 3 "&C5&" to watch. Return these values for each."

Create a table with the following column headers in the row B11:G11:

• Movie title
• Main actor
• Plot summary
• Genre
• Star rating from ⭐ to ⭐⭐⭐⭐⭐
• Poster

Now, select B12 and enter the following formula into the cell:

=COPILOT(C8,B11:F11)

Hit Enter, and you’ll get your movie or TV show suggestions table.

Generate Random Test Data

Often, you need random test data for your formulas and data analytics processes in Excel. You invest hours in finding matching data in free dataset repositories. Instead, you can use COPILOT in Excel. Let me show you how in step-by-step below:

Firstly, you’ll need to create a few rows containing the dataset specifications. For example, the Number of rows is 20 in C3, the requirement (Type), like I want is employment data from Santas toy factory, in C4.

Then, you can create as many prompts as you need to make the dataset more compatible with your data analytics process. For example, you can use these prompts as Prompt 1 and Prompt 2 in C5 and C6.

="Give me "&C3&" rows of random data. "&C4&" It should include the following fields:"    

The output for these fields should be as follows:

Once the basic requirements and prompts are ready, you need to create the Field and Description columns. For the Field column, you might use the following options:

• Employee ID
• Full name
• Job title
• Salary
• Start date
• Managers name

Now, for the above fields, you can define descriptions for the AI under the Description column and use these options:

• With leading zeros for a total of 6 digits
• Last name (all caps), first name (proper case)
• Job title all capitals
• As annual salary in the format $#,##0
• Start date of employment in this format July 1st, 2010
• First name last name

In this exercise, I have created the above table in B8:C14.

Now, select the destination cell for the table of random dataset and use the following formula in the first cell:

=COPILOT(C5,tblFieldNames[Field],C6,tblFieldNames[Description])

Hit Enter, and Excel will create the dataset instantly.

Fuzzy Lookups

Fuzzy matches refer to identifying text values that are similar but not exactly the same, such as Jon and John or NYC and New York City. It’s used to match data that may contain typos, variations, or inconsistent formatting.

In Excel, fuzzy matching is often done using Power Query’s Fuzzy Merge option, which compares text values based on similarity scores. However, this is a complicated approach and requires extensive practice. You can simply replace the Power Query-based approach with the COPILOT function.

Let me show you the steps below using a simple yet real-world dataset:

Suppose you have a table containing full names and email addresses. Now, you also have a second table containing names, but with typos and spelling mistakes.

Type the following prompts in C3 and C4:

Give me the best match to these names

From these names

Let’s consider the list of names and emails in the table B6:C16.

Now, select a cell to the left of the second table that contains names with typos, and enter the following formula:

=COPILOT(C3,E7:E11,C4,B7:B16) 

In the above formula, C3 is the first prompt, E7:E11 represents the reference table of names with typos, C4 is the second prompt, and B7:B16 is the reference table with correct names. Ensure you change the cell range references according to your own workbook.

Hit Enter to perform the first phase of fuzzy match between the names with typos and accurate names.

To match the emails to names, select the G7 cell and type in this formula:

=XLOOKUP(F7#,B7:B16,C7:C16)

Press the Enter key, and Excel will match the emails to full names in the second table.

Split Complex Names

This is an advanced example of the usage of the COPILOT function. Here, you’ll learn how to split names into their components and define the naming convention. Let’s get started!

Suppose you have a table of names that includes prefixes, suffixes, middle names, and other variations. You can split the components of these names and show those in a tabular format using the following two prompts.

Ensure you type the prompts in C3 and C4 or in two cells of your choice.

Split the names into their components 

Return these items for each name

Now, create the following columns in the table of names:

• Prefix
• Suffix
• First name
• Middle name or initial
• Last name
• Describe the naming format used

In this exercise, the above-mentioned column names are in the C6:H6 row.

Now, select C7 and type in the following formula into the cell:

=COPILOT(C3,B7:B26,C4,C6:H6) 

In the formula shown above, C3 is the first prompt, cell range B7:B26 refers to the names that need analysis, C4 is the second prompt, and the cell range C6:H6 represents column headers against which the formula needs to find data.

Press the Enter key to split the components of the names in a flash.

Extract Names From Text

Suppose there’s a table containing text strings. These texts also contain names, often full names. You want to extract these names from the text strings and create a table containing first and last names. Instead of manually extracting names, let’s see below how to automate the process using the COPILOT function:

Firstly, you need to put the prompts in separate cells. Here are the prompts you can enter in C3 (primary prompt) and C4 (secondary prompt):

Extract only the names from these sentences

Return these items

Now, let’s consider that the text strings are saved in the Sentence column in a table B6:B16. Extend the table to the right by adding two more columns, namely First name (C6) and Last name (D6).

Enter the following formula in C7 and hit the Enter key.

Since the formula is an array formula, it’ll populate data for all the columns in the table. You don’t need to copy and paste the formula in all the cells.

There you go! You’ve successfully extracted first names and last names from text strings with little or no effort.

Convert JSON Data to Tables

Let’s say you got a JSON code for a large database. You want to convert it into a data table that shows the fields in an organized and tabulated format. Let me show you below how the COPILOT function can help you here in simple steps:

Copy and paste the JSON code into the active workbook in a cell, say C3.

Type in the following prompt in G3:

Convert this to a table

Now, select the destination cell for your table and enter the following formula syntax in the first cell and hit Enter:

=COPILOT(G3,C3)

This array formula will itself create column headers for the table and list relative data points below each column. In a few minutes, you can convert a JSON file containing thousands of data points into a tabulated format.

Explain Formulas

If you come across any complicated Excel formula, you can use COPILOT to explain the formula to you in simple language. It helps you to learn complex formulas with less effort.

Go to the workbook that contains the formula for which you need a step-by-step explanation.

Select a blank cell and type in the following text as the prompt, such as in F3:

Explain how this formula works step by step

Now, select a cell where you want the explanation and enter the following formula. Suppose that’s cell F6:

=COPILOT(F3,FORMULATEXT(C6))

In the above formula, C6 is the cell that contains the Excel formula is being explained by the COPILOT function.

Hit Enter, and the COPILOT formula should generate a text as an explanation.

Select the destination cell and click on the Wrap Text command in the Alignment block of the Home tab to see the full explanation.

Conclusions

So, now you know how to use the COPILOT function in Excel in various scenarios that you might come across at work or school.

Did the article help you learn COPILOT in Excel? Comment in the box provided below to share your feedback.