Copilot in Excel: Advanced Analysis with Python

Advanced Analysis is the mode in Copilot in Excel where, instead of writing formulas or building a PivotTable, Copilot writes and runs Python on your data and hands back the results — a correlation matrix, a forecast, a cleaned dataset, a chart — as cells and objects in the workbook. It's the feature most likely to make an analyst say "wait, it can do that?" and also the one most likely to be misunderstood by the people approving it. This guide covers both halves.

What it is, precisely

Advanced Analysis is Copilot driving Python in Excel. Python in Excel is the =PY() cell function that runs a curated Anaconda distribution — pandas, NumPy, Matplotlib, seaborn, statsmodels, scikit-learn — in the Microsoft cloud, not on the user's machine. Copilot's contribution is translation: you describe the analysis in English, Copilot writes the Python, inserts it as =PY() cells, runs it, and explains what it found. You see the code. You can edit it. It is not magic and it is not hidden.

That distinction matters for three audiences:

• Analysts get a fast route into pandas without learning pandas — and, because the code is visible, a genuine on-ramp to learning it.
• Security teams need to know that the workbook data referenced by the Python cells is sent to the Python runtime in Microsoft's cloud. It stays inside the Microsoft 365 boundary and the usual compliance commitments apply, but it is not "on-device."
• Finance should know that heavy use can hit compute limits (more below).

What it's good at

Descriptive and diagnostic work. "Summarise this table by region and flag outliers," "show me the correlation between discount and margin," "what drives churn in this dataset?" — Copilot will pick a sensible approach (group-bys, correlation, sometimes a quick regression), run it, and narrate the result. For a business user who would otherwise have made a PivotTable and squinted, this is a real upgrade.

Data cleaning. Splitting inconsistent date formats, trimming whitespace, standardising categories, filling gaps — Python does this better than formulas, and Copilot writes the boring code for you.

Charts beyond Excel's native set. Heatmaps, pair plots, distribution plots. Python charts land as images in the grid, which is fine for a report and annoying if you want to tweak them like a native Excel chart.

Simple forecasting. Linear trend, seasonal decomposition, a basic ARIMA. Usable for "roughly where is this heading," not for anything you'd sign off in a board pack without an actual analyst checking it.

Where it falls down

It's confident, not correct. Copilot picks a method and reports a result with the same tone whether the method fit the data or not. A correlation on a tiny sample, a regression with an obviously collinear predictor, a forecast on data with a structural break — you'll get a clean number and a tidy sentence. Someone who knows what a p-value is should look at the code before the number goes anywhere important.

Data shape matters enormously. Advanced Analysis wants a proper table: one header row, one record per row, no merged cells, no subtotal rows, no "notes" column that's really six columns. Most real workbooks aren't that. Spend five minutes converting the range to a table (Ctrl+T) and stripping the cruft before asking; the difference in output quality is dramatic.

Iteration is clunky. Each follow-up regenerates or extends Python cells. It works, but it isn't a notebook experience, and a long chain of "now also…" prompts leaves a workbook full of =PY() cells nobody can explain later. For anything beyond an exploratory session, move the code into a deliberate structure or into a real notebook.

It doesn't know your business. It doesn't know that column F is net of returns or that Q4 2024 was the ERP migration. Every insight it offers is only as good as the column names and the context you type.

Compute, licensing, and the "Python in Excel add-on"

Python in Excel ships with a standard compute allowance for Microsoft 365 subscribers. When that's exhausted, execution slows down (Microsoft's term is reduced compute), and the Python in Excel add-on licence buys premium compute — faster execution and higher limits. Copilot's Advanced Analysis runs through the same pipe, so a team that leans on it hard will feel the throttle. It is not a per-query charge; it's a "you've had your share this month" governor. If you're piloting this with a data-heavy team, budget for the add-on rather than being surprised by a slowdown.

You also need a Microsoft 365 Copilot licence for the Copilot half — Python in Excel alone is available more broadly, but the "describe it in English" layer is Copilot. Availability is Windows desktop and Excel for the web first; check the current platform matrix before promising it to Mac-heavy teams.

Admin controls worth knowing

• Python in Excel can be disabled tenant-wide through the Cloud Policy service (Office cloud policy). If your data residency or regulatory posture doesn't allow workbook data to hit the Python runtime, that's the switch — and it disables Advanced Analysis with it.
• The Python environment is locked: no arbitrary pip install, no network calls out from the runtime. That's a feature. Users occasionally ask why they can't fetch an API from inside a cell; the answer is "by design."
• Copilot's data access follows the user's own permissions, as everywhere else in Microsoft 365 Copilot. There's nothing new to govern on the retrieval side.

A sensible way to introduce it

Pilot with people who already do analysis in Excel and know what a bad result looks like. Have them prompt Advanced Analysis on real datasets and read the code it produces. Two things happen: they catch the errors, and they learn pandas by osmosis. Then write two lines of guidance for the wider org: tables in, results checked by a human before they leave the workbook. That's the whole policy.

Advanced Analysis is a genuinely useful bridge between spreadsheet thinking and data-science tooling. Treat it as a very fast junior analyst who never says "I'm not sure," and it earns its place.