Using Python in Excel

Most of this site is about Python working on Excel files from the outside: scripts that read, clean, format and deliver workbooks. Python in Excel is the other direction. It lets you type Python into a cell with a =PY formula, runs it in a Microsoft cloud container with pandas, NumPy, matplotlib, seaborn and statsmodels available, and puts the result back into the grid. For analysts who already live in Excel it removes the whole question of environments and files; for developers it is a new place where Python code ends up, with its own rules. This section of Getting Started with Python Excel Automation covers how those rules work in practice — and how to tell when a job belongs in a script instead.

What this section covers

• Reference Cells, Ranges and Tables with xl() in Python in Excel — the only way data gets into a formula.
• Return DataFrames and Values from Python in Excel Cells — output types, spilling and structuring a sheet.
• Plot Charts with matplotlib in Python in Excel — images in cells and over the grid.
• Fix Python in Excel Errors — every error value and its fix.
• Python in Excel vs openpyxl and xlwings — choosing between in-cell Python and scripts.

Code on these pages is formula code: it runs inside a =PY cell, where xl, pd, np, plt and sns already exist. It does not run as a standalone script unless you provide those names yourself.

Entering your first formula

Select a cell and choose Formulas → Insert Python, or type =PY( and choose Python from AutoComplete. The cell enters Python editing mode, where Enter starts a new line and Ctrl+Enter commits the formula. While editing, selecting cells with the mouse inserts the matching xl() reference for you.

sales = xl("Sales[#All]", headers=True)
sales.describe()

That two-line formula already shows the model. The first line pulls an Excel table named Sales into a pandas DataFrame, using its header row as column names. The second line is the formula's result: a formula has no return, and the value of the last expression is what reaches the cell. Everything a pandas user knows applies from here on.

What does not carry over is access to the outside world. The runtime cannot read files from your disk, cannot make network requests and cannot install packages. The workbook is the data source, and anything external has to be brought into it — usually through Power Query — before Python can see it.

Getting data in with xl()

xl() accepts the same reference text as an Excel formula, and what it returns depends on what you point it at: a single cell gives a Python scalar, and anything larger gives a DataFrame. For blocks of records, always pass headers=True, and prefer an Excel table with the [#All] specifier over a fixed range, because tables grow when rows are added.

sales = xl("Sales[#All]", headers=True)         # a table that grows with the data
tax_rate = xl("TaxRate")                        # a named single cell
raw = xl("'Raw data'!A1:C200", headers=True)    # a fixed range on another sheet
orders = xl("Orders")                           # a Power Query query, by name

len(sales), tax_rate, raw.shape, orders.shape

Power Query is the supported bridge for external data: connect to a file, database or web source with Data → Get Data, load the query (a connection-only load is enough), and reference it by name. Refreshing the query recalculates every Python formula that uses it. Other workbooks cannot be referenced directly. The details, including the types you receive and how to check them, are in Reference Cells, Ranges and Tables with xl() in Python in Excel.

The initialization code behind every formula

Before any of your formulas run, the runtime executes the workbook's initialization code, which you can inspect under Formulas → Initialization. It contains two kinds of statement. The required ones — import excel, the warnings settings and the excel.set_xl_scalar_conversion and excel.set_xl_array_conversion calls — control how xl() converts cells into Python values, and removing them can break formulas. The library imports — NumPy as np, pandas as pd, matplotlib's pyplot as plt, statsmodels as sm and seaborn as sns — are there for convenience, and you can add imports for other available libraries or remove ones you do not use.

Treat changes to initialization as changes to every formula in the workbook. Adding an import is harmless; changing how conversions behave alters what every existing xl() call returns. If a formula needs a less common library, importing it inside that formula is the more contained option:

from scipy import stats

sales = xl("Sales[#All]", headers=True)
north = sales.loc[sales["Region"] == "North", "Amount"]
south = sales.loc[sales["Region"] == "South", "Amount"]
result = stats.ttest_ind(north, south, equal_var=False)
round(float(result.pvalue), 4)

Cleaning data inside a formula

Data typed by people arrives in formulas with the same problems it has everywhere: amounts stored as text, stray spaces in labels, blank rows at the bottom of a range. The difference in Python in Excel is that the cleaning runs on every recalculation, so it should be quick, deterministic and written once in a load cell that every output reuses.

raw = xl("Sales[#All]", headers=True)
clean = (
    raw.dropna(how="all")
       .assign(
           Region=lambda d: d["Region"].astype(str).str.strip().str.title(),
           Amount=lambda d: pd.to_numeric(d["Amount"], errors="coerce"),
       )
       .dropna(subset=["Amount"])
)
problems = len(raw) - len(clean)
clean

Keep a count such as problems and surface it in its own small output cell, so a reader notices when rows start disappearing. The techniques themselves are ordinary pandas, and Cleaning Excel Data With Pandas covers them in depth — though a workbook that needs heavy cleaning on every calculation is often better served by fixing the source in Power Query, which runs once per refresh rather than once per recalculation.

Getting results out: objects and values

Each Python formula has an output type, switched from the Python output menu by the formula bar or with Ctrl+Alt+Shift+M. As a Python object, the result stays a live Python value in one cell, shown as a card; later Python cells can use it through its variable. As an Excel value, the result is converted: scalars become ordinary numbers, text and dates, and DataFrames spill into the cells below and to the right, where charts, conditional formats and normal formulas can use them.

sales = xl("Sales[#All]", headers=True)
(
    sales.groupby(["Region", "Rep"])["Amount"]
         .agg(Total="sum", Deals="count")
         .sort_values("Total", ascending=False)
         .reset_index()
         .head(10)
)

Shape a DataFrame before it spills: reset_index() turns group keys into real columns, rounding and renaming tidy the block, and head() caps its height so that it never runs into content below — the cause of #SPILL!. A spilled block has no number formats, so format its cells in Excel; the formatting is kept when the formula recalculates. More patterns, including a guard for spills that grow over time, are in Return DataFrames and Values from Python in Excel Cells.

Calculation order and structuring a workbook

Python formulas share state: a variable assigned in one Python cell is available to Python cells that calculate after it. The order is fixed and worth memorising — row-major: across row 1 from column A to the last column, then across row 2, and so on down the sheet, with worksheets processed in order.

That rule suggests a structure that keeps workbooks maintainable. Put a small number of "load" cells at the top-left of the first sheet that read tables, clean them and define helper functions, returning Python objects. Put "output" cells below and to the right, each producing one view as Excel values or one chart. The expensive work runs once, and every output is small, independent and easy to move.

# A1 — load and clean once (Python object)
sales = xl("Sales[#All]", headers=True).dropna(subset=["Amount"])
sales["Month"] = sales["Date"].dt.to_period("M").astype(str)

def top_reps(frame, n=5):
    return frame.groupby("Rep", as_index=False)["Amount"].sum().nlargest(n, "Amount")

sales

# A3 — an output that reuses the load cell (Excel values)
top_reps(sales, n=3)

A NameError in a formula almost always means this order was violated: the cell that defines the name calculates after the cell that uses it. While you are editing a workbook with many Python cells, switch to Partial calculation (Formulas → Calculation Options) so Python formulas only recalculate when you press F9.

Charts from Python formulas

matplotlib and seaborn are imported by default as plt and sns, so a formula can draw anything they can. Build an explicit figure, finish the formula with it, and Excel receives an image object. Display Plot over Cells (Ctrl+Alt+Shift+C) places a resizable floating copy; switching the output to Excel Value draws the image inside the cell, sized by the cell.

sales = xl("Sales[#All]", headers=True)
monthly = sales.set_index("Date").groupby("Region")["Amount"].resample("MS").sum().unstack(0)

fig, ax = plt.subplots(figsize=(6, 3), dpi=150)
monthly.plot(ax=ax, marker="o", linewidth=1.8)
ax.set_title("Revenue by month and region", loc="left", fontsize=10)
ax.set_xlabel("")
ax.spines[["top", "right"]].set_visible(False)
fig.tight_layout()
fig

The value of Python charts is in what Excel's gallery cannot do: small multiples, distributions, regression and density overlays. For simple bar and line charts that readers need to hover over, spill the numbers as Excel values and insert a native chart — it is interactive and does not need a cloud round trip to redraw. Both routes are compared in Plot Charts with matplotlib in Python in Excel.

When formulas fail

Python formulas report failures as error values, and each points at a different layer. #PYTHON! is an exception in your code; right-click the cell and open Diagnostics for this cell to see the message and line number, along with anything printed. #CALC! concerns the data — over the 100 MB limit for one calculation, or a volatile input such as RAND(). #SPILL! means the result has no room. #BUSY!, #CONNECT! and #TIMEOUT! come from the cloud runtime; Formulas → Reset runtime (Ctrl+Alt+Shift+F9) fixes most of them, and the timeout is adjustable in Excel's advanced options. #BLOCKED! and #UNKNOWN! are about access and client support rather than code.

For formulas that other people's data feeds, check inputs first and return a readable message rather than letting an exception reach the cell:

sales = xl("Sales[#All]", headers=True)
missing = {"Date", "Region", "Amount"} - set(sales.columns)
f"Missing columns: {sorted(missing)}" if missing else sales.groupby("Region")["Amount"].sum().round(0)

The complete list, with causes and fixes, is in Fix Python in Excel Errors.

Debugging a formula step by step

There is no breakpoint in a cell, but the last-expression rule gives you something almost as good: whatever you put on the final line is what you see. When a long formula produces a wrong number, temporarily end it earlier — on the intermediate frame, its shape, or its dtypes — and move the inspection point down one step at a time until the result stops making sense.

sales = xl("Sales[#All]", headers=True)
filtered = sales[sales["Date"] >= "2026-03-01"]
by_rep = filtered.groupby("Rep")["Amount"].sum()

# Inspection lines — keep exactly one of them last while debugging:
# sales.dtypes
# filtered.shape
pd.DataFrame({"rows in": [len(sales)], "rows after filter": [len(filtered)], "reps": [by_rep.size]})

A one-row summary frame like the one above answers the usual questions at a glance: did the reference return the rows you expected, did the filter keep a sensible number, and did the grouping produce the right number of keys. print() works too, with its output going to the Diagnostics pane, which is handy when you want to inspect several values without changing what the cell returns. Once the formula is right, remove the inspection lines so the cell returns its real result again.

Limits that shape what you build

A few constraints are fundamental rather than temporary, and they should guide design from the start.

• It needs Excel and the cloud. Formulas calculate only in supported Excel clients with an internet connection. Someone opening the file on a mobile app, an unsupported licence or without connectivity sees error values.
• It runs when the workbook calculates. Nothing happens unless the file is open and calculating, so it cannot produce a report on a schedule by itself.
• It is sandboxed. No local files, no network, no package installs, and a 100 MB ceiling on the data in one calculation.
• Code lives in cells. That is convenient for one analyst and awkward for review, version control and testing as a workbook grows.

Those limits are exactly the strengths of the rest of this site's tooling. A scheduled pandas and openpyxl script can pull from a database at 06:00, build a formatted workbook with an Excel table in it, and deliver it; analysts can then add =PY formulas on top for exploration. The comparison, with the same report built three ways, is in Python in Excel vs openpyxl and xlwings.

A worked example: a regional review sheet

Putting the pieces together, here is a compact review sheet built from four formulas. Each is shown with the cell it lives in; together they follow the load-then-output structure and the calculation order described above.

# A1 — load (Python object)
sales = xl("Sales[#All]", headers=True)
budget = xl("Budget[#All]", headers=True)
sales["Quarter"] = sales["Date"].dt.to_period("Q").astype(str)
sales

# A3 — actual against budget by region (Excel values)
actual = sales.groupby("Region", as_index=False)["Amount"].sum()
review = actual.merge(budget, on="Region", how="left")
review["Variance"] = review["Amount"] - review["Budget"]
review["Variance %"] = (review["Variance"] / review["Budget"]).round(3)
review

# F3 — headline number (Excel value)
float(review["Variance"].sum())

# A10 — quarterly trend per region (image)
fig, ax = plt.subplots(figsize=(6, 3), dpi=150)
sales.pivot_table(index="Quarter", columns="Region", values="Amount", aggfunc="sum").plot(kind="bar", ax=ax)
ax.set_title("Revenue by quarter", loc="left", fontsize=10)
ax.spines[["top", "right"]].set_visible(False)
fig.tight_layout()
fig

Every number on the sheet traces back to two tables, Sales and Budget. When next month's rows are added to either table, the whole sheet recalculates, which is the core promise of Python in Excel: analysis that stays live without anyone rerunning a script. Format the Variance % spill as a percentage and add a conditional format to the variance column, and the sheet reads like a hand-built report.

Before sending a sheet like this to anyone outside your team, decide what they should receive. People with Python in Excel can open the live workbook and see it recalculate. Everyone else is better served by a copy in which the spilled blocks have been pasted as values and the chart images placed over the cells, so nothing depends on their Excel client. If that copy has to be produced every month, that is the point at which the reporting half of the job should move into a script that builds the file directly, as described in Generating Excel Reports from Templates.

Key takeaways

• A =PY formula runs in a Microsoft cloud container and returns its last expression to the cell.
• xl() is the only data path: cells, ranges, tables, names and Power Query queries in the same workbook.
• Use Table[#All] with headers=True as the default reference, so formulas see new rows.
• Keep intermediate results as Python objects; publish final views as Excel values and format them in the grid.
• Calculation runs across each row, then down, sheet by sheet — define before you use.
• Return explicit matplotlib figures for charts; use native charts where interactivity matters.
• Read #PYTHON! in the Diagnostics pane; reset the runtime for #BUSY!, #CONNECT! and #TIMEOUT!.
• Scheduled, external or package-heavy work belongs in a script; exploration of workbook data belongs in cells.

Frequently asked questions

Does Python in Excel run on my computer? No. The code runs in a Microsoft cloud container. Excel sends the formula and the referenced data, and receives the result, which is why an internet connection is required.

Can I use my own Python packages? No. The runtime offers a curated distribution with common analysis libraries, and packages cannot be installed. Custom code has to be written in the workbook's cells.

Can Python in Excel read a CSV file or a database? Not from the formula. Load the CSV or database table with Power Query and reference the query with xl(); refreshing the query updates the Python results.

Will colleagues without Python in Excel see my results? They see the last results only if you convert them to static values. In unsupported clients, live Python formulas show error values instead.

Is it a replacement for VBA macros? Not really. Python formulas compute results for cells; they do not automate the application, respond to buttons or modify other cells. VBA, Office Scripts or an external tool such as xlwings still handle automation.

How do I share code between workbooks? There is no import mechanism for your own modules. Copy helper functions into a load cell, or move shared logic into a script-based workflow where it can be packaged and versioned.

Related

• Up one level: Getting Started with Python Excel Automation — every way Python and Excel work together.
• Reference Cells, Ranges and Tables with xl() in Python in Excel
• Return DataFrames and Values from Python in Excel Cells
• Plot Charts with matplotlib in Python in Excel
• Fix Python in Excel Errors
• Python in Excel vs openpyxl and xlwings
• Choosing a Python Excel Library — the script-side libraries compared.
• Automating Excel With xlwings: The Basics — driving Excel from a local script.
• Excel Formula Equivalents in pandas — the pandas idioms most =PY formulas are built from.

More in this section:

• Forecast a Time Series with Python in Excel
• Run a Regression with Python in Excel