Analyze and work with tables using Microsoft 365 Copilot in Excel

  • 4 minutes

If you're not sure how to write a formula for a new column, Microsoft 365 Copilot 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:

Suggest a formula column.

In this simple prompt, you start with the basic Goal: to create a new column with a formula. However, there isn’t enough detail to determine what the formula should calculate.

Element Example
Basic prompt:
Start with a Goal		 Suggest a formula column.
Good prompt:
Add Context		 Adding Context helps Copilot understand what the formula should calculate.

"...for column J to determine the engagement ratio of each campaign."
Better prompt:
Specify Source(s)		 The Source for this prompt is the specific columns needed for the calculation.

"...using the values from 'Engaged Users' and 'Total Users Targeted'."
Best prompt:
Set clear Expectations		 Adding Expectations helps Copilot structure the formula correctly.

"Ensure the formula divides 'Engaged Users' by 'Total Users Targeted' and formats the result as a percentage."

Note

Crafted prompt:

Suggest a formula for column J to calculate the engagement ratio of each campaign. Use the values from 'Engaged Users' and 'Total Users Targeted'. Ensure the formula divides 'Engaged Users' by 'Total Users Targeted' and formats the result as a percentage.

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.