Spot trends and visualize data with Copilot in Excel

  • 4 minutes

Microsoft Copilot in Excel helps you do more with your data in Excel tables by generating formula column suggestions, showing insights in charts and PivotTables, and highlighting interesting portions of data.

In Excel, select Copilot on the ribbon to open the chat pane. You need to have an Excel table that includes data in a worksheet before using Copilot.

Screenshot of the Copilot icon in the Excel ribbon.

Quickly turn a range of cells into an Excel table by following these steps:

  1. Select the cell or the range in the data.

  2. Select Home > Format as Table.

  3. In the Format as Table dialog box, select the checkbox next to My table has headers if you want the first row of the range to be the header row.

  4. Select OK.

Screenshot of the Copilot panel in Excel upon first opening.

Let's get crafting

First, download Contoso Chai Tea market trends 2023.xlsx and save the file to your OneDrive folder if you haven't yet done so.

Open the spreadsheet in Excel and then open the Copilot pane by selecting the Copilot icon in the ribbon's Home tab. Enter the prompts below and follow along.

Note

Starting prompt:

Analyze this table in Excel.

In this simple prompt, you start with the basic Goal: to analyze an Excel table. However, there's no information about why the table needs to be summarized or what the summary is needed for.

Element Example
Basic prompt:
Start with a Goal		 Analyze this table in Excel.
Good prompt:
Add Context		 Adding Context can help Copilot understand the purpose of the analysis and adjust the response accordingly.

"We're looking for the top selling products from May through August to inform our marketing strategy for next year."
Better prompt:
Specify Source(s)		 Adding Sources can help Copilot narrow down the scope by telling it to use specific information or ranges.

"...from May through August..."
Best prompt:
Set clear Expectations		 Lastly, adding Expectations can help Copilot understand how to format the summary and what level of detail is required.

"Please highlight the top selling product for each store and month."

Note

Crafted prompt:

Analyze this Excel table. We're looking for the top selling products from May through August to inform our marketing strategy for next year. Please highlight the top selling product for each store and month.

Screenshot of the results of the crafted prompt using Copilot in Excel.

This prompt gives Copilot everything it needs to come up with a good answer, including the Goal, Context, Source, and Expectations.

Explore more

Try out the final crafted prompt and others with your own Excel table. Here are some suggestions for prompts you might want to try. Copy them and add Context, Sources, and Expectations.

  • Plot sales by category over time.

  • Show total sales for each product.

  • Show the total of advertising sales for each region last year.

Important

This feature is available to customers with a Copilot for Microsoft 365 license or Copilot Pro license. To learn more about Excel tables and how to create them, see Create a table in Excel.