Writing complex Excel formulas from scratch takes time and requires remembering the correct syntax for functions like XLOOKUP, SUMIFS, or nested IF statements. Copilot in Excel can generate these formulas automatically when you describe what you want in plain English. This feature works by interpreting your natural language request and translating it into the appropriate formula syntax. This article explains how to use Copilot to create Excel formulas from a description, what prerequisites are needed, and how to avoid common mistakes that produce incorrect results.
Key Takeaways: Using Copilot to Write Excel Formulas
- Copilot pane in Excel > Home tab > Copilot button: Opens the chat interface where you type your formula description.
- Describe the calculation in plain English: Copilot translates your description into the correct formula syntax.
- Review and insert the formula: Always verify the generated formula before inserting it into your sheet.
How Copilot Generates Excel Formulas From a Description
Copilot uses the Microsoft Graph and the Excel calculation engine to understand the structure of your data, including column headers, data types, and cell ranges. When you type a request such as “calculate the total sales for the East region,” Copilot identifies the relevant columns, determines the appropriate function such as SUMIF or SUMIFS, and writes the formula with the correct cell references. The feature requires a Microsoft 365 subscription that includes Copilot for Microsoft 365, and your workbook must be stored in OneDrive or SharePoint Online for Copilot to access the data context.
Prerequisites for Using Copilot in Excel
Before you can use Copilot to write formulas, confirm the following:
- You have a Microsoft 365 Copilot license assigned to your account.
- Your Excel version is Version 2402 or later for Windows, or Version 16.83 or later for Mac.
- The workbook is saved to OneDrive or SharePoint Online. Copilot cannot read local files.
- Your data is formatted as an Excel table. Use Ctrl+T to convert a range to a table.
Steps to Make Copilot Write an Excel Formula From a Description
Follow these steps to generate a formula using natural language. The exact prompts you use affect the accuracy of the result.
- Open the Copilot pane in Excel
Go to the Home tab on the ribbon. Click the Copilot button on the far right side. The Copilot pane opens on the right side of the Excel window. - Select the table or cell range
Click any cell inside the Excel table that contains your data. Copilot uses the table name and column headers to build the formula. If your data is not in a table, select the entire range and press Ctrl+T to create a table. - Type your formula description in the chat box
In the Copilot pane, type a description of the calculation you want. Be specific. For example, type: “Add a column that calculates the profit margin as a percentage by dividing profit by revenue.” Do not write vague statements like “calculate profit.” Include the column names exactly as they appear in the table header. - Review the suggested formula
Copilot displays the formula and a preview of the result. Check that the formula uses the correct column names and cell references. For example, Copilot might generate:=([@Profit]/[@Revenue])100. If the formula looks correct, click the Insert button below the suggestion. - Test the formula in the worksheet
After insertion, verify the result in a few rows manually. Copilot sometimes misinterprets column names if they contain abbreviations or special characters. If the result is wrong, click the Undo button or press Ctrl+Z, then refine your description.
Refining a Formula After Insertion
If the formula is close but not correct, you do not need to start over. Click the cell that contains the formula, then type a follow-up prompt in the Copilot pane. For example, if Copilot gave you a SUM formula but you need a SUMIF, type: “Change this formula to only sum values where the region is West.” Copilot updates the formula in place.
Common Mistakes and Limitations When Using Copilot for Formulas
Copilot is powerful but not infallible. Understanding where it fails helps you get better results.
Copilot Returns a Formula That References the Wrong Column
This happens when column headers contain spaces, punctuation, or ambiguous terms such as “ID” appearing in multiple columns. Rename your column headers to single words without spaces before using Copilot. For example, change “Sales Amount (USD)” to “SalesAmount”. After renaming, type your description again.
Copilot Uses an Incorrect Function for the Calculation
Copilot sometimes chooses a function that is logically similar but not exact. For example, it might use AVERAGE when you need AVERAGEIF. In this case, type the exact function name in your description. Write: “Use AVERAGEIF to calculate the average sales for the East region.” Copilot respects explicit function names.
Copilot Cannot Write Array Formulas or Lambda Functions
Copilot currently supports a subset of Excel functions. It cannot generate dynamic array formulas that spill results, nor can it create LAMBDA or LET functions. If you need these, write them manually or use Copilot to generate the building blocks and then combine them yourself.
The Copilot Button Is Grayed Out
This indicates that your workbook is not saved to OneDrive or SharePoint Online. Save the file to a cloud location by clicking File > Save As > OneDrive. If the button remains grayed out, check that your Microsoft 365 Copilot license is active in the Microsoft 365 admin center under Billing > Licenses.
| Item | Describing the Calculation | Using the Formula Builder Manually |
|---|---|---|
| Input method | Plain English sentence | Clicking function categories and filling arguments |
| Speed | Seconds per formula | Minutes per formula for complex nested functions |
| Accuracy for simple formulas | High with clear column names | High with proper argument selection |
| Accuracy for nested or conditional formulas | Moderate, requires refinement | High with expertise |
| Requires internet connection | Yes | No |
| Learning curve | Minimal | Requires knowledge of function syntax |
Copilot in Excel is a complement to manual formula writing, not a replacement. For straightforward calculations like totals, averages, or conditional sums, Copilot saves significant time. For complex array formulas or custom LAMBDA functions, manual entry remains the better choice. After you insert a formula, use the Evaluate Formula tool on the Formulas tab to step through the calculation if the result seems incorrect. This helps you understand how Copilot built the formula and where to adjust your description.