How to use Copilot in Excel with examples

This article will take you on a fascinating journey through Excel Copilot, examining its various features, revealing its untapped potential, and showing how to use it effectively.

In the world of spreadsheet software, Excel has long reigned as the go-to tool for crunching numbers and analyzing data. But what of you had a smart assistant helping you navigate Excel's vast capabilities, giving you expert advice and quick insights. That's the idea behind Copilot - the newest and most innovative feature of Excel. Like a reliable wingman, Copilot uses artificial intelligence (AI) to change the way we interact with spreadsheets.

What is Copilot in Excel?

Excel Copilot is an innovative feature that utilizes artificial intelligence (AI) and machine learning algorithms to enhance the user experience in Excel. By integrating these advanced technologies, Copilot provides users with intelligent suggestions, formulas, and insights as they work on their spreadsheets. By analyzing patterns, context, and user behavior, Copilot anticipates the user's needs and offers relevant assistance in real-time.

In simple words, Excel Copilot is like having a smart helper right inside your spreadsheet. It uses artificial intelligence, which is a type of technology that makes computers think and learn like humans. When you're working on your Excel sheets, AI watches what you're doing and suggests helpful things to make your work easier.

For example, if you need to do a calculation or make a chart, you can just ask Copilot using regular words, and it will figure out what you need and help you do it. It's like having a friend who knows a lot about Excel and gives you tips to get your work done faster.

Overall, Copilot doesn't extend beyond what you could accomplish yourself by using various Excel features and functions. The advantage is that you don't have to master Excel's language. Now, you have a single entry point to communicate your intentions to Excel in your own language. This saves you time and makes using Excel easier, especially if you're not an expert at it. Copilot in Excel

How to access Copilot in Excel

To get access to Copilot features in Excel, all you need to do is to purchase the Microsoft 365 and Copilot subscriptions and install the Excel app:

  1. Get Microsoft 365 subscription. Ensure you have a Microsoft 365 subscription that supports Copilot, such as Microsoft 365 Personal, Family, or Business.
  2. Obtain Copilot subscription. Choose the Copilot plan corresponding to your Microsoft 365 subscription and purchase it. If your organization has purchased Copilot for you, ask your admin to log into the Microsoft 365 admin center and assign a Copilot license to you.
  3. Install Excel 365 or log into a web app. Install Excel 365 on your desktop and sign in with your Microsoft account. Or log into the Excel web app using your Microsoft account.
  4. Look for Copilot icon. Once you have purchased the required subscriptions, the Copilot icon will show up in your Excel ribbon, granting access to its features.

Note. For the Copilot icon to appear in desktop Excel, you may need to restart the application and sync your Microsoft 365 settings. If you're using Excel on the web, simply refresh the page.

What Copilot in Excel can do

Copilot in Excel is a powerful tool that can help you visualize, analyze and transform data in your spreadsheets in many different ways. Here's what it can do for you:

  • Data analysis. Copilot looks at your tables and brings important information into focus. You can then dig deeper by questioning Copilot.
  • Modeling. When working on financial modeling, you can explore different scenarios by asking "what-if" questions and providing prompts such as "model how a change to X would affect Y"
  • Visualizations. AI helps you create charts and graphs to visualize your data in a clear and understandable way.
  • Writing formulas. Copilot assists in writing formulas for your calculations, making complex formula creation easier and more accessible.
  • Organizing and cleaning data. Copilot can quickly clean up messy data for you. It can remove extra spaces, merge values from different columns, highlight duplicates, sort, filter, and more.
  • Suggestions. Get intelligent recommendations for formulas, functions, and actions based on your context.
  • Insights. Uncover interesting insights within your data, like trends, outliers, or correlations between different parts.
  • Automating tasks. Copilot can even write VBA code to help you automate common tasks. This is like creating kind of shortcuts for things you do often.

In summary, Copilot in Excel is a handy helper that makes your work easier and helps you get more done without all the hard work.

How to use Copilot in Excel

Getting started with Excel Copilot is really easy and intuitive. Just follow these simple steps:

  1. Run Excel. Launch Excel 365 on your computer, or if you prefer, log into the Excel web app using your Microsoft account.
  2. Open cloud-stored workbook. Open the workbook stored on OneDrive or SharePoint where your data resides.
  3. Activate Copilot. On the Home tab of the Excel ribbon, click the Copilot button to activate it. Click the Copilot button on the Excel ribbon to start using it.
  4. Turn your data into a table. Copilot can only work with Excel tables, so if your data isn't already in table format, convert it. You can do this yourself by pressing Ctrl + T or let Copilot convert it for you. Convert data into a table.
  5. Use Copilot. In the Copilot pane, either follow the suggested options or type your request directly into the text box. You can also explore additional prompts provided above the chat box. Use the options suggested by Copilot or type your request.
  6. Review and apply results. As Copilot fulfills your request, it may directly implement the solution into your sheet for tasks like highlighting, sorting, and filtering. For actions such as adding formula columns and creating visuals, Copilot will display the results in the pane. Here, you can click on Explain formula for detailed information or hover over action buttons like Insert column to preview the results. If everything looks good, just click the Insert button to add the result to your worksheet. Review and apply Copilot's results.

If you encounter unexpected results, don't worry! Copilot has the handy Undo button for quick corrections. Copilot has the Undo button for quick corrections.

Explore more prompt suggestions

To get more prompt ideas, click the View Prompts icon at the bottom of Copilot's pane. From there, you can explore the suggested categories such as Create, Understand, Edit and Ask. By delving into a category that aligns with your data's nature, Copilot will provide you with a variety of prompts that you can use directly or modify to suit your needs.

Explore more prompt suggestions.

Tips and notes:

  • If the Copilot button appears inactive (grayed out) in your Excel, it's likely because you've opened a file stored locally on your computer. Copilot can only operate on files stored on OneDrive or SharePoint. To activate Copilot, try saving your file to the cloud or open another file directly from the cloud.
  • When making custom queries or analysis requests, provide as much detail as possible. Clearly identify the columns by their names. The more specific you are, the better Copilot can help.
  • If you don't have your own data to experiment with, then you can start with a sample table that Copilot has ready for you.
Start with Copilot's sample table.

Excel Copilot examples

Now, let's explore some practical examples of how Copilot can be used in Excel. By looking at the suggested categories, you can gain insight into the various functionalities and tasks that Copilot can accomplish.

Add formula columns with Copilot

You need to calculate something in your dataset but unsure how to write a formula? Ask AI to build a formula for you:

  1. Click on Add formula columns in the Copilot pane.
  2. Select one of the suggested prompts or describe your calculation needs in your own words.
  3. Copilot will write a formula and explain how it works.
  4. Choose the desired action:
    • To better understand the formula, click Explain formula.
    • To preview the results, hover over the Insert formula button.
    • To insert the results directly into the worksheet, click the Insert formula button.

Note. As with any AI-generated content, it's essential to review and verify anything Copilot creates for you.

Let's begin by exploring the suggested prompts: Add a formula column with Copilot.

Copilot has added this formula, which compares the revenues of Q1 or Q2:

=IF([@[Q1 Revenue]]>[@[Q2 Revenue]], "Q1", "Q2") A formula column added by Copilot.

Let's ask for a more complex calculation. For instance, you can tell Copilot to create a column that shows the percent difference between Q1 and Q2 revenues.

Nice! Copilot has generated an accurate percent change formula and automatically applied the Percent format to the newly added column: A custom formula written by Copilot on request.

Furthermore, when tasked with projecting revenues for Q3, Copilot also performed nicely: Copilot has projected revenues based on available data.

Here are a few more formula requests that Copilot successfully executed for our sample dataset:

  • Add a column to calculate the total revenue for Q1 and Q2.
  • Add a column that calculates the profit margin (assuming Revenue and Profit columns are present).
  • Add a column that calculates the product duration in days (assuming the project Start date column is available).

Get multiple formula columns at once

With the latest April 2024 update, Excel Copilot becomes even more efficient at creating formulas. Now, you can have multiple formula columns created with a single prompt. Just take a look at how Copilot solves a common problem of splitting full names into first and last names.

  1. You provide Excel Copilot with a prompt, such as "Split the name column into first name and last name". Or you can be more precise and mention exactly which column you want to extract the information from, like "Extract the first and last name from column C and insert into 2 new columns".
  2. Copilot then suggests the appropriate formulas, which you can copy and paste manually.
  3. Alternatively, you can review the suggested formulas, and with a single click, insert them into your worksheet.
Create multiple formula columns at once.

In our case, the formulas suggested by Copilot align perfectly with those you'd write manually: Excel formulas to split names.

This feature not only saves time but also reduces the likelihood of errors, making your data management more reliable.

Highlight with Copilot

With Copilot, you can quickly make important information in your sheets stand out using colors. Here's how to do it:

  • Click Highlight in the Copilot pane.
  • Follow the given prompts or specify your highlighting preferences in the chat box.

For the most part, Copilot highlights values with conditional formatting, so all the changes made to your worksheets are dynamic in nature. That is, when your data changes, the formatting will update automatically to reflect those changes.

For example, you can ask Copilot to highlight the top n values in a key column, and it will do so for you. Highlights certain values with Copilot.

Here are a few other prompts you can try:

  • Highlight revenues over 50,000.
  • Highlight bottom 10 revenues.
  • Highlight duplicate rows.

Note. At this time, Copilot can only color cells based on simple conditional formatting. It is unable to highlight values with two or more conditions. For example, Copilot is unable to fulfill a request like this: "highlight rows that have "Development" in the Project type column and % Change lower than 3%".

Sort and filter with Copilot

Sorting and filtering data in Excel tables is usually simple, thanks to the built-in filter buttons in each column. Copilot can offer additional assistance, especially when filtering across multiple columns. Here's how to get started:

  1. Click Sort and Filter in the Copilot pane.
  2. Choose from the suggested prompts or explain what you're looking for in the chat box.

For instance, you can instruct Copilot to filter "Design" projects where the percentage change is negative. In a moment, you'll observe that only two such projects exist in your dataset: Filter by two columns using Copilot.

Analyze with Copilot

And now, we're diving into the most captivating aspect: analyzing with Copilot. To provide Copilot with more data to work with, I've expanded our sample table by adding a few extra columns as follows: Source table

To start your analysis with Excel Copilot, you have a couple of options:

  • Navigate to the Analyze category in the Copilot pane and choose from the suggested prompts.
  • Alternatively, you can opt to Show data insights.
Start your analysis with Excel Copilot.

The main way that Copilot conducts its analysis is through pivot tables and pivot charts. When tasked to show data insights, it generated 2 pivot tables and 6 visuals that vividly illustrate various aspects of our data. Here's what we can glean from these graphs:

  • Design projects yield the highest profits.
  • Research projects experienced the most significant percentage increase in revenue from Q1 to Q2.
  • Profit margins for most projects fall within the 3% to 10% range.
  • The most common budget range is between $56K and $71K.
  • There is a strong correlation between budget and revenue.
Copilot insights

If you wish to delve deeper into the analysis, feel free to do so. For example, you can request a breakdown for specific data categories: Ask Copilot to do a breakdown for specific data categories.

Additionally, you can explore various What-If scenarios. Just remember to be accurate and specific with your queries. Copilot thrives on clarity and precision and may struggle to respond to vague or ambiguous questions, as illustrated in the example below: Be accurate and specific when setting tasks for Copilot.

Visualize with Copilot

In case the visuals suggested by Copilot for your data analysis don't quite match your expectations, fear not. You can directly instruct Copilot to create the desired graph by specifying the chart type and the data categories to include.

For instance, you might ask Copilot to generate a bar graph showing Q1 – Q3 revenues by project for the project type "Development": Visualize with Copilot.

Automate tasks with Copilot (write VBA code)

Copilot can help automate your daily routines by generating VBA code. Not only can Copilot write the code for you, but it also provides brief instructions on how to integrate it into your workbook and execute the macro.

For example, Copilot has written perfect VBA code to hide all worksheets in the current workbook except the active one. Write VBA code using Copilot.

Excel Copilot benefits

Excel Copilot makes working with spreadsheets easier in many ways. Here are the key benefits you can expect:

  • Natural language queries. You can ask Copilot questions using normal words, and it will give you answers in charts, tables, or formulas. Simply type your questions or command in everyday language, and Copilot will understand and respond accordingly. This makes it easy to perform tasks without needing to remember specific function syntax.
  • Speed up your tasks. Copilot can help you complete routine tasks, such as data cleaning, sorting, and filtering, much faster. It can identify patterns and anomalies in your data, allowing you to focus on analysis rather than manual data manipulation.
  • Accessible for all skill levels. Copilot is designed to be accessible for users of all skill levels. Whether you're new to Excel or have been mastering it for a while, Copilot is easy to use for everyone.
  • Continuous learning and improvement. AI keeps getting better as it learns from how you use it, so it becomes more helpful over time. This means that the more you use Copilot, the more effective it becomes at understanding your needs and providing relevant assistance.

Limitations of Excel Copilot

While Microsoft Copilot in Excel offers many benefits and advantages, it also has some limitations to keep in mind:

  • Copilot is only available for Excel files (.xlsx or .xlsm) that are stored on OneDrive or SharePoint with AutoSave turned on. When working with unsupported file types or files saved locally on your computer, the Copilot button will remain inactive.
  • Currently, Copilot understands prompts in English, French, German, Italian, Japanese, Brazilian Portuguese, and Simplified Chinese. Also, Microsoft plans to add more languages in the future.
  • It cannot perform certain common tasks such as comparing tables or looking up and pulling matches.
  • It only works within Excel tables and is limited to handling tables of up to two million cells.
  • Copilot can be slow, especially on large tables, sometimes taking 30 seconds or more, and it may even hang in some cases.

Excel Copilot FAQs

After exploring the main features of Excel Copilot, let's get into the technical details and address some common questions.

Which Excel versions have Copilot?

Copilot is only available in Excel for Microsoft 365 (for Windows and Mac) and in Excel on the web.

Is Copilot for Excel free?

No. To have Copilot features integrated in Excel, you'll need to buy a subscription.

What's the cost of Excel Copilot?

At present, there are two subscription options to choose from:

  • Copilot Pro ($20 per user per month) – for Microsoft 365 Personal or Family subscriptions.
  • Copilot for Microsoft 365 ($30 per user per month) – for Microsoft 365 Business Standard, Business Premium, E3, E5, or Office 365 E3 or E5 subscriptions.

To make it clear, Copilot cannot be purchased only for Excel. With either subscription, you'll get it integrated with all your Microsoft 365 apps such as Excel, Outlook, Word, and PowerPoint.

Can Copilot settings be customized in Excel?

Currently, Copilot in Excel cannot be customized. Simply, there are no options available for customization within Excel or in the Microsoft 365 admin center.

Why is Copilot not showing in Excel?

If you're wondering why Copilot isn't showing up in your Excel ribbon when you expect it to, here are important things to check:

  • Ensure you have the required Microsoft 365 and Copilot subscriptions.
  • If your organization purchased a Copilot subscription for you, ensure your admin has assigned it to you in the Microsoft 365 admin center.
  • Make sure you have the latest version of Excel 365 installed on your computer.
  • Verify that you are logged into Excel with the same account associated with your Copilot subscription.

Additionally, to use Copilot for Microsoft 365, you need to meet the requirements for app version, license, network, and privacy settings: Requirements for Microsoft 365 Copilot.

Why is Copilot grayed out in Excel?

If you find that the Copilot button is grayed out in Excel, it's likely because you're working on a workbook stored locally on your computer. To activate Copilot, simply save your file to OneDrive or SharePoint and make sure the AutoSave option is enabled.

Can Copilot's formulas and insights be trusted?

While Copilot in Excel does provide explanations for formulas and insights in a grammatically correct manner, the generated content may sometimes be inaccurate. This can happen due to misunderstandings or misinterpretations of your requests. Copilot openly notifies users with a transparent warning stating that "AI-generated content may be incorrect". Therefore, it's important to double-check the generated formulas and visuals before inserting them into your worksheets.

As we finish up our journey through Copilot in Excel, it's clear that this tool can change the way we work with spreadsheets. It is designed to help us work more intelligently, not more strenuously. Whether it lives up to Microsoft's goals is something you can decide based on your own experience with it.

Practice workbook for download

Using Copilot in Excel - examples (.xlsx file)


  1. Your blogs indeed awsome ! i Requested you to please write article on pivotby and groupby.

Post a comment

Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)