Lesson 15 · Advanced · 20 min
Analysing an Excel table with the built-in Python interpreter
Analyse an attached Excel table in Copilot Chat with the built-in Python interpreter: prepared file, closed question, stated method, cross-checked result.
- Goal
- You will be able to prepare an Excel workbook, attach it to Copilot Chat, ask a closed question that triggers the Python interpreter, and cross-check the result before using it.
- Skills
- Frame
Your first attempt, unaided
Attach to Copilot Chat a spreadsheet export you have not cleaned up, then ask it for a total. Then look in its answer for anything that would tell you how many rows it actually counted.
Copilot Chat includes a Python interpreter: attach an Excel workbook or a CSV file, ask a numerical question, and it writes then runs the code that calculates, groups and plots. The result is only reliable if the file is clean, the question closed, the method stated and one figure cross-checked by hand. This is not Copilot in Excel, reserved for the paid licence: you work on an attached copy.
1A clean file before any numeric question
The Microsoft Learn FAQ is explicit: Copilot Chat "uses the Python language to help users perform complex data analysis, such as coding, visualisation and mathematics". With an attached file, an Excel workbook, a CSV or a TSV according to the supported formats page, it runs an analysis or a visualisation. Not to be confused with Copilot in Excel, whose Python mode acts directly inside the sheet. That one belongs to the paid licence. Users without a licence have had no Copilot pane in Excel since April 2026.
The code runs in a sandbox documented by Microsoft: a fresh virtual machine, isolated from other sessions, with no inbound or outbound network traffic, destroyed at the end of the session. The attached file is stored in your work OneDrive, deletable at any time, and is not used to train the models. Two reservations. Microsoft confirms that upload limits exist without putting numbers on them. The values circulating, three files per twenty-four hours and 512 MB per file, come from community answers from 2025. They are not guaranteed in September 2026. And so-called standard access depends on service capacity: at peak times, the feature may be temporarily restricted, without this being an outage.
The lever is what you provide. Before the prompt, prepare the file:
Preparation Why A single sheet Copilot does not guess which one matters Row 1 = headers, one row per record The code reads the columns by their name No total row and no merged cell A total inside the data is counted twice Typed columns: dates in DD/MM/YYYY, numbers without text A "12k EUR" is not a number In the prompt, describe this structure, ask a closed question rather than "find insights", ask for the method before the result, the number of rows taken into account and the list of anomalies encountered. Then cross-check one figure in Excel, with a filter or a sum. That cross-check is what turns the answer into data.
Diagram "Analyse an Excel table with Copilot"Diagram generated by AI and reviewed 2Two thousand tickets, a checkable average delay
A support manager attaches a fictional export of two thousand tickets and wants to know whether resolution times are getting worse.
Weak prompt:
Find insights.Copilot returns six general observations, two of which rest on misread columns, with no method and no ticket count. Nothing can be checked, so nothing can be used.
Strong prompt:
Attached file sample_tickets.csv: one sheet, row 1 = headers (id, created_on, closed_on, category, priority, team), dates in DD/MM/YYYY format, one row per ticket, no total row. Question: did the average closure time increase between the first and the second quarter of 2026, overall and by category? Before calculating, describe your method in three lines: closure time calculation, exclusion of unclosed tickets, grouping by quarter. Then present a table by category: number of tickets, average time Q1, average time Q2, difference in days. State the number of rows taken into account and flag any anomaly encountered: missing dates, closure date earlier than creation. Separate facts from explanatory hypotheses.Copilot states its method, excludes 142 unclosed tickets, flags 9 closure dates earlier than creation, then delivers the table. The manager filters one category in Excel and finds the average to one decimal place.
What changes: the question is closed and measurable, so the answer is a table, not an opinion. The method stated before the result makes the calculation choices visible, and therefore their errors too. Anomalies are listed instead of being ignored, and the manual cross-check turns the result into a figure you can present.
3Clean a sales export before calculating
Build a fictional sales export of two hundred rows: headers on row 3, a "Total" row every fifty records, amounts written "1,250 EUR". Here is the starting prompt, to be improved:
"How much did we sell in March?"
First clean the file according to the table in the concept section. Then rewrite the prompt: structure described, closed question (total amount excluding tax and total quantity for March 2026), method requested before the result, number of rows taken into account, list of non-compliant dates. The composer below guides you; enable the "Stick to the attached files" and "Separate facts from assumptions" boosters. Finally, cross-check the amount with an Excel filter.
Self-assessment grid: (a) the file has been cleaned and its structure is described in the prompt; (b) the method and the number of rows come before the result; (c) one figure is cross-checked in Excel and matches.
4A correct total on the wrong column
A management controller attaches a clean sales export: one sheet, one row per order, no total row. She asks for the revenue for March 2026. Copilot states its method, takes 1,842 rows into account, and delivers 412,300 euros, a correct calculation, which she cross-checks in Excel to the euro. The figure goes to the steering committee. It is wrong: the column added up is titled "Amount", and it holds the amount including tax; the "Net" column, two cells further, holds the expected figure excluding tax. What should have been seen: the file was clean but its headers were ambiguous, and the question did not name the column. The cross-check confirmed the sum, not the choice of column. Correction: describe the columns in the prompt ("Amount = including tax, Net = excluding tax, Qty = quantity") or rename the headers before attaching; ask a question that names the column ("sum of the Net column for orders whose Date is in March 2026"); ask "before calculating, state which column you add up and why". Rule to remember: a correct total on the wrong column is still a wrong figure.
5Quiz
Three questions, instant feedback. Each option comes with an explanation.
6Proof of mastery
Paste your final prompt, with the description of the file structure, Copilot's answer including the stated method and the number of rows, then the figure you cross-checked in Excel and the result of that cross-check.
Criteria
What you wrote at the start of the lesson
Going further
For an analysis in several stages, extraction then calculation then synthesis, reuse the method of Breaking a task down into three chained prompts. The first lesson of the course, Copilot Chat: what is included in your Microsoft 365, recalls why the file must be attached, and the glossary defines grounding. The Copilot prompts for finance sheet reuses these moves on month-end closes and budget variances.
Frequently asked questions
Is this the same thing as Copilot in Excel?
No. Copilot in Excel, with its Python mode, is part of the paid Microsoft 365 Copilot licence; Copilot Chat users without a licence have had no Copilot pane in Excel since April 2026. What the lesson uses is the Python interpreter of Copilot Chat itself, which works on the file you attach in the conversation.
Do my data go out onto the internet?
The Python code runs in an isolated sandbox, with no inbound or outbound network access, destroyed at the end of the session. The attached file is stored in your work OneDrive, which you can empty, and it is not used to train the models. Your organisation's confidentiality rules still apply: send only what you could pass on to a contractor.
How many files can I attach, and how big?
Microsoft confirms that limits exist without putting numbers on them for Copilot Chat. Community answers from 2025 cite three files per twenty-four hours and 512 MB per file; these figures appear on no Microsoft page and may have changed. On standard access, the feature can also be temporarily restricted at peak times: this is not an error.