Python in Excel: Complete Guide (2026)
Microsoft built native Python into Excel 365 — write pandas, matplotlib, and scikit-learn directly in cells. Here is what it can do, how to use it, and when formulas or VBA are still the better choice.
How to Start in 30 Seconds
- Open any Excel 365 workbook saved to OneDrive or SharePoint
- Click an empty cell and type
=PY( - Write your Python code (e.g.
xl("A1:C10", headers=True).describe()) - Press Ctrl + Enter
How Python in Excel Works
Python does not run on your computer — it runs in Microsoft's Azure cloud (an Anaconda environment). When you press Ctrl+Enter on a =PY() cell, Excel sends your code and the referenced data to Azure, Python executes it, and the result comes back to your sheet.
This means:
- You need an internet connection. Python cells do not calculate offline.
- Your data leaves your machine. For sensitive data, check your organisation's Microsoft data residency settings before using Python in Excel.
- First runs are slower. The environment takes a few seconds to spin up; subsequent calculations in the same session are faster.
- Results are static until you recalculate. Python cells do not auto-recalculate like formulas — press Ctrl+Alt+F9 to force a refresh.
Reading Excel Data into Python
The xl() function is the bridge between your sheet and Python. It reads a range and returns a pandas DataFrame:
Reference Excel data in Python
# xl() reads a range from your sheet into a pandas DataFrame
df = xl("A1:D100", headers=True)
df.head()headers=True uses the first row as column names.
Descriptive statistics
df = xl("A1:D100", headers=True)
df.describe()Returns count, mean, std, min, quartiles, and max for every numeric column.
Filter rows
df = xl("A1:D100", headers=True)
df[df["Region"] == "North"]Same as a FILTER formula, but more readable for complex conditions.
Create a matplotlib chart
import matplotlib.pyplot as plt
df = xl("A1:B13", headers=True)
fig, ax = plt.subplots()
ax.bar(df["Month"], df["Sales"])
ax.set_title("Monthly Sales")
figReturn the fig object — Excel renders it as an image in the cell.
Linear regression with scikit-learn
from sklearn.linear_model import LinearRegression
import numpy as np
df = xl("A1:C50", headers=True)
X = df[["Units", "Price"]].values
y = df["Revenue"].values
model = LinearRegression().fit(X, y)
f"R² = {model.score(X, y):.3f}"Machine learning directly in a cell — no Jupyter notebook needed.
Python vs Formulas vs VBA: When to Use Each
| Task | Use | Why |
|---|---|---|
| Statistical analysis (regression, ANOVA, correlation) | Python | statsmodels and scikit-learn handle this natively; Excel formulas get unwieldy |
| Custom charts (violin plots, heatmaps, faceted grids) | Python | matplotlib/seaborn produce charts Excel cannot |
| Machine learning (classification, clustering, prediction) | Python | scikit-learn — impossible in VBA or formulas |
| Simple lookups and conditional sums | Formulas | XLOOKUP and SUMIFS are faster and recalculate automatically |
| Looping through sheets, formatting cells, event triggers | VBA | Python in Excel has no access to the Excel object model |
| Data cleaning (TRIM, SUBSTITUTE, text extraction) | Formulas or Power Query | Faster and recalculate without cloud round-trip |
| Text analysis, sentiment, NLP on cell content | Python | NLTK and similar libraries are available |
| Generating PDF reports from Excel data | VBA | Python in Excel cannot access the file system |
Available Libraries
Python in Excel uses the Anaconda distribution. You cannot install additional packages — only pre-installed libraries are available:
- pandas — data manipulation, groupby, merge, reshape
- NumPy — numerical arrays, linear algebra, random sampling
- matplotlib + seaborn — charts, heatmaps, distribution plots
- scikit-learn — machine learning: regression, classification, clustering
- statsmodels — statistical tests, time series, econometrics
- SciPy — optimisation, signal processing, statistics
- NLTK + spaCy — natural language processing
Limitations to Know Before You Start
- Python cells do not auto-recalculate — you must trigger a refresh manually
- No access to the Excel object model (cannot format cells, loop through sheets, or trigger on events)
- Cannot read or write files — no file system access from cloud Python
- Cannot call external APIs or URLs from Python code
- Data is sent to Microsoft's cloud — check data residency requirements for sensitive data
- Only works on files saved to OneDrive or SharePoint (not local-only files)
Using Python to analyse a workbook with formula errors?
Python in Excel is great for analysis — but it cannot audit formulas. Run the free ExcelErrorFinder audit first to clean up any formula errors before Python analysis.
Frequently Asked Questions
How do I use Python in Excel?
Type =PY( in any cell in Excel 365, write your Python code, then press Ctrl+Enter. Python runs in Microsoft's cloud and the result appears in the cell. Reference Excel ranges with xl('A1:C10', headers=True).
Is Python in Excel free?
Available on Microsoft 365 Business and Enterprise plans at no extra charge. Not available on personal M365 plans (Home/Family) or standalone Excel 2021/2019.
What Python libraries are available in Excel?
The Anaconda distribution: pandas, NumPy, matplotlib, seaborn, scikit-learn, statsmodels, SciPy, NLTK. You cannot install additional libraries.
Is Python in Excel replacing VBA?
Complementing, not replacing. VBA handles workbook automation and event triggers better. Python is better for data science tasks — statistics, machine learning, complex visualisations.
Related Guides
Excel and AI: Complete Guide
Copilot, ChatGPT, Formula Bot, and Python in Excel — the full landscape.
Read →
Microsoft Copilot in Excel
The built-in AI assistant — how it compares to Python in Excel.
Read →
ChatGPT for Excel Formulas
Use AI to write and fix Excel formulas without a Copilot subscription.
Read →
Excel Formulas Hub
When formulas are faster than Python — the essential functions.
Read →