Analyze and work with tables using Copilot in Excel

  • 4 minutes

If you're not sure how to write a formula for a new column, Copilot for Microsoft 365 can quickly add new columns with formulas based on your data.

  1. With your data formatted as a table, select the Copilot button in the ribbon.

  2. Select Add formula columns or Show suggestions for formula columns. You can also describe what columns you’d like to add by typing it in your own words.

  3. Copilot provides formula suggestions with an explanation on how each formula works. View the explanation by selecting Explain formula.

  4. Select Insert column to add the formula column to your table.

Important

As with any AI-generated content, it’s important to review, edit, and verify anything Copilot creates for you.

Let's get crafting

First, download Fabrikam Q1 marketing campaigns.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:

Explain this formula.

In this simple prompt, you start with the basic Goal: to explain an Excel formula. However, Copilot doesn't know which formula or what you don't understand.

Element Example
Basic prompt:
Start with a Goal		 Explain this formula.
Good prompt:
Add Context		 Adding Context can help Copilot understand what you don't understand about the formula.

"We need to understand how it calculates user engagement."
Better prompt:
Specify Source(s)		 The Source for this prompt is assumed to be the table we're working with in Excel, but you can get even more specific.

"...from column J of the 'Table1' table in the Excel spreadsheet."
Best prompt:
Set clear Expectations		 Lastly, adding Expectations can help Copilot understand how you want the formula explained and broken down.

"Please provide a step-by-step breakdown of the formula, including its arguments and syntax. Please suggest additional formulas that would be useful to determine return on investment."

Note

Crafted prompt:

Explain the formula from column J of the 'Table1' table in the Excel spreadsheet. We need to understand how it calculates user engagement. Please provide a step-by-step breakdown of the formula, including its arguments and syntax. Please suggest additional formulas that would be useful to determine return on investment.

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

Copilot has all the info it needs to give you a solid answer, thanks to the Goal, Context, Source, and Expectations in this prompt.

Explore more

Use these prompts as a starting point. Copy and modify them to suit your needs.

  • Calculate total cost per product in a new column.

  • Add a column that calculates the total profit for each marketing campaign in 2022.

  • Add a column that calculates the number of days after the product launch event.

For more information, see Generate formula columns with Copilot in Excel.