Copilot in Excel Issues

How to Use Copilot in Excel to Find Functions and Write Formulas

Khadija KhanKhadija Khan Nov 10, 2026 869 views

Question details

The user wants to know how to leverage AI tools like Copilot in Excel to automatically generate formulas and find the right functions for specific data tasks.

How to Use Copilot in Excel to Find Functions and Write Formulas
Product
Microsoft Excel
Device & OS
not provided
Scenario
Writing complex formulas and searching for specific functions, such as extracting text from an email address.
Observed behavior
The user needs a guided way to prompt Excel's Copilot to suggest functions, explain formula logic, and provide working examples for data manipulation.
Before you start

Ensure you have an active Microsoft 365 subscription with Copilot enabled, and that your Excel data is formatted as a Table (Insert > Table), as Copilot typically requires table formatting to function correctly.

Solution 1Recommended

Prompting Copilot for Formula Generation

Use natural language prompts in the Copilot pane to ask for specific formulas or function explanations.

Excel Copilot can translate your natural language requests into ready-to-use formulas. For example, if you need to extract specific parts of a text string, you can describe the outcome you want, and Copilot will suggest the appropriate functions.

1
Open Copilot

Launch Excel and click on the 'Copilot' icon located on the Home tab of the ribbon.

2
Enter your prompt

In the Copilot chat pane, clearly state what you want to calculate or extract. For example, type 'Show me an example of how to use TEXTAFTER to extract the domain from email addresses in column A'.

3
Apply the suggested formula

Copilot will explain the logic and provide a formula like =TEXTAFTER(A1,"@",,1). You can copy this formula or click 'Insert' to apply it directly to your worksheet as an additional column.

Prompting Copilot for Formula Generation
Formatting Requirement: Copilot works best when your data has clear headers and is stored in OneDrive or SharePoint with AutoSave enabled.
AI Formula Assistant

Write Complex Formulas Effortlessly with WPS AI

If you don't have access to a Microsoft Copilot subscription, WPS Office offers a powerful, built-in AI assistant for spreadsheets. WPS AI can automatically generate formulas, analyze data, and explain complex functions based on your simple text descriptions.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your existing workbook or create a new one.
  2. 2. Activate WPS AI: Click on the 'WPS AI' tab in the top ribbon to bring up the AI assistant panel.
  3. 3. Describe Your Needs: Type what you want to achieve, such as 'Extract the domain name from the email in cell A1', and WPS AI will instantly generate the correct formula for you to insert.
Generate and explain complex formulas using simple natural language prompts.Fully compatible with Microsoft Excel (.xlsx) file formats and standard functions.Free and lightweight alternative to Microsoft 365 installations.Built-in AI capabilities designed for intuitive data analysis and manipulation.
microsoft office alternative - wps office

Frequently Asked Questions

Why is the Copilot button missing in my Excel?

Copilot availability depends on your Microsoft 365 plan. You typically need a Copilot Pro or Enterprise license. Additionally, ensure your Excel app is updated to the latest version and that your file is saved in OneDrive or SharePoint.

Can Copilot explain formulas I've already written?

Yes. If you select a cell containing a complex formula, you can ask Copilot in the chat pane to 'Explain the formula in this cell', and it will break down the function's logic step-by-step.

Does Copilot work with non-table data?

Currently, Copilot in Excel is optimized for data formatted as Tables. If your data is in standard ranges, Copilot may prompt you to convert it to a Table before it can analyze it or suggest new formula columns.