When you try to use Copilot in Excel to analyze a table, you may see an error message stating that Copilot cannot analyze the table because the data is not formatted. This error occurs when Copilot cannot recognize your data as a structured table with clear column headers and consistent data types. The root cause is that your data is in a plain range rather than a proper Excel Table object. This article explains why this happens and provides two methods to format your data so Copilot can analyze it.
Key Takeaways: Fixing Copilot Table Analysis in Excel
- Ctrl+T keyboard shortcut: Converts a selected range into an Excel Table with default formatting and headers.
- Insert > Table menu: Alternative method to create a table from the ribbon when you prefer using the mouse.
- Ctrl+Z after formatting: Use Undo immediately if the table conversion changes your data layout or merges cells incorrectly.
Why Copilot Requires a Proper Excel Table
Copilot in Excel relies on the Excel Table object to understand the structure of your data. A proper Excel Table has defined column headers, consistent data types in each column, and a clear boundary between the header row and the data rows. When your data is in a plain range, Copilot cannot identify where headers end and data begins, nor can it determine the data type for each column. This leads to the error message: “Copilot cannot analyze this table because the data is not formatted.”
The technical requirement is that the data must be in an Excel Table, not just visually arranged with bold headers and borders. Excel Tables are created using the Insert > Table command or the Ctrl+T shortcut. Once the data is in a table, Copilot can read the column names, detect data types such as text, number, or date, and apply filters or aggregations correctly.
What Excel Tables Provide That Ranges Do Not
An Excel Table offers structured references, automatic expansion when new rows are added, and built-in filter buttons on each header. Copilot uses these structured references to generate formulas and summaries. Without a table, Copilot has no reliable way to reference columns by name or to know the total row count. Converting your data to a table resolves the error in almost all cases.
Steps to Format Your Data as an Excel Table
Follow one of these two methods to convert your data range into a proper Excel Table. After conversion, Copilot will be able to analyze the table.
Method 1: Use the Ctrl+T Keyboard Shortcut
- Select the entire data range
Click and drag to highlight all cells that contain your data, including the header row. Make sure no empty rows or columns are inside the selection. - Press Ctrl+T
The Create Table dialog box appears. Verify that the range shown matches your selection. - Check the “My table has headers” box
This box must be checked so that the first row of your selection becomes the table headers. If your data does not have headers, leave this box unchecked and add headers manually after creation. - Click OK
Excel converts the range into a table with alternating row shading and filter buttons on each header. The table is now ready for Copilot.
Method 2: Use the Ribbon Menu
- Select the data range
Highlight the entire dataset including the header row. - Go to the Insert tab
On the Excel ribbon, click the Insert tab. - Click Table in the Tables group
The Create Table dialog opens. Confirm the range and check the “My table has headers” option. - Click OK
The range becomes a formatted table. You can now use Copilot on this table.
If Copilot Still Has Issues After Formatting
Even after converting to a table, Copilot may still fail to analyze the data if the table has structural problems. Below are the most common residual issues and their fixes.
Copilot Returns “Cannot Analyze Table” After Conversion
This error can persist if the table contains merged cells in the header row or data area. Excel Tables do not support merged cells. Remove all merged cells by selecting the merged area, going to Home > Alignment > Merge & Center, and clicking it to unmerge. After unmerging, ensure each column has a unique header name. Duplicate header names also block Copilot analysis.
Copilot Ignores Some Columns
If Copilot seems to skip certain columns, check that those columns contain consistent data types. For example, a column mixing text and numbers may confuse Copilot. Convert the entire column to one data type by selecting the column, going to Home > Number, and choosing the correct format. Also ensure no blank cells exist in the header row.
Copilot Shows Wrong Summaries or Totals
When Copilot generates summaries, it uses the data type of each column. If a column is formatted as text but contains numbers, Copilot may sum incorrectly. Change the column format to Number by selecting the column and clicking Home > Number > Number. Then refresh the Copilot pane by closing and reopening it.
Excel Table vs Plain Range for Copilot: Key Differences
| Item | Excel Table | Plain Range |
|---|---|---|
| Copilot support | Fully supported for analysis | Not supported |
| Column headers | Required and automatically recognized | Not recognized by Copilot |
| Data type detection | Automatic per column | Not detected |
| Automatic expansion | Yes, new rows extend the table | No |
| Filter buttons | Added automatically | Must be added manually |
| Merged cells allowed | No | Yes, but not recommended |
After converting your data to an Excel Table using Ctrl+T or the Insert > Table menu, Copilot can immediately analyze the table. If errors persist, check for merged cells, duplicate headers, or inconsistent data types in any column. Use the Undo shortcut Ctrl+Z if the table conversion changes your data layout in an unexpected way. Once the table is clean, Copilot will generate summaries, formulas, and insights based on your structured data.