Copilot in Excel Issues

Fix Copilot for Excel Add Formula Columns Prompt Not Working

Aamir Naveed Akram Oct 1, 2026 868 views

Question details

The user needs to resolve an issue where Copilot for Excel refuses to add formula columns despite receiving a seemingly valid text prompt.

How to Fix Copilot for Excel Add Formula Columns Prompt Not Working

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using Copilot AI to automate the creation of calculated formula columns in an Excel spreadsheet.
Observed behavior: Copilot rejects the prompt, asking for more specific column and calculation details instead of generating the requested formula column.

Before you start

Ensure you have a stable internet connection, an active Microsoft 365 Copilot license, and that your workbook is saved to OneDrive or SharePoint with AutoSave enabled.

Solution 1Recommended

Refine Your Prompt and Format Data as a Table

Copilot requires structured data and highly specific instructions to accurately generate formula columns.

Microsoft Copilot relies on strictly defined data boundaries to understand calculations. It cannot process raw ranges effectively and requires explicit table formatting along with clear instructions detailing exactly which columns to use.

Format data as an Excel table

Select your entire data range, go to the 'Home' tab, click 'Format as Table', choose a style, and ensure the 'My table has headers' box is checked.

Specify exact table and column names

Update your Copilot prompt to explicitly state the table name and the exact header names of the columns you want to calculate (e.g., 'Multiply [Price] by [Quantity] in Table1').

Define the calculation and destination

Tell Copilot exactly what mathematical operation to perform and what to name the new column (e.g., 'Add a new column named Total Cost').

Refine Your Prompt and Format Data as a Table

Prompt Example: Instead of saying 'calculate totals', try: 'In Table1, add a new formula column named Profit by subtracting [Cost] from [Revenue].'

Solution 2

Check Service Status and Retry

Intermittent server-side issues can cause Copilot to temporarily fail or reject valid prompts.

Free Microsoft Office alternative

Try WPS Office for a Powerful and Reliable Spreadsheet Experience

If Microsoft Excel's Copilot is frequently experiencing issues or you want to avoid heavy subscription fees, consider switching to WPS Office. WPS Spreadsheet provides advanced data processing, built-in formula assistance, and seamless cross-platform functionality for free.

1. Download the software: Visit the official WPS website and download the free installation package for your operating system.
2. Install WPS Office: Run the installer, open the application, and select WPS Spreadsheet to start your data analysis.
3. Open existing Excel files: Simply drag and drop your current .xlsx files into WPS Spreadsheet to continue working without losing any formatting or formulas.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in robust formula editor for complex data calculations.Lightweight and fast, running smoothly on older or lower-spec devices.Free alternative to expensive Microsoft 365 subscriptions with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Copilot say my data isn't supported for formula columns?

Copilot for Excel currently requires your data to be formatted as an official Excel Table. Select your data and press Ctrl+T to convert it into a table before prompting Copilot again.

Can Copilot reference multiple tables to create a new formula column?

Currently, Copilot works best when analyzing a single formatted table. If you need a formula that spans multiple tables, you may need to use VLOOKUP or XLOOKUP manually, or merge the data into a single table first.

Does AutoSave need to be on for Copilot to work in Excel?

Yes, Copilot requires your Excel file to be saved to OneDrive or SharePoint with AutoSave enabled. It cannot process locally saved files that are not synced to the cloud.

What if my prompt is perfectly formatted but Copilot still rejects it?

Sometimes Copilot experiences intermittent server-side issues. If you are certain your prompt is correct and your data is a Table, try restarting Excel or waiting a short period before resubmitting your prompt.

Microsoft Copilot Issues