2.1Converting a range into an Excel Table
The essentials in 30 seconds
- No Table, no Copilot: a raw range leaves it blind or greyed out — Ctrl + L first, AI second.
- A clean Table = three rules: unique headers, zero blank rows, one value per cell — mess cannot be analysed.
- Name it to talk to it: “Ventes2025” rather than “Table1” — “analyse Ventes2025” becomes natural.
Running mini-case — Hicham and the silent export
Hicham, an administrative assistant in Algiers, opens his sales export in Excel and launches Copilot: “no data to analyse”. His file has a floating title in row 1, two levels of merged headers and blank rows. He cleans it up (title removed, one header row, one value per cell), presses Ctrl + L, names the Table “Ventes2025”, then relaunches the same request: Copilot answers. This same “Ventes2025” file returns in 2.2 (first insights), 2.3 (calculated column), 2.4 (message chart) and 2.5 (first Python analysis).
Level 2 running thread: Hicham's “Ventes2025” file returns in the lesson, the diagram, the script and the 48h challenge — and continues in 2.2, 2.3, 2.4 and 2.5.
1. The non-negotiable prerequisite
Here is the condition that explains 90% of Copilot failures in Excel: it requires a formal Table, not a simple range of cells. A range is a rectangle of data; a Table (Ctrl + L / Insert > Table) is a named object with typed columns that Copilot can query. Without a Table, Copilot answers “I cannot find any data to analyse” or stays greyed out. Hicham's first troubleshooting reflex: “have you converted it to a Table?” — that resolves most blockages.
2. The conditions for a good Table
An exploitable Table follows three rules: clear headers (one row of unique, meaningful titles), no blank rows (they split the Table in two), one value per cell (never “Dupont / Paris” in a single cell). These rules are not specific to Copilot: they are the rules of clean data as they have always been. Copilot only makes the cost of mess visible — a poorly structured range cannot be analysed, with or without AI. Hicham checks these three points before every conversion.
3. Name it to talk to it
Give the Table a meaningful name (Table Design tab > Name): “Ventes2025” rather than “Table1”. You can then tell Copilot “analyse Ventes2025” — the dialogue becomes natural. Immediate bonus, outside Copilot: automatic filters, formatting, formulas that extend on their own, dynamic ranges. The Table is the Excel best practice that Copilot finally makes mandatory — Hicham names it as soon as he converts.
🔎 Concrete example — 🔎 Concrete example — Copilot stays greyed out: the reflex that unblocks
Hicham's export does not answer, like so many files left as a free range:
1. Click inside your data
2. Ctrl+L (or Insert > Table), tick “My table has headers”
3. Design tab > Table Name: “Ventes2025”
4. Relaunch: “Analyse Ventes2025 and give the trends”Reading: the conversion creates the named object with typed columns that Copilot can query.
Lesson: then check for the absence of blank rows and the single header level.
Section vocabulary
- Formal Table
- Excel object created with Ctrl + L (Insert > Table): typed columns, proper name, automatic extension — the only format Copilot understands.
Simple example: Hicham converts his export into the “Ventes2025” Table.
What it is not: a simple range of cells, however well filled.
Reflex: Greyed-out Copilot = convert to a Table.
- Range vs Table
- A range is a raw rectangle of data; a Table is a queryable object (filters, expandable formulas) that Copilot can analyse.
Simple example: Hicham's range becomes queryable after Ctrl + L.
What it is not: two equivalent presentations for the AI.
Reflex: to analyse = Table, not range.
- Clean data
- Unique headers on one row, no blank rows, a single value per cell: the age-old rules, made mandatory by Copilot.
Simple example: Hicham splits “Dupont / Paris” into two columns.
What it is not: merges and subtotals embedded in the middle.
Reflex: before Ctrl + L: clean up.
- Table name
- Meaningful name given in the Table Design tab (“Ventes2025”): it makes requests to Copilot precise and natural.
Simple example: Hicham asks “analyse Ventes2025” instead of “analyse Table1”.
What it is not: the automatic “Table1” name kept by default.
Reflex: convert = name immediately.
In practice — In practice — Convert a real export into a named Table
- Take a raw export (sales, stock, contacts) laid out as a range of cells, like Hicham's.
- Check cleanliness: one row of unique headers, no blank rows, one value per cell.
- Convert with Ctrl + L, then name the Table in the Design tab (“Ventes2025”).
- Open Copilot and launch “Analyse Ventes2025”: see that it now answers.
Ready-to-say script — unblock a colleague whose Copilot is “broken” (30 s)
“For Hicham's export, Copilot said it saw nothing: the file was a free range, with a floating title and merges. We cleaned it up — one header row, one value per cell — then Ctrl + L and the name “Ventes2025”. Same request relaunched: Copilot answers. When it looks broken in Excel, first reflex: have you converted it to a Table?”
Say while showing the range before (silent Copilot) then the Table after (Copilot answering).
Copy-paste template — conversion checklist
TABLE CONVERSION — Hicham checklist
1. Remove floating title and blank rows
2. One row of unique headers (e.g.: Mois;Region;Commercial;Montant)
3. One value per cell (never “Dupont / Paris”)
4. Click inside the data > Ctrl+L > tick “My table has headers”
5. Table Design > Name: Ventes2025
6. Copilot prompt: “Analyse Ventes2025 and give the trends”Raw accounting export
Hicham's export arrives with a title in row 1: remove it, check the headers, then Ctrl + L.
File from a predecessor
Two levels of merged headers: flatten them into a single row before converting.
Shared monthly tracking
Name it “Ventes2025” so the whole team in Algiers can say “analyse Ventes2025” without ambiguity.
Old .xls or CSV
First save as .xlsx, then convert the range to a Table: flat formats limit Copilot.
✓ Best practices
- Adopt the “have you converted it to a Table?” reflex as soon as Copilot stays greyed out or finds nothing.
- Always name your Tables: “analyse Ventes2025” is much clearer than “analyse Table1”.
- Enjoy the bonus outside Copilot: filters, formatting and expandable formulas come free with the Table.
✗ Common pitfalls
- Leaving blank rows in the middle: they split the Table and skew the analysis.
- Putting two values in one cell (“Dupont / Paris”): Copilot cannot tell them apart.
- Stacking several levels of merged headers: a Table wants a single clear header row.
Key takeaways
- Copilot in Excel requires a formal TABLE (Ctrl + L), not a simple range: that is 90% of blockages.
- A good Table: clear headers, no blank rows, one value per cell.
- Name the Table (“Ventes2025”) to dialogue naturally with Copilot.
- Converting to a Table brings an immediate bonus even outside Copilot: filters, expandable formulas, formatting.
48h challenge — apply tomorrow
Take a real export (Hicham's or yours): clean it up (unique headers, zero merges, zero blank rows), convert it into a named Table, then launch “Analyse [Name]” in Copilot and compare before/after.
- I can explain why Copilot requires a formal Table.
- I know how to convert a range into a named Table (Ctrl + L + meaningful name).
- I have checked cleanliness (headers, blank rows, one value per cell).
Frequently asked questions
How do I quickly convert a range into a Table?
Click inside the data and press Ctrl + L (or Insert > Table). Check that “My table has headers” is ticked, confirm — it is instant.
Does Copilot work on an old .xls file or a CSV?
First convert to .xlsx format and turn the range into a Table. Old or flat formats strongly limit Copilot.
Why does Copilot often stay “greyed out” in Excel?