Home/Blog/AI Tools at Work
AI Tools at Work · 10 min read

How to use Copilot and Python in Excel for data analysis

The short answer

Write the question first, keep data in named Excel tables, clean recurring files with Power Query, and report numbers from formulas or PivotTables. Use Copilot to explore and draft, naming the table, task, method and output. Use Python in Excel for statistics and charts. Check every output against an independent total before it reaches a reader.

Want the tools, not just the method? The The Excel with Copilot and Python Analyst Kit kit turns this guide into ready-to-use templates, spreadsheets and printables, with an instant download.

Key takeaways
  • Write the question and definitions before you open the data
  • Profile a file before cleaning it: a plausible total can hide errors that cancel
  • Copilot works in edit, plan and chat modes; ask it to show its method
  • Python in Excel runs in the Microsoft Cloud and reads data through xl()
  • Compute rates from sums; averaging percentages can be well off
  • Know what data may go into which AI tool before you paste anything

What are the steps to analyse data with Copilot and Python in Excel?

The steps below put the tools in the order that keeps numbers right. The first three do most of the work and involve no AI at all. Skipping them is the main reason AI-assisted analysis goes wrong: the assistant is asked to make sense of a messy table, and nobody can tell whether its answer is right.

Each step is small. Writing the question takes ten minutes with the person who asked. Profiling a file means counting its problems before you fix them. Power Query records your cleaning so next month you press Refresh. After that, formulas and PivotTables carry the numbers you report, Copilot helps you explore and draft, and Python in Excel handles what formulas do badly.

  • 1. Write the question as one sentence with a number, the decision it feeds, and the definition of every measure.
  • 2. Put raw data in named Excel tables: one variable per column, one observation per row.
  • 3. Profile the file: duplicates, numbers stored as text, mixed dates, key variants, blanks, outliers.
  • 4. Clean with recorded Power Query steps; keep corrections in a documented list.
  • 5. Build summaries from PivotTables, GROUPBY or PIVOTBY, never typed totals.
  • 6. Use Copilot with the table, task, method and output named; ask for the formula.
  • 7. Use Python in Excel for statistics, reshaping and special charts.
  • 8. Check every output against an independent value; reconcile totals; get a second reviewer.
  • 9. Disclose AI use where it shaped the analysis, and log what you checked.

Which tool should you use for which part of the job?

Excel now has four engines, and each has a job it does best. Formulas and PivotTables are exact, transparent and recalculate when the data changes, so they should carry every number you report. Power Query is for cleaning files that arrive again and again. Copilot is fastest at exploring a table you do not know, explaining a formula, proposing a layout and drafting commentary. Python in Excel is for statistics, reshaping and charts that formulas handle badly.

The evidence on AI assistants supports dividing the work this way. A study of 5,179 customer-support agents found an average gain of about 14% in issues resolved per hour, mostly among less experienced agents, while the consultant and developer studies show that the same tools can lower accuracy or speed on tasks outside their strengths. The practical answer is to decide in advance which parts of a task an assistant may do, and to measure the result rather than trusting the feeling of speed.

JobBest engineWhy
Numbers you reportFormulas and PivotTablesExact, reproducible, reviewable
Cleaning a monthly filePower QueryRecorded steps re-run on refresh
Exploring an unfamiliar tableCopilot (chat mode)Fast, and easy to verify
Explaining or fixing a formulaCopilotPlain-English explanation you can test
Medians, reshaping, special chartsPython in Excelpandas and matplotlib in a cell
An audit trailFormulas, Power Query, codeA conversation cannot be re-run

Why does tidy data matter before you use AI tools?

Every analysis tool, from PivotTables to pandas, assumes tidy data: each variable in its own column, each observation in its own row, and each kind of thing in its own table, as Hadley Wickham set out in the Journal of Statistical Software. Practical rules from Broman and Woo add consistent formats, dates written as year-month-day, one item per cell, a data dictionary and no colour used as data.

Tidy data matters even more with AI assistants, because they read your column names and values to decide what to do. A table with months across the top, merged cells or subtotals between rows invites a wrong interpretation. A named Excel table with clear column names gives Copilot and Python something they can work with, and gives you something you can check.

How do you clean data so it stays clean every month?

Use Power Query. Microsoft describes it as a data preparation engine that records transformations as steps, written in its M language, and applies them every time the query runs without modifying the source. That makes cleaning repeatable and reviewable: next month's file goes through exactly the same steps, and a colleague can click through them.

Order matters. Read the file as text, standardise text keys (trim spaces, fix case), remove test rows, convert money and dates with a fixed culture, set types, remove duplicates, and apply documented corrections last. A correction is a decision, so keep it in its own small file with the reason and the approver, rather than typing over the raw value.

How do you write Copilot prompts that give checkable answers?

In November 2026 Microsoft describes three modes for Copilot in Excel: edit, which changes the workbook; plan, which proposes a plan for you to confirm; and chat, which answers without changing anything. Its documentation also says Copilot can make mistakes, that you should verify what it creates, and that you should avoid using it for decisions in sensitive areas such as finance, legal or medical topics.

A prompt that produces a checkable answer has five parts: the mode, the table, one task, the method, and the output. 'What is our margin?' gives you a number. 'In the Sales table, what was March gross margin? Calculate total margin divided by total net sales, not an average of row percentages, and show the formula' gives you a number and a method you can test in thirty seconds. If an answer does not show its method, do not use the number.

What is Python in Excel good for, and where does it run?

Python in Excel lets you type Python into a cell with =PY and read workbook data with xl(), for example xl("Sales[#All]", headers=True), which returns a pandas DataFrame. Cells calculate in row-major order, left to right and then down, so define variables before you use them. You can return a Python object or spill Excel values into the grid.

Microsoft says the code runs in hypervisor-isolated containers in the Microsoft Cloud, has no network access and no access to your computer, and reads data only through xl() or Power Query connections. Availability depends on your plan: in November 2026 it was available to Enterprise and Business users and in preview for Family, Personal and Education users, and not on iPad, iPhone or Android. Use it for medians and percentiles by group, reshaping, rules across several rows and charts formulas do badly. For a simple total, a formula is still clearer.

Measure where you are

Reading helps; measuring tells you what to work on. These AI-graded assessments on AssessAll pair with this topic:

How do you check an AI answer in Excel? A worked example

Here is a real trap, using sample data from a fictional 12-store retailer. Asked for March gross margin without a stated method, an assistant averages the 72 store-department margin percentages and returns 39.0%. The right method divides total margin by total sales. Because grills, the second-largest department, has the lowest margin and home decor, a small department, the highest, the two methods disagree.

The fix is the check loop: ask with the method, read the method, compare with an independent value such as a SUMIFS or a PivotTable, and only then keep and log the result. Research supports the habit. In a field experiment with 758 consultants, AI helped on tasks inside its capability, but on a task chosen to sit just outside it people using AI were 19% less likely to produce a correct solution. In a 2025 trial, experienced developers were 19% slower with AI tools while believing they were faster.

Measure (March, sample data)Value
Net sales$5,974,741
Gross margin (dollars)$2,243,672
True margin: total margin / total sales37.6%
Simple average of 72 line margins39.0%
Gap1.4 points, about $84,000 of margin

What data should never go into an AI tool?

It depends on the tool, the account and your organisation's policy, so start with data classes: public, internal, confidential, personal and sensitive. Regulators point the same way. Australia's privacy regulator recommends not entering personal, particularly sensitive, information into publicly available generative AI tools. Canada's privacy commissioner says personal information should go into prompts only when authorised, with de-identified data used where possible. New Zealand's commissioner expects human review before acting on outputs.

For organisational accounts, Microsoft says Copilot prompts and responses are covered by its enterprise data protection commitments and not used to train foundation models. That does not make every dataset suitable. Minimise columns, replace names with IDs, aggregate where you can, and take care with very small groups, which can identify people even without names. Ask your privacy lead; this is not legal advice.

How do you prove a report total is right?

Test, do not just look. Spreadsheet-error research by Raymond Panko concludes that large spreadsheets very likely contain at least one wrong bottom-line value and that authors are overconfident. In the Enron spreadsheet corpus, 24% of spreadsheets with formulas contained an Excel error value.

Three tests cover most reports: control totals (rows against the number expected; totals against an independent source such as the finance ledger), a bridge from the raw total to the clean total one documented step at a time, and three numbers recalculated by hand. Then a second person reviews. The UK government's AQuA Book requires the analyst and the assurer to be different people for the same reason.

A plausible total is not proof. In the same sample retailer's raw sales file, a plain SUM gives $74,416,074 against a clean total of $73,781,207, only 0.9% apart. That small gap hides three errors: 18 duplicate rows adding $1,401,498, one line keyed ten times too large adding $1,435,537, and 22 amounts stored as text, which SUM skips, removing $2,202,168. Errors that partly cancel are the hardest to see, which is why the bridge has to add up to the cent.

Common mistakes

  • Answering 'send me the numbers' with everything, instead of agreeing the question.
  • Fixing the raw file by hand, so nobody can say next month what changed.
  • Averaging a column of percentages instead of dividing sums.
  • Keeping an AI answer because the number looks right, without reading its method.
  • Sharing GROUPBY or LAMBDA formulas with colleagues whose Excel cannot calculate them.
  • Pasting personal data into a consumer AI tool on a personal account.
  • Reviewing your own work and calling it checked.
Bodhih Pro Kit

Practise the whole workflow on messy data, with answers

The Excel with Copilot and Python Analyst Kit includes a practice pack with messy datasets, a Power Query script, tested Python, Python in Excel cells and an answer workbook, plus the workbook that reconciles every number. Not affiliated with or endorsed by Microsoft.

Sources

  1. Journal of Statistical Software: Wickham, H. Tidy Data. JSS 59(10), 1-23
  2. PeerJ Preprints (published in The American Statistician 72(1), 2018): Broman, K. W. and Woo, K. H. Data Organization in Spreadsheets
  3. arXiv (University of Hawaii author): Panko, R. What We Don't Know About Spreadsheet Errors Today: The Facts, Why We Don't Believe Them, and What We Need to Do
  4. North Carolina State University (ICSE 2015 paper by Hermans and Murphy-Hill): NC State News: research on Enron's spreadsheets and the Fuse corpus
  5. Organization Science 37(2), 403-423 (first released as a working paper, 18 September 2023): Dell'Acqua, F., McFowland III, E., Mollick, E., Lifshitz, H., Kellogg, K. C., Rajendran, S., Krayer, L., Candelon, F. and Lakhani, K. R. Navigating the Jagged Technological Frontier: Field Experimental Evidence of the Effects of Artificial Intelligence on Knowledge Worker Productivity and Quality
  6. arXiv (METR): Becker, J., Rush, N., Barnes, E. and Rein, D. Measuring the Impact of Early-2025 AI on Experienced Open-Source Developer Productivity
  7. GOV.UK (Government Analysis Function, Government Operational Research Service, GAD, ONS): The AQuA Book: guidance on producing quality analysis for government
  8. Office of the Privacy Commissioner of Canada: Principles for responsible, trustworthy and privacy-protective generative AI technologies
  9. Office of the Australian Information Commissioner: Guidance on privacy and the use of commercially available AI products
  10. Microsoft Support: Python in Excel availability
  11. Microsoft Support: Data security and Python in Excel
  12. Microsoft Support: Frequently asked questions about Copilot in Excel
Common questions

Questions people ask next

Is Copilot in Excel free?

Not on every plan. In November 2026 Microsoft listed specific Personal, Family, Premium, commercial Copilot and Copilot Chat-eligible business plans. Check Microsoft's current page; plans change often.

Do I need to install Python to use Python in Excel?

No. Python in Excel runs in the Microsoft Cloud with a curated set of libraries, so you need a qualifying plan and an internet connection, not a local install.

Can Copilot in Excel make mistakes?

Yes. Microsoft's own FAQ says it can make mistakes or produce inaccurate results, that you should verify what it creates, and that you should avoid using it for decisions in sensitive areas such as finance. Ask for the method and compare with an independent value.

What happened to the COPILOT function in Excel?

Microsoft retired it: from 14 September 2026 the COPILOT worksheet function is no longer available, and existing results show #NAME? when recalculated.

Is GROUPBY available in Excel 2024?

Microsoft's GROUPBY page lists Excel for Microsoft 365. If your version lacks it, use a PivotTable or SUMIFS against a list of unique values.

When should I stop using Excel for a report?

When signals stack up: very large data, daily rebuilds, many editors, auditors relying on the numbers, many manual steps, or a need for version control and code review. Then consider a code pipeline with version control, as UK government guidance on reproducible analytical pipelines describes.

Should I use Copilot's edit mode or plan mode?

Use plan mode for changes you will reuse, such as a summary sheet rebuilt every month, so you can read and adjust the layout and formulas before anything changes. Use edit mode for small, well-defined changes you can undo, and chat mode when you only want an answer.

How can I measure my Excel and data skills?

Take an AssessAll assessment such as Spreadsheet and Data Literacy for an independent baseline, and turn the gaps into a plan with Jobulary's individual development plan.