Excel's new Copilot function turns your prompts into formulas – how to try it

ZDNET’s key takeaways

• A new COPILOT function in Excel lets you use AI in a formula.
• The new skill is now available to Microsoft 365 insiders.
• Reduces some of the complexity involved in creating formulas.

Get more in-depth ZDNET tech coverage: Add us as a preferred Google source on Chrome and Chromium browsers.

Using functions in Excel can sometimes be challenging, especially if you’re trying to devise complex formulas to work with your data. Now Microsoft is tapping into AI in an attempt to ease the process.

New COPILOT function

On Monday, the company announced that its Copilot AI is now available as an Excel function for Microsoft 365 insiders. That means you can add COPILOT to a formula and then use natural language in a prompt to tell Excel what you want to achieve.

You’re able to use the new COPILOT function alongside existing functions such as IF, SWITCH, LAMBDA, and WRAPROWS. Alternatively, you can use the results from other formulas as part of your prompt. If your data changes, the resulting formula using Copilot automatically changes. So you don’t have to redo or revise the prompt or refresh the results on your own.

Also: I use Edge as my default browser – but its new AI mode is unreliable and annoying

“It can be painful and time-consuming to wrangle data, summarize feedback, categorize information, and brainstorm ideas,” Microsoft said in its blog post. “The new COPILOT function in Excel for Windows and Excel for Mac is here to save time and supercharge your workflows. Just enter a natural language prompt in your spreadsheet, reference cell values as needed, and watch Copilot instantly generate AI-powered results.”

How to try COPILOT

To try out the new COPILOT function, you need to be a Microsoft 365 insider on the Beta channel and be a Microsoft 365 Copilot subscriber. If you qualify, here’s how it works.

Open an Excel spreadsheet with the data you wish to use. Enter the following code into any cell: =COPILOT(prompt_part1, [context1], [prompt_part2], [context2], …). Replace the Prompt_part string with the text that describes the task or request for Copilot. Replace the context strings by referring to the grid for the data, such as a single cell or a range of cells.

Also: Microsoft Copilot 3D turns your 2D images into 3D models for free – how to try

As one example offered by Microsoft, let’s say you’ve created a spreadsheet that collects comments from fellow employees about a new coffee machine in the office. Typically, you’d have to manually read, tag, and summarize the data. With the COPILOT function, you could instead write a formula such as =COPILOT(“Classify this feedback”, D4:D18). In response, the AI generates the results and displays them in the selected cells.

Ways to use COPILOT

In its post, Microsoft suggested a few ways to try the new function.

Spur ideas. Maybe you’re setting up a marketing campaign or designing new features for a product. With the COPILOT function, you can brainstorm different ideas directly in Excel. You could also try out different SEO keywords based on a product description.

Generate summaries. With the COPILOT function, you can summarize large ranges of data into more concise and digestible information. That might be especially handy for other people who need to view or work with your spreadsheet and don’t have time to pore over every piece of data.

Classify data. You could use the COPILOT function to categorize customer feedback, technical support tickets, survey responses, and other data. Doing so eliminates the need to export the data into another tool for in-depth analysis.

Create lists or tables. With the COPILOT function, you’re able to create lists and tables of your existing data. The AI can deliver multi-row, multi-column tables that integrate directly into your spreadsheet.

Also: Microsoft gives Copilot a face – here’s how to try your new Appearance chat buddy

Further, Microsoft has served up a few tips to help you better use the COPILOT function.

As with an AI prompt, the way you write it makes a difference. Make sure your instructions are clear, that you specify the cells to include in the formula, indicate the order in which you want to see the results, and tell it whether you need a list or table.

Use action words like “summarize,” “categorize,” or “rank” in the formula. You can also provide Copilot with examples if you need the results displayed in a certain style or format.

Limitations

For now, Copilot in Excel can’t directly access live web data or internal business documents. If you need to work with such data, you’ll have to first import it into your spreadsheet. Microsoft says that support for live web data and internal documents will be added down the road.

The COPILOT function supports 100 calls every 10 minutes and up to 300 calls per hour. If you need more, you may want to try passing arrays as a single call to include a larger range of data, which counts as only one usage. Microsoft says that it will expand the number of allowed calls over time.

Also: How to create a drop-down list in Excel and save yourself hours of time

The COPILOT function is optional and is added to your spreadsheets only when you choose to use it. Further, Microsoft promises that any data sent through the function remains confidential and won’t be used to train or improve its AI models.

Though the new function is now limited to Microsoft 365 Copilot subscribers, it will soon expand to Excel for the web through Microsoft’s Frontier program.

As someone who struggles with functions and formulas in Excel, I think the new COPILOT feature is a cool move. Though I avoid relying too much on AI, this seems like a helpful way to make Excel more accessible and advanced formulas easier to design.