Expert guide
Python in Excel for Business Users
What =PY() can do with your data, five worked examples, and where it stops
Call us at +1 (385) 386-3860
Short answer: Python in Excel lets you write Python in a cell with =PY(), pull worksheet data into a pandas DataFrame with xl("A1:F500", headers=True), and return tables or charts to the grid. Code runs in Microsoft's cloud with a fixed set of analytics libraries. Use it for forecasting, statistics, segmentation and better charts. Use VBA, Power Query or an Excel consultant for automation, file handling and integrations.
For years, business analysts who needed anything beyond SUMIFS and PivotTables had two choices: wrestle with array formulas, or export data to a separate Python or R environment that the rest of the team could not open. Python in Excel removes that split. The analysis lives in the workbook, next to the data, and anyone with the right Microsoft 365 plan can refresh it.
How Python in Excel Works
Type =PY( in a cell
The cell switches to Python mode. Press Ctrl+Enter to run the code.
Read data with xl()
xl("Sales[#All]", headers=True) or xl("A1:F500", headers=True) returns a pandas DataFrame.
Return a Python object or Excel values
By default a cell shows a Python object card. Switch the output to Excel Value to spill a table into the grid.
Cells run in order
Python cells calculate left to right, top to bottom across sheets, so a variable defined in one cell can be used in a later one.
Libraries are pre-installed
pandas, NumPy, matplotlib, seaborn, statsmodels and scikit-learn are available. You cannot pip install others.
Five Business Examples
Each example assumes an Excel Table named Sales with columns OrderDate, Region, Customer, Product and Amount.
1. Monthly revenue by region, ready for a report
df = xl("Sales[#All]", headers=True)
df["Month"] = df["OrderDate"].dt.to_period("M").astype(str)
df.pivot_table(index="Month", columns="Region",
values="Amount", aggfunc="sum").fillna(0)Set the output to Excel Value and this spills a clean month-by-region grid. A PivotTable can do the same; Python earns its place when you add the next steps.
2. Flag unusual orders automatically
df = xl("Sales[#All]", headers=True)
stats = df.groupby("Customer")["Amount"].agg(["mean", "std"]).reset_index()
df = df.merge(stats, on="Customer")
df["z"] = (df["Amount"] - df["mean"]) / df["std"]
df.loc[df["z"].abs() > 3, ["OrderDate", "Customer", "Amount", "z"]]This lists orders more than three standard deviations from that customer's normal order size: a quick way to spot keying errors, fraud, or a customer whose buying pattern just changed.
3. A simple sales forecast with a trend and seasonality
from statsmodels.tsa.holtwinters import ExponentialSmoothing
df = xl("Sales[#All]", headers=True)
monthly = df.set_index("OrderDate")["Amount"].resample("MS").sum()
model = ExponentialSmoothing(monthly, trend="add",
seasonal="add", seasonal_periods=12).fit()
model.forecast(6).round(0)You need at least two years of monthly history for seasonality to mean anything. Excel's built-in FORECAST.ETS does a similar job without Python; we compare them in sales forecasting in Excel.
4. Customer cohorts (retention by first-purchase month)
df = xl("Sales[#All]", headers=True)
df["OrderMonth"] = df["OrderDate"].dt.to_period("M")
first = df.groupby("Customer")["OrderMonth"].min().rename("Cohort")
df = df.join(first, on="Customer")
df["Age"] = (df["OrderMonth"] - df["Cohort"]).apply(lambda x: x.n)
df.pivot_table(index="Cohort", columns="Age",
values="Customer", aggfunc="nunique")Cohort tables are painful with formulas and trivial in pandas. They answer the question most small businesses never measure: of the customers who first bought in March, how many were still buying six months later?
5. Charts Excel cannot draw natively
import seaborn as sns
df = xl("Sales[#All]", headers=True)
sns.boxplot(data=df, x="Region", y="Amount")Box plots, heatmaps and distribution charts return as images in the cell. Right-click to place the chart over the grid for a report.
Limits You Should Know
Code runs in Microsoft's cloud, so it needs an internet connection and is subject to compute limits on some plans.
No access to local files, network drives, databases or web APIs from inside the Python code.
Only the pre-installed libraries are available.
Python in Excel does not automate the Excel application: it cannot open other workbooks, format sheets or send email.
Recalculation of many Python cells can be slow on large datasets; keep heavy work in as few cells as possible.
Python in Excel vs VBA vs Power Query
| Feature | Python in Excel | VBA | Power Query |
|---|---|---|---|
| Main job | Analysis and statistics | Automating Excel and Office | Importing and reshaping data |
| Runs where | Microsoft cloud | Your PC | Your PC |
| Reads other files and databases | No (only workbook data) | Yes | Yes |
| Advanced statistics and ML | Yes | Very limited | No |
| Learning curve for business users | Medium (Copilot helps) | Medium | Low to medium |
| Works on Mac | Via Excel for the web | Yes, with limits | Partial |
A typical well-built reporting workbook uses all three: Power Query brings the data in and cleans it, Python does the analysis that formulas cannot, and VBA or Office Scripts handles distribution.
Getting It Right in a Team
Python cells are code, and code needs ownership. Name the DataFrames clearly, keep one cell per step, add comments, and document which tables feed which outputs. If your analysis is becoming a monthly process that others depend on, consider having it built as a proper model with tests. Our Excel dashboard and BI reporting work often combines Python in Excel with Power Query, and our Excel consultants can review Python cells written by staff or Copilot.
FAQ
Python in Excel FAQ
Questions from finance and operations teams