06 · Excel + Python Integration (Overview)¶

This module covers the two current ways to combine Excel and Python: the built-in =PY() formula (Python in Excel), and driving Excel files from standalone Python via openpyxl/pandas — and when to reach for each.

1. Worked dataset¶

Sheet Data, A1:B6:

	A	B
1	Product	Units
2	Widget	120
3	Gadget	95
4	Gizmo	140
5	Sprocket	60
6	Doohickey	110

2. Python in Excel — `=PY()`¶

In a cell, =PY( opens a Python-enabled formula cell (available on Microsoft 365 builds with Python in Excel enabled). Reference sheet ranges as pandas objects using xl(): df = xl("A1:B6", headers=True) df["Units"].sum() This returns 120+95+140+60+110=525, computed by the Python runtime but displayed as a normal cell result.
Python in Excel runs in a secure, isolated container — it can use pandas, NumPy, and Matplotlib, but it cannot reach the local file system or network, so a =PY() cell can analyze the workbook's own data but cannot pull in an external CSV or API directly.
A quick chart from the same cell: df.plot(kind="bar", x="Product", y="Units") renders as an image output in the cell — useful for exploratory analysis that would otherwise need a full chart object.

3. Driving Excel from standalone Python — `openpyxl`¶

import openpyxl

wb = openpyxl.load_workbook("Data.xlsx")
ws = wb["Data"]

total = 0
for row in ws.iter_rows(min_row=2, max_row=6, min_col=2, max_col=2, values_only=True):
    total += row[0]

ws["B8"] = "Total"
ws["B9"] = total
wb.save("Data.xlsx")

Manual check: total sums the same five values as Section 2, 120+95+140+60+110=525, and the script writes 525 into B9 of the saved file — this is the pattern for batch-processing many workbooks unattended (a folder of 200 monthly reports, for example), which =PY() inside a single open workbook cannot do.

4. `pandas` for heavier transforms¶

import pandas as pd

df = pd.read_excel("Data.xlsx", sheet_name="Data")
df["Units_x1.1"] = df["Units"] * 1.1
df.to_excel("Data_updated.xlsx", index=False)

For Widget (120), Units_x1.1 computes 120*1.1=132.0; for Gizmo (140), 140*1.1=154.0. pandas is preferred over openpyxl for row-wise numeric transforms across large tables because its vectorized operations avoid a Python-level loop per cell.

5. Choosing between the three approaches¶

Situation	Best tool
One-off analysis inside a workbook someone else will open and read normally	`=PY()` — stays inside the familiar Excel file
Automating the same edit across many `.xlsx` files on a schedule, no Excel install required	Standalone `openpyxl`/`pandas` script
Heavy numeric transforms (joins, groupby, reshaping) on data already in Excel	`pandas`, then write back with `to_excel`
Reading a value or writing a result but preserving existing formatting/charts exactly	`openpyxl` (cell-level control) over `pandas` (which rewrites the whole sheet)

6. A caution on round-tripping¶

pandas.to_excel and even openpyxl can strip conditional formatting, data validation, or chart objects that pandas doesn't understand when it rewrites a sheet. For a workbook with dashboards/formatting that must survive the edit, prefer openpyxl's in-place cell writes (as in Section 3) over pandas.to_excel's full-sheet rewrite (Section 4).

Cheat sheet¶

Tool	Runs where	Best for
`=PY()`	Inside the open workbook, sandboxed	Exploratory analysis, in-cell charts
`openpyxl`	Standalone Python	Precise cell writes, preserving formatting
`pandas`	Standalone Python	Heavy tabular transforms, at the cost of formatting fidelity

How It Actually Works¶

Python in Excel doesn't run inside the same process as Excel's calculation engine at all — it executes in a sandboxed, isolated container (Microsoft's cloud-hosted runtime), sending your Python code and the referenced cell data across that boundary, running it remotely, and returning the result back into the workbook as a special PY cell object. This is a fundamentally different execution model from a SUM or even a LAMBDA: Excel's normal formulas are evaluated in-process by the same dependency graph you've used all course, while a Python cell's result is treated by that graph as an opaque value dependent on its declared input cells — Excel knows that the Python cell depends on certain ranges (so it recalculates when they change) but the actual computation happens outside the recalculation engine entirely, in a separate Python interpreter process with pandas/NumPy/matplotlib available. This explains two practical behaviors directly: Python cells require network connectivity to the cloud runtime (there's no local fallback), and they're intentionally sandboxed with no direct file-system or internet access from inside the Python code itself, because the code is running in an environment Microsoft isolates for security, not directly inside your machine's Excel process.

Exercise¶

Write an openpyxl script that opens Data.xlsx, computes the average units (525/5=105), writes it to B10 labeled "Average" in A10, and saves the file — then open it in Excel and confirm B10 reads 105.