How to Prepare Excel Data for Copilot and ChatGPT
Copilot gives a confident, wrong answer and you cannot see why. Nine times in ten it is not the question — it is the sheet. Microsoft says unstructured data is unsupported for direct analysis, and the specific culprits are always the same handful.
Last updated: October 2026
| Human-formatted sheet | What an AI needs | |
|---|---|---|
| Merged region label | → | Value repeated on every row |
| Header split over 2 rows | → | One header row |
| Blank spacer + Total rows | → | Data rows only |
| Amount as text "1,234.50" | → | 1234.5 |
Method 1 — fix it by hand
- Unmerge every merged cell, then fill the value down into the freed rows.
- Collapse a stacked header into one row and make sure no two columns share a name.
- Delete blank spacer rows, repeated header rows, and any totals row sitting inside the data.
- Convert numbers stored as text — but leave part numbers like
A08and IDs like00123alone, or you destroy them.
Method 2 — convert to a Table first
Ctrl+T gives the range named headers, which genuinely helps an assistant address columns. It does nothing about merged cells, stacked headers or text numbers — so it is a useful last step, not a fix.
The 1-click way — Tellsheet
Make Table AI-Ready does the whole checklist and writes the result to a new sheet, so your formatted original survives. It fills merged blocks, flattens a stacked header, drops blank, repeated-header and totals rows, trims hidden characters, and converts text numbers — then tells you exactly what it changed. It will not guess at ambiguous dates: 03/04/2026 is reported and left alone, because picking day-first or month-first wrongly corrupts a quarter of the calendar silently. Run Fix Dates after, which asks.
Frequently asked questions
Why does Copilot get my spreadsheet wrong?
Usually the structure, not the question. Merged cells leave most rows blank, a two-row header gives columns no single name, and numbers stored as text are not numbers. Microsoft states unstructured data is not supported for direct-answer analysis.
Does Copilot clean data for me?
No. It works best on data that is already a clean, flat table with proper headers — which is the thing people most often need help producing.
Will it convert my product codes to numbers?
No. Only unambiguous numbers are converted. Codes like A08 and identifiers with leading zeros such as 00123 stay as text, because converting them loses information permanently.
Does it change my original sheet?
No — the flat table is written to a new sheet. Your formatted layout is usually load-bearing for human readers, so it is kept.
Related Excel guides
Make any sheet AI-ready in one click
Flatten merged cells, stacked headers and text numbers — inside Excel, nothing uploaded.
Get Tellsheet free See pricing