With the Copilot Pro subscription, Microsoft 365 Personal or Family subscribers also get an AI companion by their side in Excel. Copilot can help you analyze, understand, and visualize your data with ease in Excel, enhancing your efficiency in interacting with a dataset. Here's everything you need to know about it.

Note: Copilot in Excel is currently in Preview and thus, only available in English language.

Getting Started with Copilot in Excel

Other than active Copilot Pro and Microsoft 365 subscriptions, there are a few things that need to be done in order to be able to use Copilot in Excel.

First of all, you should have an Excel table with data in a worksheet; otherwise, the Copilot button will not become active.

It's easy to turn your data into an Excel table.

  1. Open the workbook that has your data.
  2. Then, select the cell or range of cells in question.
  3. Go to the 'Format as Table' option from the 'Home' ribbon.
  1. Select a table style.
  1. Checkmark the option for 'My Table has headers' if you want the first row of your data to appear as a table header, and click 'OK'.
  1. You now have an Excel table at hand.

Second, your worksheet should also be saved, and that too on OneDrive or SharePoint for Copilot to become available.

Once you've saved the sheet to the cloud, Copilot will become clickable.

Using Copilot in Excel

Now, you can use Copilot in your Excel worksheet to get assistance with various tasks listed below.

Copilot works for even large Excel tables – tables with up to two million cells. However, with large tables, it can take a long time (more than 30 seconds) for Copilot to generate a response. Options like highlight, sort, filter, and formula column suggestions have no limit on the size of the table or the amount of data.

  1. To Use Copilot, open your Worksheet.
  2. Then, go to the 'Home' tab.
  1. Click the 'Copilot' icon from the Home ribbon.
  1. The Copilot chat panel will open on the right.
  2. If your workbook already has Copilot active, but you don't have an Excel table for your current data, Copilot can convert it for you.
  1. To interact with the table using any of the skills mentioned below, make sure that you have a cell or data range in the Excel table selected.

Find Insights with Copilot

  1. From the Copilot chat panel, click on 'Analyze'.
  1. Copilot will analyze your data and show some prompts that you can use to get insights based on your data. Click on a prompt to use it.
  1. You can also type your prompt in the prompt area if you know what insights you'd like to gather about your data from Copilot. Click on the 'Send' button to send your request.
  1. Copilot can show insights as PivotTable objects, charts, summaries, trends, or outliers. It will generate what best serves your request. To add a chart to the sheet, click on the 'Add to a new sheet' button.
  1. Click on another suggestion to get more insights. Or click on 'Refresh' to get a fresh set of prompt suggestions.

Analyze your data for various prompts and review the insights Copilot generates to get the most out of your data.

Highlight and Sort your Data

Copilot can also easily highlight your data according to simple conditional formatting rules or sort and filter your tables for you.

  1. From the Copilot panel, click on 'Highlight' or 'Sort and filter'.
  1. Copilot will analyze your data.
  2. Once the analysis is complete, it'll suggest prompts you can use on your data. No matter which category you choose, the suggested prompts will have options for highlighting, sorting, and filtering the data.
  3. Click on the prompt you want to process the data for.
  1. You can also enter your own prompt to instruct Copilot how you'd like to change your table.
  2. Depending on the prompt, Copilot will make the changes to your table.

Generate Formula Columns

Copilot in Excel can also help you come up with formulas for a new column and add the column for you.

  1. From the Copilot chat panel, click the 'Add formula columns' option.
  1. Copilot will come up with a few formula columns you can add; click on a suggested prompt to use it.
  1. You can also describe what column you'd like to add in your own words and send the prompt to Copilot.
  2. The AI will then provide a formula suggestion based on the request. You can also see an explanation for how to formula works.
  1. If you want to add it to your sheet, simply click on the 'Insert Column' button.

Copilot in Excel provides a powerful way to analyze data and gain insights more efficiently. By leveraging advanced techniques like machine learning and natural language processing, Copilot serves as an intelligent assistant that augments the capabilities of Excel users. While the tool is still in preview, Microsoft is continuously improving Copilot based on user feedback. As the capabilities expand to support more languages and data types, Copilot has the potential to transform how people work with data in Excel.