Microsoft, in its ongoing effort to AI-ify every product it has, is now adding it right into the cells of Excel. Available on Monday to beta users of Microsoft 365 Copilot, a new COPILOT function allows you to task Redmond’s AI with performing generative tasks right in, for example, C2 or B23.
It’s a smart strategic move on the part of the software behemoth, which is trying to justify its AI datacenter buildout by bundling Copilot into anything and everything. Excel is perhaps the stickiest of Microsoft Office products, essential and irreplaceable for many finance functions.
In a blog post and YouTube video, Head of Product Excel Core Catherine Pidgeon explains the advantages of the new function and how to invoke it.
As with any function in Excel, you preface it with an equals operator, then put your prompt in quotes inside parentheses. You follow that with a comma and then add any “context” (aka the address of any specific cells) you want Copilot to look at.
For example, in the video, Pidgeon and Principal PM Manager Joe McDaid use the COPILOT function to examine the sentiments in a spreadsheet full of comments about a coffee machine. The individual comments appear in the D column, starting at row four and ending at row 18. McDaid places a COPILOT formula below the data at cell D21. The formula reads:
=COPILOT(“Summarize this feedback into a paragraph”,D4:D18)
After a few seconds of processing, cell D21 shows a summary of all of the coffee machine reviews that reads “The coffee machine is generally praised for its ease of use, quick operation, and the fresh, consistently . . . “
McDaid then changes the formula’s prompt to read “Summarize this feedback into a sentence,” and it recalculates into shorter text. Summarization, particularly sentiment summarization, is something new that GenAI brings to the table, which you can’t do normally in Excel.
You can also use the COPILOT function to sort data into a predefined set of categories. In another demo, McDaid has a list of four categories – Taste, Ease of Use, Noise, and Other – that he wants the function to assign to each of the 15 response rows. So he lists those four categories in cells B4 to B8.
Then, in cell E4, right next to the column with the coffee machine reviews, he enters the following formula:
=COPILOT(“Categorize this feedback”,D4:D18,”into one of these categories”,B4:B8)
This formula shows that the COPILOT function supports multiple prompts and contexts. After a second or two of processing, the E column, from E4 to E18, fills with categories that match the feedback of the cells in column D. For example, Copilot assigns the comment “This machine operates so quickly” to the Ease of Use category.
In the next step, McDaid adds column headers for Sentiment and Emoji in F3 and G3, right next to the column header for Category in E3. Then he changes the formula in cell E4 to read:
=COPILOT(“Categorize this feedback”,D4:D18,”into one of these categories”,B4:B8,”return these columns”,E3:G3)
Excel then fills in all three columns next to each coffee machine review with a Category (ex: Ease of Use), a Sentiment (Positive or Negative), and an emoji of a smiley or frowny face. He then shows how these columns can roll up into a pivot table.
Bringing in outside information
In the blog post, Pidgeon also provides an example of the COPILOT function bringing information from the AI’s knowledge graph into a spreadsheet. An animated GIF shows a user entering:
=COPILOT(“List airports codes from major airports in”,E3)
The E3 cell has the name of a country in it. When E3 reads USA, cells D5 to D14 have US airport codes such as ATL, LAX, and JFK. When the user changes E3 to name Australia as the country, the airport codes change to SYD, MEL, BNE, etc.
In the blog post, Pidgeon also explains that you can nest the COPILOT function inside other formulas or nest other formulas inside of it. For example, in the blog post, an animated GIF shows someone using WRAPROWS around the list of airport codes like so:
=WRAPROWS(COPILOT(“List airports codes from major airports in”,E3),3,””)
The airport codes then appear in a grid formation that’s three columns across and three rows tall.
Microsoft will almost certainly expand the availability of the COPILOT feature. But, right now, you need to have Microsoft 365 Copilot, which costs $30 per user per month and you need to join the Microsoft 365 Insider program in the beta channel. ®