How-to

Getting Started With Python Excel AutomationDeep dive

Return DataFrames and Values from Python in Excel Cells

A =PY cell returns its last expression, either as a Python object card or spilled into the grid as Excel values. Shape DataFrames for the grid, avoid #SPILL!, and chain cells without recomputing.

Python in Excel pandas

Getting data into a Python in Excel formula is one function call. Getting a useful result out is where most of the confusion lives: a cell that shows a little card reading DataFrame instead of numbers, a result that spills over data somebody typed next to it, a column of dates that turns into five-digit numbers, or a formula that returns None because its last line was an assignment. This guide, part of Using Python in Excel, explains how a =PY cell decides what to return and how to shape the result so it lands in the grid the way you intended.

Prerequisites

Excel with Python in Excel available and enabled.
A table named Sales with Date, Region, Rep and Amount columns, referenced as in Reference Cells, Ranges and Tables with xl() in Python in Excel.

Step 1 — Understand the last-expression rule

A Python formula has no return statement. Like a notebook cell, it returns the value of its last expression. Everything before that line runs for its side effects — variable assignments, imports, helper functions — and only the final expression reaches the cell.

Python

sales = xl("Sales[#All]", headers=True)
by_region = sales.groupby("Region", as_index=False)["Amount"].sum()
by_region                     # the last expression: this is what the cell returns

End a formula with an assignment such as by_region = … and there is no expression to return, so the cell shows None. It is the most common "my formula does nothing" report, and the fix is always to repeat the variable name on its own line.

Variables assigned in one Python cell remain available to Python cells that calculate after it. That is what makes it practical to load and clean a table once near the top of the sheet and derive several small outputs from it further down.

Step 2 — Choose Python object or Excel value

Every Python formula has an output type, set from the Python output menu next to the formula bar, from the cell's right-click menu, or with Ctrl+Alt+Shift+M:

Python object keeps the result as a live Python value. A DataFrame shows as a card with a DataFrame label; you can open the card to preview it. Nothing is written into neighbouring cells.
Excel value converts the result to the closest Excel equivalent. A scalar becomes an ordinary number, text or date; a DataFrame or Series spills across the cells below and to the right.

Python

sales = xl("Sales[#All]", headers=True)
pivot = sales.pivot_table(index="Rep", columns="Region", values="Amount", aggfunc="sum", fill_value=0)
pivot

Leave a formula like this one as a Python object while you are still building the workbook, so an intermediate result does not overwrite anything. Switch it to Excel value once you want other people — or Excel charts, conditional formats and ordinary formulas — to see the numbers.

Step 3 — Shape a DataFrame before it spills

When a DataFrame is converted to Excel values, what you see is exactly what the frame holds — including its index, its full-precision floats and its internal column names. Tidy those in Python rather than fixing them in the grid afterwards, because the spill is regenerated on every recalculation and manual edits to it are not possible.

Python

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

report = (
    sales.groupby(["Region", "Rep"])["Amount"]
         .agg(Total="sum", Deals="count", Average="mean")
         .round({"Total": 0, "Average": 2})
         .sort_values("Total", ascending=False)
         .reset_index()                       # index levels become ordinary columns
         .rename(columns={"Rep": "Sales rep"})
         .head(10)
)
report

reset_index() is the step that matters most. A grouped result keeps its group keys in the index, and turning them into regular columns gives you control over their names and position in the spilled block. head(10) keeps the spill a predictable size, which is the easiest way to leave room for content underneath.

Step 4 — Return single values for KPIs and labels

Not every formula should spill. A headline number that an Excel chart title, a text box or a conditional format refers to is best returned as a scalar, converted to an Excel value so it behaves like any other number.

Python

sales = xl("Sales[#All]", headers=True)
this_month = sales[sales["Date"] >= sales["Date"].max().replace(day=1)]
float(this_month["Amount"].sum())            # a plain number in the cell

The float() call is deliberate. pandas aggregations return NumPy scalar types, which convert fine, but an explicit Python number makes the intent obvious and guards against returning a one-element Series by accident. For a label, return a formatted string — f"{total:,.0f} this month" — and accept that the cell then holds text rather than a number that other formulas can use.

Step 5 — Keep dates and numbers usable

A spilled block contains values but no number formatting, so dates can appear as serial numbers and currency as raw floats. There are two approaches, and they suit different purposes.

Python

sales = xl("Sales[#All]", headers=True)
monthly = (
    sales.set_index("Date")["Amount"]
         .resample("MS").sum()
         .reset_index()
         .rename(columns={"Date": "Month"})
)

# Option A: keep real dates and numbers; format the spill range in Excel
monthly

# Option B: presentation-only text columns (not usable in further calculations)
# monthly.assign(Month=monthly["Month"].dt.strftime("%b %Y"), Amount=monthly["Amount"].map("{:,.0f}".format))

Prefer option A whenever the output feeds a chart or other formulas: select the spill area in the grid and apply a date or currency format once. Formatting applied to cells is kept when the formula recalculates. Option B is for a block that is purely for reading, where the exact appearance matters more than the ability to calculate on it. The same trade-off exists for scripts writing files, described in Applying Number and Date Formats in Excel.

Step 6 — Chain cells instead of recomputing

Because variables flow forward, a sensible workbook structure is one "load" cell that returns a Python object and several small "output" cells that each return one view as Excel values. The expensive work happens once, and each output can be switched, moved or deleted independently.

Python

# Cell A1 — Python object: the cleaned source, computed once
sales = xl("Sales[#All]", headers=True)
sales = sales.dropna(subset=["Amount"]).assign(Month=lambda d: d["Date"].dt.to_period("M").astype(str))
sales

# Cell A3 — Excel value: a view that reuses the variable from A1
sales.pivot_table(index="Month", columns="Region", values="Amount", aggfunc="sum", fill_value=0).reset_index()

Remember the calculation order: Python cells run across each row from column A, then down to the next row, sheet by sheet. Put load cells above and to the left of everything that uses them, and keep them on the first sheet if several sheets share the data.

Step 7 — Guard the size and shape of a spill

A spill that is five rows today can be five hundred next quarter, and a result that grows into the cells below it turns into #SPILL! the moment it reaches somebody's notes. When a formula publishes a view to the grid, decide its maximum size in code and make the truncation visible, rather than letting the data decide how much of the sheet it takes.

Python

sales = xl("Sales[#All]", headers=True)
MAX_ROWS = 15

ranked = (
    sales.groupby("Rep", as_index=False)["Amount"].sum()
         .sort_values("Amount", ascending=False)
         .reset_index(drop=True)
)
shown = ranked.head(MAX_ROWS)
if len(ranked) > MAX_ROWS:
    rest = pd.DataFrame({"Rep": [f"{len(ranked) - MAX_ROWS} others"],
                         "Amount": [ranked["Amount"].iloc[MAX_ROWS:].sum()]})
    shown = pd.concat([shown, rest], ignore_index=True)
shown

The "others" row keeps the column total correct while capping the height at sixteen rows, so the block below can be placed with confidence. The same idea applies to width: a pivot whose columns come from data — one per region, one per month — grows sideways, so select the columns you intend to show explicitly instead of returning whatever the pivot produced.

Common pitfalls

Symptom	Cause	Fix
Cell shows `None`	Last line is an assignment	End with the variable name as an expression
Cell shows a `DataFrame` card	Output type is Python object	Switch to Excel value with Ctrl+Alt+Shift+M
`#SPILL!`	Cells in the spill area are not empty	Clear the block, or move the formula
Group names missing from the spill	Keys are in the index	Call `reset_index()` before returning
Dates shown as numbers	Spill has no number format	Format the spill range in Excel
Later cell raises `NameError`	It calculates before the cell defining the variable	Move the definition up or left

Performance and scale notes

Converting a result to Excel values costs time proportional to the number of cells written, and a spill of tens of thousands of rows makes every recalculation slower and the workbook heavier. Return aggregated views to the grid and keep row-level data as Python objects. If a sheet has many Python cells, Partial calculation mode stops them recalculating on every edit; press F9 when you want fresh results.

Conclusion

A Python formula returns its last expression, and its output type decides where that result goes. Keep intermediate DataFrames as Python objects so they stay in one cell and can be reused; switch the final views to Excel values so charts, formulas and people can see them. Shape frames before they spill — reset_index, rounding, renaming, head — return plain scalars for single numbers, and format spill ranges in Excel rather than turning numbers into text. Structure the sheet as one load cell feeding several small output cells, in calculation order.

Frequently asked questions

How do I return more than one result from a single Python cell? A cell returns one value. Return a DataFrame that combines the results, or assign each result to a variable and return them from separate cells, which is usually clearer.

Can an Excel formula use a value from a Python object cell? Switch that cell's output to Excel value first. Ordinary formulas and charts work with converted values, not with Python objects.

Why does my DataFrame spill with an extra column of numbers? That column is the index. Call reset_index(drop=True) to discard a meaningless index, or reset_index() to keep meaningful index labels as a named column.

Does formatting on the spill range survive recalculation? Yes. Number formats, fonts and fills applied to the cells stay in place when the formula recalculates, as long as the spill still covers those cells.

Up one level: Using Python in Excel — the full picture of =PY formulas and where they fit.
Reference Cells, Ranges and Tables with xl() in Python in Excel — the input side of the same formulas.
Plot Charts with matplotlib in Python in Excel — returning images instead of values.
Fix Python in Excel Errors — #SPILL!, #PYTHON! and the rest.
Create a Pivot Table from Excel with Pandas — the pivot_table options used above.

More on python in excel