How to Use Copilot in Excel to Create and Explain Formulas
Question details
The user wants to know how to leverage Copilot in Excel to automatically generate formula suggestions from plain language and understand existing complex formulas.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating new formulas to analyze datasets, compare lists, or explaining existing complex formulas in a spreadsheet.
- Observed behavior
- Copilot generates formula suggestions (like IF, MATCH, XLOOKUP) and step-by-step explanations based on natural language prompts provided by the user.
Ensure you have an active Microsoft 365 subscription with Copilot enabled, and verify that your Excel workbook is saved to OneDrive or SharePoint with AutoSave turned on.
Generate Formulas from Natural Language Prompts
Use Copilot to automatically build complex formulas by simply describing what you want to achieve.
Instead of memorizing complex syntax, you can ask Copilot to write the formula for you. This is especially helpful for advanced scenarios like comparing two datasets or handling errors.
Launch your Excel workbook and click the Copilot icon located on the Home tab of the ribbon.
In the Copilot chat pane, type a natural language request. For example: "Write a formula to find values in Address List 2 that are missing from Address List 1."
Copilot will analyze your request and suggest a formula, such as =IF(ISERROR(MATCH(A2,AddressList1,0)),A2,""), or an alternative like XLOOKUP or FILTER.
Hover over the suggested formula and click the Insert button to apply it to your selected cell or column.
Ask Copilot to Explain an Existing Formula
Break down and understand complex, nested formulas created by others or generated by AI.
Use WPS AI to Create and Explain Excel Formulas for Free
WPS Office features powerful built-in AI tools that can instantly generate complex formulas, explain existing ones, and analyze your spreadsheet data—all without needing an expensive Copilot subscription.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet file.
- 2. Access WPS AI: Click on the WPS AI tab located on the top ribbon menu.
- 3. Use AI Formula: Select the AI Formula option from the toolbar.
- 4. Type your request: Describe the calculation you need (e.g., "Sum the sales if the region is East") or ask the AI to explain a selected cell.
- 5. Apply the result: Click Insert to apply the AI-generated formula directly to your worksheet.
Frequently Asked Questions
Why is the Copilot button grayed out or missing in Excel?
The Copilot button may be unavailable if your file is not saved to OneDrive or SharePoint, if AutoSave is turned off, or if you do not have an active Copilot for Microsoft 365 license assigned to your account.
Can Copilot in Excel fix broken formulas?
Yes, you can ask Copilot to troubleshoot errors like #N/A or #VALUE!. Select the cell with the error, open the Copilot pane, and ask 'Why is this formula returning an error and how can I fix it?'
Does Copilot work if my data isn't formatted as a table?
Currently, Copilot in Excel requires your data to be formatted as an Excel Table (using Insert > Table or Ctrl+T) to fully analyze and manipulate the data effectively.
Which formulas can Copilot generate?
Copilot can generate a wide range of formulas, from basic arithmetic (SUM, AVERAGE) to complex lookups and logical tests (XLOOKUP, INDEX/MATCH, nested IFs, and FILTER).
