Excel 2024: Using Artificial Intelligence with Copilot for Excel

In December 2023, Microsoft began a preview of their new Copilot feature. The results are promising.

Copilot is a broad term for several features at Microsoft. Each of the apps Excel, Word, PowerPoint, Outlook and Teams have different functionality. There is also a Business Chat feature in Copilot that uses a Large Language Model along with data in your calendar, e-mails, chats, documents, meetings and contacts to perform tasks. The descriptions below are of the Copilot tools in Excel.

Microsoft describes Copilot will unlock a new wave of productivity growth by allowing you to interact with your data using natural language.

Work with your I.T. administrators to get access to the Copilot preview. Your data has to be stored in OneDrive and you have to format the data as a Table using Ctrl+T or Home, Format as Table.

You will see a Copilot icon on the right side of the Home tab. Click the icon and a Copilot Preview pane will open on the right. The four main sections are:

• Add new column of formulas
• Highlight certain data using conditional formatting
• Sort and/or filter your data
• Analyze your data using pivot tables or pivot charts

This figure shows a sample data set. There are columns for Revenue, COGS and Profit.

Say that you want to add a column to calculate Gross Profit Percent. In my company, this is generally abbreviated as GP%. I click in the Copilot prompt box and type "Add a calculation for GP"and press Enter.

Excel takes tens of seconds to process the question. There are various status updates where it says it is working on understanding the question, then understanding the data.

It eventually comes back with the figure below. They've suggested a valid formula to calculate gross profit percent. Just as a note here, if this was my data, I would have come up with a simpler calculation of Profit divided by Revenue, but (Revenue COGS) divided by Revenue also works.

---

---

Note that your choices are to have them explain the formula or to Insert the formula as a new column.

If you open the section to explain the formula, it does a good job of explaining how the formula will work, as shown here.

Hover over Insert Column and a preview of the new column appears.

Click Insert Column and you have a suitable formula added to your data set as shown here.

In the Copilot pane they report that they added the column. There is a large button to Undo. There are also feedback buttons where you can report back to Microsoft if the answer worked or not.

I tried a prompt of "Highlight all rows where GP% is in the top 20%". Copilot answered "For now, I can only do that for columns in tables. Try asking me to apply formatting in a table column instead."

As shown below, I then changed the prompt to "Highlight cells where GP% is in the top 20%". After 10-15 seconds, Microsoft has set up conditional formatting to highlight the top 20% of GP% values.

The yellow highlighting appears in the data. To check the logic, I open Home, Conditional Formatting, Manage rules and the rules are correct, as shown here.

Here are some other examples of calculations created by Copilot:

Running total for revenue:

=SUM(INDEX([Revenue],1):[@Revenue])

Calculate a Bonus of 2% of revenue if GP% is greater than 55%:

=IF([@[GP%]] > 0.55, [@Revenue] * 0.02, 0)

Calculate a bonus of 2% of revenue if the Product is ABC and the GP% is in the top 20%:

=IF(AND([@Product]="ABC",[@[GP%]]>=PERCENTILE.INC([GP%],0.8)),[@Revenue]*0.02,0)

Calculate a $100 bonus if the Quantity is a prime number:

=IF(ISNUMBER(MATCH([@Quantity],{2,3,5,7,11,13,17,19,23,29,31,37,41,43,47,53,59,61,67,71,73,79,83,89,97},0)),100,0)

The first three are correct. The fourth one will work for some rows but not other rows. It does represent a clever approach and certainly better than when Chat-GPT tried to make up a non-existent ISPRIME function.

Here, I asked Excel to sort by two columns and it had no problem with this.

In the figure below, I asked which customer had the highest revenue. The preview of the pivot table gives me the correct answer.

It is Not Perfect Yet

Currently, Copilot seems to fail with some slightly more complex commands:

Highlight all dates in the month of February: the concept of February confused Copilot. It offered to highlight everything in the current month.

Create a pivot table with revenue by year and month and format revenue as currency: Copilot could create the pivot table but could not apply formatting to the revenue values. And then, when I asked it to format the pivot table, it reported that it only worked with tables and not with pivot tables.

Highlight the revenue amount that is the maximum revenue for each customer. Copilot instead highlighted the top revenue overall and not for each customer.

Although it is not perfect, the feature is still in preview and shows a lot of promise. As the team at Microsoft gets feedback from the big companies in the preview, they will be able to improve the results.